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,
eak 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
. 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 cu
ency amounts as needed. Make sure you fill
in B4-B6, B8 – B14, B16-B21, and B23-B24.Verify that the Net Income is ($ XXXXXXXXXXor
-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
e from column 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.
) 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
ow 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.
) 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 Ru
ic
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 co
ectly
Inco
ect 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
eport filed.
Submitting identical file from another semester -100pts and a formal
eport 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 Statement Break-Even Analysis Net Income Analysis
Weekly Revenue Participants Revenue Expenses Net Income Average 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)