Scenario 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 A Balance Sheet as at 31 March AutoSave OF Financial reports templates Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (Body) v 11 Al A ab Wran Text General v X LG SB V HIM Paste B 1 v OA Merge Centre $ , Insert Delete Format Conditional Format Cell Formatting as Table Styles Sort Filter Find Select Analyse Data V B4 T A A c D F F G 1 K L M N o 0 P R S 1 U Actual Income Analysis for January 1 2 Fourable Unfavourable Actual Rassanire ar siegested strategies for improvement Budget variance 3 4 Room sales revenue 5 6 Operating expenses 2 8 9 10 11 12 13 14 15 16 17 18 19 20 2 22 23 24 25 26 27 28 29 30 32 33 34 35 36 37 38 35 40 42 Income Statement template Balance Sheet template Actual Income analysis Ready E o 100

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jul 15, 2024

Scenario 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

Scenario

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
A Balance Sheet as at 31 March

AutoSave OF Financial reports templates Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (Body) v 11 ~ Al A ab Wran Text General v X LG SB @ V HIM Paste B 1 v OA = = Merge & Centre $ %, Insert Delete Format Conditional Format Cell Formatting as Table Styles Sort Filter Find & Select Analyse Data V B4 T A A c D F F G 1 K L M N o 0 P R S 1. U Actual Income Analysis for January 1 2 Fourable! Unfavourable Actual Rassanire ar siegested strategies for improvement Budget variance 3 4 Room sales revenue 5 6 Operating expenses 2 8 9 10 11 12 13 14 15 16 17 18 19 20 2: 22 23 24 25 26 27 28 29 30 32 33 34 35 36 37 38 35 40 42 Income Statement template Balance Sheet template Actual Income analysis + Ready = E o 100%