Excel spreadsheet needs to be filled out per pdf instructions
Break-Even Analysis for Harmony Fitness Files Needed: Tutorial 9_Analysis_Last_Name.xlsx and Tutorial 9_Analysis_Instructions.pdf Harmony Fitness is a 1000 square foot facility that offers fitness classes, including Yoga, Pilates, Nia, and aerobics. Shelley Getz, the owner of the facility, is evaluating her pricing options for fitness classes. She needs to know how many people need to take a class each week for her to pay instructors, cover her expenses, break even, and then make a profit. She asks you to help her set up a Break-Even Analysis using one and two-variable tables and scenarios. 1. Open the file Tutorial 9_Analysis_Last_Name.xlsx. (Replace “Last Name” with your last name.) 2. Enter data in the Income Statement as stated below: a. There should be 150 participants per week b. The average class price is 12 dollars c. There should be 10 classes d. The average cost per participant is 4 dollars e. The average cost for each instructor per class is 80 dollars f. Monthly rent expenses are 500 dollars g. Monthly advertising expenses are 200 dollars h. Monthly administration expenses are 800 dollars i. Monthly miscellaneous expenses are 500 dollars 3. Enter all the required calculations to determine the Net Income in cell B25. For cell B8, enter the cell address of cell B4. Format currency amounts as needed. Make sure you fill in B4-B6, B8 – B14, B16-B21, and B23-B24.Verify that the Net Income is ($100.00) or -100.00. Income Statement Table 4. Using the data in the Income Statement, set up a One-Variable table starting in cell D3: a) In row 4, enter the appropriate cell addresses to match the column labels. (Should all be from column B) b) Starting in cell D5 and ending in D14, increase the number of participants from 175 in increments of 25 to 400 participants. c) Create the one-variable table to show the revenue, expenses and net income generated from each of the Participant numbers shown in Column D. Remember to enter the number of participants per week from cell B4 as the column input cell. (see tutorial on how to make a 1-variable data table) 5. In the range D16:G30, create a Scatter chart with Straight Lines and Markers from the one-variable data table. 6. Change the charts colors to Monochromatic Pallet 7. If you don’t have this. Use a greyscale palette as seen below: 7. Change the X-Axis to start at 100 and include the title Break-Even Analysis. Net Income Analysis Table 8. Using the data in the Income Statement, set up a Two-Variable table starting in cell I3: a) In Row 4, show five different class costs: $10, $12, $15, $18, and $20. b) Reference the cell address Net Income amount G4 in cell I4. c) Enter increments from 175 to 400 starting in cell I5 and ending in cell I14. d) Create the two-variable table using cell B4 for the column input cell and B5 for the row input cell. (see tutorial on how to make a 2-variable data table) 9. In the range I16:N30, create a Scatter chart with Straight Lines and Markers from the two-variable data table. 10. Change the charts colors to Monochromatic Pallet 7, as seen in number 6 above. 11. Change the X-Axis to start at 100, change the name of each series to match the class costs (e.g., $10, $12, etc.) and enter Net Income Analysis as the chart title. 12. Create three scenarios as follows (DO NOT COPY THIS TABLE INTO YOUR EXCEL FILE, YOU HAVE TO USE THE SCENARIO MANAGER. THIS TABLE IS ONLY FOR REFERENCE. ENTER THE VALUES BELOW INTO YOUR 3 SCENARIOS). Be sure to add the total revenue, total expenses, and net income as RESULTS CELLS, no input values required: a) Use the Ctrl key to select and name the five input cells, then create each of the three scenarios with the values shown above. Enter appropriate text for the comments. b) In the scenario manager, create a scenario summary report of the three scenarios. Make sure you display the total revenue, total expenses, and net income under each scenario. As you can see, Shelley needs to seriously rethink her business model. Only the Maximum Classes scenario generates a profit. 13. Format the Income worksheet in Landscape orientation and fit the contents to one page. 14. Save the file and submit it to your instructor. Grading Rubric Grade Item: Points Income statement table 20pts 1-variable data table 20pts 1-variable data table chart 10pts 2-variable data table 20pts 2 variable data table chart 10pts Scenario Manager 20pts File named correctly Incorrect examples including but not limited to: Tutorial 9_Analysis_Last_Name (1) or Tutorial 9_Analysis_Last_Name.xlsx.xlsx 5 pts Common Additional Deductions: Submitting identical files as someone else in the class(s) -100pts and a formal report filed. Submitting identical file from another semester -100pts and a formal report filed. Taking liberties and making changes you want to make and not ones that are in the instructions. Ie formatting etc. -10~100pts depending upon infraction Using your own excel document instead of the one provided -100pts Income Harmony Fitness Income StatementBreak-Even AnalysisNet Income Analysis Weekly RevenueParticipantsRevenueExpensesNet IncomeAverage Cost Per Class Participants per Week Average Class Price Total Revenue Weekly Variable Expenses Participants Classes Average Equipment Cost per Participant Total Equipment Cost Average Instructor Cost per Class Total Instructor Costs Total Variable Expenses Weekly/Montly Fixed Expenses Montly Rent Montly Advertising Montly Administrative Montly Miscellaneous Total Monthly Fixed Expenses Total Weekly Fixed Expenses (Montly/4) Weekly Summary Total Revenue Total Weekly Expenses (fixed + variable) Net Income (Total Rev - Total Weekly Exp)