5 2020 2021 2022 6 Bank Account $ 81,696 $ 147,907 $ 260,409 7 Accounts Receivable 41,300 65,667 74,340 8 Gym Equipment 15,095 8,000 9,000 9 Merchandise Inventory 40,000 55,000 60,000 10 Total Assets $ 178,091 $ 276,574 $ 403,749 11 Liabilities 12 Accounts Payable $ 18,134 $ 16,244 $ 18,213 13 Current Loans Due 26,000 26,000 26,000 14 Long Term Equipment Loan 174,000 148,000 122,000 15 Total Liabilities $ 218,134 $ 190,244 $ 166,213 16 Retained Earnings (40,043) 86,330 237,536 17 Net Worth $ 178,091 $ 276,574 $ 403,749 18 19 20 21 22 23 24 Choose Data for Chart Types 25 Review the data on the Balance worksheet. What chart type would be best to use to show a comparison of Total Assets, Total Liabilities and Retained earnings for Years 2020, 2021, and 2022? a. Clustered Column Chart 26 27 28 29 Explain why you choose this 30 chart type. b. Pie Chart c. Combo Chart 31 32 Balance Income Membership Edit Charts Summary + Profit and Loss Statement 2020 2021 2022 Income Memberships Clothing Merchandise Other $ 100,000 85,000 4,250 $ 275,000 111,300 5,350 $ 395,000 126,000 6,140 Total Income $ 189,250 $ 391,650 $ 527,140 Expenses Cost of Sales 75,000 $ Payroll 75,000 92,500 180,000 $ 65,000 250,000 Advertising 15,270 17,163 15,879 Education & Training 10,000 12,000 10,000 Insurance 15,000 15,000 15,000 Rent 25,000 25,000 25,000 Repairs & Maintenance 5,000 10,000 3,000 Utilities 12,000 13,200 14,520 Supplies 3,000 3,200 3,800 Total Operating Expenses Net Operating Profit $ 235,270 $ 368,063 $ 402,199 $ (46,020) $ 23,587 $ 124,941 Review the data above. What chart type would be best to use to show the proportional relationship of expenses for 2022? a. Stacked Column chart b. Pie Chart Explain why you choose this chart type. c. Line Chart 0 P Q Better You Fitness Center 2022 Daily Attendance Trends Better You Fitness Center 3-Year Membership Totals 3 Weekend Muit 250 560 935 5 Monday Tuesday Wednesday Thursday Friday Saturday Sunday Apr 250 560 948 6 4am - 6am 50 50 45 40 30 May 255 560 960 7 6am - 8am 125 100 85 110 120 100 90 Jun 275 575 990 B 8am-10am 100 100 110 90 100 125 120 Jul 275 575 990 9 10am-12pm 90 100 100 90 100 75 95 Aug 260 575 990 10 12pm - 2pm 75 65 70 50 25 50 50 Sep 250 585 1025 11 2pm - 4pm 62 50 55 50 35 25 25 Oct 275 750 1025 12 4pm - 6pm 125 140 150 85 35 Nov 275 750 1050 13 6pm - 8pm 125 150 105 150 20 Doc 325 765 1100 14 8pm-10pm 75 20 40 30 20 Jan 500 925 1025 Feb 550 930 1200 15 16 17 18 Create Chart 19 Using the data on this worksheet, create a stacked bar chart that illustrates breakdown of gym visits by hour and by day on the weekday. 20 Create a similar chart for weekend data. Include meaningful titles, legends, and data labels that best reflect the data. 21 22 Then answer the following questions 23 1. If you were the owner and wanted to cut back hours the gym is open without interrupting staffing too much, which time slot(s) would you eliminate? Explain your answer. 24 25 26 27 28 29 30 2. Looking at the charts, what other recommendations would you make about staffing or operating hours during the week? 31 M N P O Using the data on this worksheet, create a stacked bar chart that illustrates breakdown of gym visits by hour and by day on the weekday. Create a similar chart for weekend data. Include meaningful titles, legends, and data labels that best reflect the data. Then answer the following questions .If you were the owner and wanted to cut back hours the gym is open without interrupting staffing too much, which time slot(s) would you eliminate? Explain your answer. 2. Looking at the charts, what other recommendations would you make about staffing or operating hours during the week? Sparklines Add Sparklines to the bottom of the 3-Year Membership Totals data. Format the sparklines as follows: a. Choose column sparklines b. Show the Highpoints c. Increase the row height to better display the data. What conclusion can you draw from the Sparkline data? Balance Income Membership Edit Charts Summary