Question

1 Approved Answer

Posted on Sep 28, 2024

#15-17 please a. 15. Update the All Locations worksheet as follows: In cell B7, enter a formula using the SUM function, 3-D references, and grouped

#15-17 please image text in transcribed

a. 15. Update the All Locations worksheet as follows: In cell B7, enter a formula using the SUM function, 3-D references, and grouped worksheets to total the values in cell B7 on the Highland Hotel:Pinehill Lodge worksheets. b. Copy the formula you created in cell B7 to the range B7:M11 without copying any cell formatting. (Hint: Use the Paste Gallery.) 16. To help analyze the revenue data for all locations, create a chart as follows: a. For the ranges B14:M14 and B20:M20 of the All Locations worksheet, insert a 2-D Clustered Column chart that shows each month as a data series. b. Move the 2-D clustered column chart to a new chart sheet and use Total Revenue Chart as the worksheet name. Vary the fill colors of the columns by point. 17. To make the new chart easier to interpret, make the following changes: Add a Primary Vertical Axis Title and use Revenue as the text. b. Use Monthly Revenue as the chart title. C. a. B7 fx =SUM('Highland Hotel':'Pinehill Lodge'lB4) F G H Adela Mountain Resort All Locations 4/26/21 2019 Monthly Rentals June July Room February March April May August January HINAME! September October Room February March Apri 2019 Monthly Revenue June July May August September October MINAME WNAME! 5 5 195 20 Total 21 22 2) 24 25 26 27 20 29 10 31 32 33 14 35 36 b Inn Mortgage Highland Hotel Adela Condos Pinehill Lodge Now Inn All Locations + Ready H Adela Mountain Resort Highland Hotel March Room Colorado Montana Utah Wyoming 1 Yosemice 2 January 30 30 30 30 30 February 29 29 29 29 29 28 31 29 30 Apri 25 20 18 20 21 May 20 15 14 12 15 2019 Rentals per Month June July 10 10 8 7 12 10 11 August 10 8 TO 12 11 September 20 21 30 18 18 October 31 28 27 30 January February March 4950 $ 4.7855 5.115 5 5250 5.075.00 4,900.00 5250 5.075.00 5425.00 5250 5.075.00 5.075.00 6000 5,800.00 6.000.00 $ 26.700 5 25 BIO $ 26.515 $ 3.375$ 2.700.00 2.430,00 2.900.00 3.675.00 15.080 $ 2019 Revenue per Month May use August September October 2.700 5 13505 13505 1,350 $ 2,700$ 5.115.5 2.025.00 1.080.00 945.00 1.080.00 2.835.00 4,900.00 1890.00 1.620.00 1.215.00 1,350.00 4.050.00 4,725.00 1.740.00 1.450.00 1.595.00 1.740.00 2.610.00 5.250.00 2.625.00 1.925.00 1.400.00 1.925.00 3.150.00 6,200.00 10.980 $ 7425$ 6.505 $ 7445 $ 15.3455 26,190 $ 14 Room 15 Colorado 16 Montana 17 Utah 18 Wyoming 19 Yosemite 20 Total 21 22 23 24 25 26 27 28 29 30 31 32 33 34 25 26 $ b Inn Mortgage Highland Hotel Adela Condos Pinohill Lodge New In All Locations fx =C8*175 K B D H E F G Adela Mountain Resort Pinehill Lodge Room pine Bellflower Edelweiss Gentian Wintergreen January 30 30 30 30 30 February 29 29 29 29 29 March 27 28 30 25 27 Apri 20 14 12 14 15 May 15 10 9 7 10 2019 Monthly Rentals June July 11 10 9 7 13 9 10 11 10 August 9 7 9 11 10 September 18 19 28 16 16 October 31 25 24 26 29 Room Alpine Belflower Edelweiss Gentian Wintergreen Total January February March 4950 s 4.785 $ 4.455 $ 5250 5.075.00 4,900.00 5250 5,075.00 5.250.00 5250 5.075.00 4,375.00 6000 5,800.00 5,400.00 $ 26,700 $ 25,810 $ 24,380 $ April May 2,700 $ 2.025 1.890.00 1.350.00 1,620.00 1.215 00 2,030.00 1.015.00 2.625.00 1.750.00 10,865 $ 7,355 2019 Revenue per Month June July August September October $ 1.485 $ 1,350 $ 1.215 $ 2.430 $ 5,115 1,215.00 945.00 945.00 2,565.00 4375.00 1.755.00 1.215.00 1.215.00 3.780.00 4,200.00 1.595.00 1.595.00 1.595.00 2.320.00 4,550.00 1.925.00 1.750,00 1.750.00 2,800.00 5,800.00 $ 7,975 $ 6,8555 6,720 $ 13,895 $ 24,040 + 1 2 3 14 35 36 a Inn Mortgage Highland Hotel Adela Condos Pinehill Lodge New Inn All Locations CH + a. 15. Update the All Locations worksheet as follows: In cell B7, enter a formula using the SUM function, 3-D references, and grouped worksheets to total the values in cell B7 on the Highland Hotel:Pinehill Lodge worksheets. b. Copy the formula you created in cell B7 to the range B7:M11 without copying any cell formatting. (Hint: Use the Paste Gallery.) 16. To help analyze the revenue data for all locations, create a chart as follows: a. For the ranges B14:M14 and B20:M20 of the All Locations worksheet, insert a 2-D Clustered Column chart that shows each month as a data series. b. Move the 2-D clustered column chart to a new chart sheet and use Total Revenue Chart as the worksheet name. Vary the fill colors of the columns by point. 17. To make the new chart easier to interpret, make the following changes: Add a Primary Vertical Axis Title and use Revenue as the text. b. Use Monthly Revenue as the chart title. C. a. B7 fx =SUM('Highland Hotel':'Pinehill Lodge'lB4) F G H Adela Mountain Resort All Locations 4/26/21 2019 Monthly Rentals June July Room February March April May August January HINAME! September October Room February March Apri 2019 Monthly Revenue June July May August September October MINAME WNAME! 5 5 195 20 Total 21 22 2) 24 25 26 27 20 29 10 31 32 33 14 35 36 b Inn Mortgage Highland Hotel Adela Condos Pinehill Lodge Now Inn All Locations + Ready H Adela Mountain Resort Highland Hotel March Room Colorado Montana Utah Wyoming 1 Yosemice 2 January 30 30 30 30 30 February 29 29 29 29 29 28 31 29 30 Apri 25 20 18 20 21 May 20 15 14 12 15 2019 Rentals per Month June July 10 10 8 7 12 10 11 August 10 8 TO 12 11 September 20 21 30 18 18 October 31 28 27 30 January February March 4950 $ 4.7855 5.115 5 5250 5.075.00 4,900.00 5250 5.075.00 5425.00 5250 5.075.00 5.075.00 6000 5,800.00 6.000.00 $ 26.700 5 25 BIO $ 26.515 $ 3.375$ 2.700.00 2.430,00 2.900.00 3.675.00 15.080 $ 2019 Revenue per Month May use August September October 2.700 5 13505 13505 1,350 $ 2,700$ 5.115.5 2.025.00 1.080.00 945.00 1.080.00 2.835.00 4,900.00 1890.00 1.620.00 1.215.00 1,350.00 4.050.00 4,725.00 1.740.00 1.450.00 1.595.00 1.740.00 2.610.00 5.250.00 2.625.00 1.925.00 1.400.00 1.925.00 3.150.00 6,200.00 10.980 $ 7425$ 6.505 $ 7445 $ 15.3455 26,190 $ 14 Room 15 Colorado 16 Montana 17 Utah 18 Wyoming 19 Yosemite 20 Total 21 22 23 24 25 26 27 28 29 30 31 32 33 34 25 26 $ b Inn Mortgage Highland Hotel Adela Condos Pinohill Lodge New In All Locations fx =C8*175 K B D H E F G Adela Mountain Resort Pinehill Lodge Room pine Bellflower Edelweiss Gentian Wintergreen January 30 30 30 30 30 February 29 29 29 29 29 March 27 28 30 25 27 Apri 20 14 12 14 15 May 15 10 9 7 10 2019 Monthly Rentals June July 11 10 9 7 13 9 10 11 10 August 9 7 9 11 10 September 18 19 28 16 16 October 31 25 24 26 29 Room Alpine Belflower Edelweiss Gentian Wintergreen Total January February March 4950 s 4.785 $ 4.455 $ 5250 5.075.00 4,900.00 5250 5,075.00 5.250.00 5250 5.075.00 4,375.00 6000 5,800.00 5,400.00 $ 26,700 $ 25,810 $ 24,380 $ April May 2,700 $ 2.025 1.890.00 1.350.00 1,620.00 1.215 00 2,030.00 1.015.00 2.625.00 1.750.00 10,865 $ 7,355 2019 Revenue per Month June July August September October $ 1.485 $ 1,350 $ 1.215 $ 2.430 $ 5,115 1,215.00 945.00 945.00 2,565.00 4375.00 1.755.00 1.215.00 1.215.00 3.780.00 4,200.00 1.595.00 1.595.00 1.595.00 2.320.00 4,550.00 1.925.00 1.750,00 1.750.00 2,800.00 5,800.00 $ 7,975 $ 6,8555 6,720 $ 13,895 $ 24,040 + 1 2 3 14 35 36 a Inn Mortgage Highland Hotel Adela Condos Pinehill Lodge New Inn All Locations CH +