Michael has decided to buy a 40-unit motel for $1 280 000, which begins operations in January
Question:
Michael has decided to buy a 40-unit motel for $1 280 000, which begins operations in January next year. This amount includes land valuation at $100 000, building at $990 000, furniture & equipment at $180 000 and a cash at bank balance of $10 000. This was all paid from a business loan from the bank. The loan will be paid over 10 years at 8.9% pa, although he has agreed to pay $12 000 per month interest and $8 000 per month for the principal repayments for this for simplistic purposes.
The building has an estimated life of 30 years and the furniture and equipment for 10 years. Use straight-line depreciation for the building and furniture & equipment.
For the first 3 months of operations, occupancy is forecast to be 65%, 70%, and 75% respectively. The average room rate will be $75. In calculating room sales revenue, assume this year is not a leap year. All sales will be on a cash basis.
Michael and his partner will manage the motel themselves, with monthly wages of $2 500 each.
The following cash expenses are estimated:
- Employee wages expense (including super) will be $15 000 a month.
- Laundry and supplies are 12% of monthly sales revenue.
- Insurance premiums are $25 000 for the first year.
- Advertising is budgeted to be $2 000 per month.
- Energy costs to be $2 200, $2 400 and $2 500 respectively for each of the first 3 months. The monthly energy cost for month 1 will be paid in month 2; month 2 in month 3, and so on.
- Administrative, office and other expenses will be $1 500 monthly.
Use the template Financial Reports Template.xlsx ( a photo of the template is below)
to prepare the following financial reports for each of the first three (3) months of the motel operation, rounding figures to the nearest dollar:
- A Cash Flow Projection
- An Income Statement
