Slopeside Condominiums is a 100-unit condominium complex in north-central Vermont, situated near several major ski area. It operates as a hotel-condominium, renting out units that owners are not using through a rental office.
In most years, the complex returns a small profit each year to the owners. But there are many expenses involved in running a hotel-condominium - expenses for electricity, heat, insurance, and maintenance staff to name a few. At times, Slopeside's manager, John Watts, has found that Slopeside has been short of cash for paying the bills.
Slopeside needs to develop a plan for managing its cash flow. In some respects, a business's cash flow is more important than its profit. If a firm can't pay its employees or suppliers or collect form its customers it may not survive long enough to generate a profit.
One way of analyzing and forecasting cash flow is to use a "cash receipts and disbursements" forecast, which estimates the amount of each category of cash receipts and disbursements. This enables businesses to track actual cash movements on a daily, weekly or monthly basis.
Essentially, this kind of cash flow forecast shows projected cash inflow and outflow and subtracts the outflow from the inflow. Cash inflow or outflow can be determined by subtracting total disbursements from total receipts. This resulting amount (which may be positive or negative) must then be added to cash on hand at the beginning of the period to arrive at an ending cash figure. This ending cash figure in turn becomes the beginning cash figure for the next period.
From past experience, Watts has determined the average occupancy rate for various months of the year. The occupancy rate is highest during the ski season, summer months and early September, when visitors flock to Vermont to witness the changing fall foliage. Watts can estimate monthly room receipts by multiplying the daily unit rental receipts by the number of days in the month, and the average occupancy rate for that month. The daily unit rental receipts are $7800 and represent the amount of receipts if all available units were rented. Slopeside also receives $8000 per month in commons fees paid by the owners for insurance and upkeep.
Watts has also come up with formulas for estimating other receipts and disbursements. Some of these are variable, and related to the occupancy rate at the hotel-condominium. Watts has figured that expenditures for telephone usage are approximately 5% of room receipts. Expenditures for room upkeep are approximately 30% of room receipts.
Other expenses are not related to occupancy rates. On an annual basis, Slopeside pays $18,500 for insurance; $275,000 in payroll for the manager, office, cleaning, and maintenance staff. $25,000 for marketing and advertising; and $250,000 for repairs and maintenance. Since furniture and applicances are constantly being damaged, Slopeside pays $6000 per month into a furniture reserve fund. Slopeside also pays between $4400 and $9500 per month for utilities and fuel. Insurance is paid in one installment every May. All other expenses are paid on a monthly basis.
Tasks
- Open the file Slopeside.xls
- Create an assumptions section to identify factors in your calculations of receipts and disbursements. Make sure formulas reference cells in the assumptions section wherever possible.
- Calculate the receipts and disbursements for each of the categories on the worksheet for 12 months. Calculate total receipts and total disbursements. The case has been simplified so that all revenue is collected in the month it is generated.
- Complete the cash flow analysis by calculating the ending cash for each month and print the results. Write an analysis of the cash flow situation for Slopeside Condominiums. What explains the negative cash flow in certain months? What steps can Slopeside take to eliminate cash flow crises?
- Print the spreadsheet showing gridlines.
- Now print the spreadsheet again showing formulas.