1. cells B19 to D19 represent the forecasted accommodation revenue over a 3-year period. The initial accommodation revenue is stored in cell B6 ($300,000). This amount is expected to grow at the rate stored in cell C6 (5%). List, in the space provided below this part of the question, the formula that should appear in cell B19. When writing the formula that should appear in cell B19, design the formula so that it can be copied to cells C19:D19 without modification. (Note: you may provide an alternate answer that has a separate formula for cells B19 and C19, as long as cell C19 can then be copied across to year 3). (4 marks)
2. The manager of Monash Commonwealth Resort would like to know what the accommodation growth rate should be if the total net profit for 3 years is $400,000. To find the answer, please use Excels Goal Seek feature.With reference to both Graph above and the Goal Seek dialog box shown below, list in the second column of the answer table provided below the Goal Seek parameters that would need to be set in order to find the answer. (3 marks)

3. Answer table:

   Set cell
   To value
   By changing cell

A B D E 1 3 Year Projected Profit and Loss Statement 2 3 DATA 4 Annual Data 5 $ Growth 6 Accommodation 300,000 5.00% 7 Restaurant 80,000 6.00% 8 Advertising 20,000 3.00% 9 Licensing fees 1,500 500 10 Labour costs 240,000 5.00% 11 Other expense 5,000 6.00% 12 13 REPORT 14 MONASH COMMONWEALTH RESORT 15 PROJECTED PROFIT AND LOSS STATEMENT 16 Year 17 1 2 3 TOTAL 18 INCOME 19 Accommodation 300,000 315,000 330,750 945,750 20 Restaurant 80,000 84,800 89,888 254,688 21 EXPENSES 22 Advertising 20,000 20,600 21,218 61,818 23 Licensing fees 1,500 2,000 2,500 6,000 24 Labour costs 240,000 252,000 264,600 756,600 25 Other expense 5,000 5,300 5,618 15,918 26 27 NET PROFIT 113,500 119,900 126,702 360,102 28 29 SENSITIVITY ANALYSIS 30 Accommodation growth rate 31 360,102 5.00% 5.50% 6.00% 6.50% 32 5.00% 360,102.00 364,759.50 369,432.00 374,119.50 33 Labour costs 5.50% 356,376.00 361,033.50 365,706.00 370,393.50 34 growth rate 6.00% 352,638.00 357,295.50 361,968.00 366,655.50 35 6.50% 348,888.00 353,545.50 358,218.00 362,905.50 36 7.00% 345,126.00 349,783.50 354,456.00 359,143.50 A B D E 1 3 Year Projected Profit and Loss Statement 2 3 DATA 4 Annual Data 5 $ Growth 6 Accommodation 300,000 5.00% 7 Restaurant 80,000 6.00% 8 Advertising 20,000 3.00% 9 Licensing fees 1,500 500 10 Labour costs 240,000 5.00% 11 Other expense 5,000 6.00% 12 13 REPORT 14 MONASH COMMONWEALTH RESORT 15 PROJECTED PROFIT AND LOSS STATEMENT 16 Year 17 1 2 3 TOTAL 18 INCOME 19 Accommodation 300,000 315,000 330,750 945,750 20 Restaurant 80,000 84,800 89,888 254,688 21 EXPENSES 22 Advertising 20,000 20,600 21,218 61,818 23 Licensing fees 1,500 2,000 2,500 6,000 24 Labour costs 240,000 252,000 264,600 756,600 25 Other expense 5,000 5,300 5,618 15,918 26 27 NET PROFIT 113,500 119,900 126,702 360,102 28 29 SENSITIVITY ANALYSIS 30 Accommodation growth rate 31 360,102 5.00% 5.50% 6.00% 6.50% 32 5.00% 360,102.00 364,759.50 369,432.00 374,119.50 33 Labour costs 5.50% 356,376.00 361,033.50 365,706.00 370,393.50 34 growth rate 6.00% 352,638.00 357,295.50 361,968.00 366,655.50 35 6.50% 348,888.00 353,545.50 358,218.00 362,905.50 36 7.00% 345,126.00 349,783.50 354,456.00 359,143.50