3 4 Total Net Revenue 5 Cost of Sales 6 Direct Expenses 2 Profit Center Analysis...

Transcribed Image Text:

3 4 Total Net Revenue 5 Cost of Sales 6 Direct Expenses 2 Profit Center Analysis of Indirect Expenses 8 Administrative 9 Depreciation 0 Energy 1 Insurance 6 7 2 Maintenance 3 Marketing 4 Total Indirect Expenses 5 Net Income 20 Depreciation 21 Energy 2 Insurance 25 8 Planned Indirect Expenses 9 Administrative 23 Maintenance 24 Marketing x 26 11 27 Name: ClarkMarquelda II ||||||||||| % 28 4 12 * 13 7 14 1 15 6 16 7 A Indirect Expenses 37 10 18 Square Footage 10 19 0 1 10 a B 29 Instructions 10 Use the Planned Indirect Expenses provided in the cells B19:B24 to calculate the following indirect expenses: a. Spa Administrative (B8) = Administrative Expenses * Spa Total Net Revenue/Resort Total Net Revenue b. Spa Depreciation (B9) = Depreciation Expenses * Spa Square Footage/Total Square Footage c. Spa Energy (B10) = Energy Expenses Spa Total Net Revenue/Resort Total Net Revenue d. Spa Insurance (B11) = Insurance Expenses Spa Square Footage/Total Square Footage an e. Spa Maintenance (B12) = Maintenance Expenses Spa Square Footage/Total Square Footage Marketing Co f. Spa Marketing (B13) = Marketing Expenses Spa Total Net Revenue / Resort Total Net Revenue Go Total Indlin 20-D421 g. Spa Total Indirect Expenses (B14) = SUM(B8:B13) h. Spa Net Income (B15) = Revenue - (Cost of Sales + Direct Expenses + Total Indirect Expenses) C 2500 $72,500.00 $61,250.00 $46,000.00 $12,500.00 $26,000.00 $52,250.00 D 5100 Children's Game Room Spa Lounge Restaurant Gift Shop $ 78,865.00 $ 492,800.00 $ 486,725.00 $ 592,500.00 $ 225,125.00 $ 196,475.00 $ 88,450.00 $ 19,450.00 36,715.00 136,500.00 106,500.00 45,125.00 325,600.00 252,975.00 37,000.00 31,800.00 8,650.00 6,940.00 14,750.00 152,975.00 53,890.00 22,475.00 Banquet Room E 8800 6000 F Business Center 14,790.00 8,435.00 G 900 Conference Rooms H 5700 750 1200 Hoo the i. Use the concept of absolute reference we learn in chapter 3 to modify some cell references in column B, then use the fill handle to copy the range B8:B15 to the range C8:115 j. AutoSum the totals for Column J, where appropriate k. Format the number as it deems appropriate (i.e., any appropriate formats will be accepted) Total 3 4 Total Net Revenue 5 Cost of Sales 6 Direct Expenses 2 Profit Center Analysis of Indirect Expenses 8 Administrative 9 Depreciation 0 Energy 1 Insurance 6 7 2 Maintenance 3 Marketing 4 Total Indirect Expenses 5 Net Income 20 Depreciation 21 Energy 2 Insurance 25 8 Planned Indirect Expenses 9 Administrative 23 Maintenance 24 Marketing x 26 11 27 Name: ClarkMarquelda II ||||||||||| % 28 4 12 * 13 7 14 1 15 6 16 7 A Indirect Expenses 37 10 18 Square Footage 10 19 0 1 10 a B 29 Instructions 10 Use the Planned Indirect Expenses provided in the cells B19:B24 to calculate the following indirect expenses: a. Spa Administrative (B8) = Administrative Expenses * Spa Total Net Revenue/Resort Total Net Revenue b. Spa Depreciation (B9) = Depreciation Expenses * Spa Square Footage/Total Square Footage c. Spa Energy (B10) = Energy Expenses Spa Total Net Revenue/Resort Total Net Revenue d. Spa Insurance (B11) = Insurance Expenses Spa Square Footage/Total Square Footage an e. Spa Maintenance (B12) = Maintenance Expenses Spa Square Footage/Total Square Footage Marketing Co f. Spa Marketing (B13) = Marketing Expenses Spa Total Net Revenue / Resort Total Net Revenue Go Total Indlin 20-D421 g. Spa Total Indirect Expenses (B14) = SUM(B8:B13) h. Spa Net Income (B15) = Revenue - (Cost of Sales + Direct Expenses + Total Indirect Expenses) C 2500 $72,500.00 $61,250.00 $46,000.00 $12,500.00 $26,000.00 $52,250.00 D 5100 Children's Game Room Spa Lounge Restaurant Gift Shop $ 78,865.00 $ 492,800.00 $ 486,725.00 $ 592,500.00 $ 225,125.00 $ 196,475.00 $ 88,450.00 $ 19,450.00 36,715.00 136,500.00 106,500.00 45,125.00 325,600.00 252,975.00 37,000.00 31,800.00 8,650.00 6,940.00 14,750.00 152,975.00 53,890.00 22,475.00 Banquet Room E 8800 6000 F Business Center 14,790.00 8,435.00 G 900 Conference Rooms H 5700 750 1200 Hoo the i. Use the concept of absolute reference we learn in chapter 3 to modify some cell references in column B, then use the fill handle to copy the range B8:B15 to the range C8:115 j. AutoSum the totals for Column J, where appropriate k. Format the number as it deems appropriate (i.e., any appropriate formats will be accepted) Total