EXCEL INDEPENDENT PROJECT BREAK EVEN ANALYSIS This project is not found in your book Use what you have learned so far in the course and apply critical thinking skills to stretch the limits of your knowledge Read the assignment instructions carefully below Compare your results to the grading rubric before submitting Open the IP PhoneCases xlsx file Save the file as Lastname Firstname IP PhoneCases xlsx You work for a cell phone case manufacturer and have been given the task of analyzing the breakeven point based for several revenue and expense scenarios Currently, you manufacture phone cases that cost you $11 to make You sell the cases for $19 Your fixed overhead (rent, utilities, insurance, etc ) is $35,000 per month You would like to calculate your break even point based on the current values and then analyze what happens if you increase your sales price, decrease your manufacturing cost, or decrease your fixed monthly expenses (If you havent had a business class before, then try googling Break Even Analysis for an overview of the concept The lower the breakeven point, the faster a business will make money ) Create an appropriate range name for each of the following cells B3, B4, B5 and B6 For example, B3 might be named PriceperUnit, B4 CostperUnit, B5 FixedExpenses and B6 BreakEvenPoint Use the range names as you create the formulas below Refer to pages 398 405 in your text to review how to create range names In B6, enter a formula to calculate the break even point (Hint For every cell phone case you sell, you are currently making a profit of $8 ($19 Sales $11 Cost) If you take the fixed overhead of $35,000 and divide it by $8, then you would find that you have to sell 4375 phone cases to break even Be sure to use the range names for B3, B4 and B5 in your break even formula Remember the order of operators as explained on page 84 so use parentheses if needed to make the subtraction happen before the division Next, youd like to look at some projections for Revenue and Expenses if you make between 0 and 20,000 phone cases You have already determined that this is your manufacturing limits based on your fixed monthly expenses Use formulas to calculate the Total Revenue and Total Expenses for the various Units Sold in the table Total Revenue is the Sales Price per Unit times the Units Sold Total Expenses is the Fixed Monthly Expenses plus the Manufacturing Cost per Unit times the Units Sold (Hint You can create a one way data table or you can simply use formulas with absolute cell references where needed and copy them down ) Add a Break Even line chart to show the relationship between Units Sold, Revenue and Expenses When the lines intersect on the chart, you have the break even point Place the chart on the Break Even Analysis sheet Resize and format as needed so the results are professional looking and the data and chart are ready to print (Hint Select A14 C55 as your source data range and look at the recommended line charts The Units Sold should be on the horizontal axis You should have one line for Revenue and one line for Expenses and the two lines should intersect ) Now, lets try running some different scenarios using the Scenario Manager Add the following four scenarios Set B3 B5 as the changing cells and use the values given in the table below Scenario Name Changing Cells B3 B4 B5 Current 19 11 35000 Increase Sales Price 20 11 35000 Decrease Mfg Cost 19 9 35000 Decrease Overhead 19 11 30000 Show each Scenario Then create a Scenario Summary using B6 as the result cell What happens to the break even points (units sold) in each scenario As a manager, which scenario would you try to make happen if they were all possible Why Type your answers in a blank row below the results shown on the Scenario Summary sheet Florida Central Hospital System LastName FirstName Department Job Title YearHired Gender Salary Anderson Kristen Pediatrics Surgical Tech 2013 F 47405 Ball Susan ICU Registered Nurse 2010 F 54229 Ball Robin Pediatrics Registered Nurse 2004 F 59973 Blackwell Dean Cardiology Registered Nurse 2000 M 64047 Bordeau Katherine ICU Surgical Tech 1994 F 46561 Bressette Cheryl Pediatrics Registered Nurse 1995 F 62832 Chin Ellen Cardiology Physical Therapist 2013 F 98417 Chin Roger Pediatrics Physical Therapist 2011 M 83531 Coats Bill Pediatrics Surgical Tech 1998 M 56621 Coats William Pediatrics Surgical Tech 2013 M 44621 Comensoli Angela Cardiology Paramedic 2008 F 48462 Comensoli Bill Cardiology Paramedic 2005 F 43875 Doepke Cheryl Pediatrics Physical Therapist 1986 F 68355 Downs Clifton Pediatrics Paramedic 1990 M 51137 Fitzgerald Edmond ICU Paramedic 2010 M 55207 Gao Xiaoming ICU Physical Therapist 1990 F 70079 Garabizien Wendy ICU Registered Nurse 2002 F 62750 Garafano Karen Cardiology Paramedic 1987 F 47250 Halpern Murray Cardiology Paramedic 2009 M 52250 Halpern Sheryl Pediatrics Physical Therapist 2013 M 88531 Hill Trevor ICU Registered Nurse 1999 M 56840 Jackson Carole Pediatrics Registered Nurse 2006 F 73031 Jackson Mary Pediatrics Registered Nurse 2000 F 62031 Jacobson Andrew ICU Physical Therapist 2012 F 68531 Jacobson Carole ICU Physical Therapist 2010 M 70531 Johnson Sue Cardiology Surgical Tech 2010 F 43945 Lee Kell Cardiology Surgical Tech 2002 M 45250 Lewis Karl ICU Paramedic 1987 M 52637 Lu Jim Pediatrics Registered Nurse 2011 M 78131 Mack Kevin ICU Surgical Tech 1997 M 43750 McKaye Mary ICU Registered Nurse 2002 F 64750 McKaye Susan ICU Registered Nurse 1997 F 88229 Mikkola Claudia Cardiology Paramedic 1984 F 49531 Nelsen Beth Cardiology Physical Therapist 1985 F 86589 Nelsen John Cardiology Physical Therapist 1985 F 79589 Nelson Dale Pediatrics Physical Therapist 1996 M 69828 Neumann Kenneth Cardiology Registered Nurse 2009 M 66631 Orr Ellen Pediatrics Physical Therapist 2005 F 100000 Palermo Sheryl Pediatrics Paramedic 2008 M 50867 Palermo Tom ICU Registered Nurse 2013 M 67840 Parker Mathew Pediatrics Physical Therapist 1982 M 67142 Parker Tom Pediatrics Registered Nurse 2010 M 69973 Rais Wendy Pediatrics Physical Therapist 2004 M 72025 Rais Mary Cardiology Registered Nurse 2001 F 69750 Rothenberger James ICU Physical Therapist 1998 M 86031 Scheib Earl ICU Registered Nurse 1997 M 61639 Smith Alicia Pediatrics Physical Therapist 1997 F 61639 Smith William ICU Physical Therapist 2006 M 72079 Smythe Janice ICU Paramedic 2007 F 41137 Stewart Mark Pediatrics Surgical Tech 2007 M 51866 True David Pediatrics Physical Therapist 1994 M 87431 Weaver Roger Pediatrics Paramedic 1996 M 49867 Weaver Robert Pediatrics Physical Therapist 1984 M 69025 Weeks Jodie Cardiology Registered Nurse 1996 F 74302 Wolter Christine Pediatrics Paramedic 1991 M 48968 Young Jeff ICU Surgical Tech 1998 M 47763

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jul 23, 2024

EXCEL INDEPENDENT PROJECT BREAK EVEN ANALYSIS This project is not found in your book. Use what you have learned so far in the course and

EXCEL INDEPENDENT PROJECT BREAK EVEN ANALYSIS

This project is not found in your book. Use what you have learned so far in the course and apply critical thinking skills to stretch the limits of your knowledge. Read the assignment instructions carefully below.

Compare your results to the grading rubric before submitting.

Open the IP_PhoneCases.xlsx file. Save the file as Lastname_Firstname_IP_PhoneCases.xlsx.

You work for a cell phone case manufacturer and have been given the task of analyzing the breakeven point based for several revenue and expense scenarios. Currently, you manufacture phone cases that cost you $11 to make. You sell the cases for $19. Your fixed overhead (rent, utilities, insurance, etc.) is $35,000 per month. You would like to calculate your break-even point based on the current values and then analyze what happens if you increase your sales price, decrease your manufacturing cost, or decrease your fixed monthly expenses. (If you havent had a business class before, then try googling Break Even Analysis for an overview of the concept. The lower the breakeven point, the faster a business will make money.)

Create an appropriate range name for each of the following cells: B3, B4, B5 and B6. For example, B3 might be named PriceperUnit, B4 CostperUnit, B5 FixedExpenses and B6 BreakEvenPoint. Use the range names as you create the formulas below. Refer to pages 398-405 in your text to review how to create range names.

In B6, enter a formula to calculate the break-even point. (Hint: For every cell phone case you sell, you are currently making a profit of $8 ($19 Sales - $11 Cost). If you take the fixed overhead of $35,000 and divide it by $8, then you would find that you have to sell 4375 phone cases to break even. Be sure to use the range names for B3, B4 and B5 in your break-even formula. Remember the order of operators as explained on page 84 so use parentheses if needed to make the subtraction happen before the division.

Next, youd like to look at some projections for Revenue and Expenses if you make between 0 and 20,000 phone cases. You have already determined that this is your manufacturing limits based on your fixed monthly expenses.

Use formulas to calculate the Total Revenue and Total Expenses for the various Units Sold in the table. Total Revenue is the Sales Price per Unit times the Units Sold. Total Expenses is the Fixed Monthly Expenses plus the Manufacturing Cost per Unit times the Units Sold. (Hint: You can create a one way data table or you can simply use formulas with absolute cell references where needed and copy them down.)

Add a Break Even line chart to show the relationship between Units Sold, Revenue and Expenses. When the lines intersect on the chart, you have the break-even point. Place the chart on the Break Even Analysis sheet. Resize and format as needed so the results are professional looking and the data and chart are ready to print. (Hint: Select A14:C55 as your source data range and look at the recommended line charts. The Units Sold should be on the horizontal axis. You should have one line for Revenue and one line for Expenses and the two lines should intersect.)

Now, lets try running some different scenarios using the Scenario Manager! Add the following four scenarios. Set B3:B5 as the changing cells and use the values given in the table below:

Scenario Name	Changing Cells: B3	B4	B5
Current	19	11	35000
Increase Sales Price	20	11	35000
Decrease Mfg Cost	19	9	35000
Decrease Overhead	19	11	30000

Show each Scenario. Then create a Scenario Summary using B6 as the result cell. What happens to the break even points (units sold) in each scenario? As a manager, which scenario would you try to make happen if they were all possible? Why? Type your answers in a blank row below the results shown on the Scenario Summary sheet.

Florida Central Hospital System

LastName	FirstName	Department	Job Title	YearHired	Gender	Salary
Anderson	Kristen	Pediatrics	Surgical Tech	2013	F	47405
Ball	Susan	ICU	Registered Nurse	2010	F	54229
Ball	Robin	Pediatrics	Registered Nurse	2004	F	59973
Blackwell	Dean	Cardiology	Registered Nurse	2000	M	64047
Bordeau	Katherine	ICU	Surgical Tech	1994	F	46561
Bressette	Cheryl	Pediatrics	Registered Nurse	1995	F	62832
Chin	Ellen	Cardiology	Physical Therapist	2013	F	98417
Chin	Roger	Pediatrics	Physical Therapist	2011	M	83531
Coats	Bill	Pediatrics	Surgical Tech	1998	M	56621
Coats	William	Pediatrics	Surgical Tech	2013	M	44621
Comensoli	Angela	Cardiology	Paramedic	2008	F	48462
Comensoli	Bill	Cardiology	Paramedic	2005	F	43875
Doepke	Cheryl	Pediatrics	Physical Therapist	1986	F	68355
Downs	Clifton	Pediatrics	Paramedic	1990	M	51137
Fitzgerald	Edmond	ICU	Paramedic	2010	M	55207
Gao	Xiaoming	ICU	Physical Therapist	1990	F	70079
Garabizien	Wendy	ICU	Registered Nurse	2002	F	62750
Garafano	Karen	Cardiology	Paramedic	1987	F	47250
Halpern	Murray	Cardiology	Paramedic	2009	M	52250
Halpern	Sheryl	Pediatrics	Physical Therapist	2013	M	88531
Hill	Trevor	ICU	Registered Nurse	1999	M	56840
Jackson	Carole	Pediatrics	Registered Nurse	2006	F	73031
Jackson	Mary	Pediatrics	Registered Nurse	2000	F	62031
Jacobson	Andrew	ICU	Physical Therapist	2012	F	68531
Jacobson	Carole	ICU	Physical Therapist	2010	M	70531
Johnson	Sue	Cardiology	Surgical Tech	2010	F	43945
Lee	Kell	Cardiology	Surgical Tech	2002	M	45250
Lewis	Karl	ICU	Paramedic	1987	M	52637
Lu	Jim	Pediatrics	Registered Nurse	2011	M	78131
Mack	Kevin	ICU	Surgical Tech	1997	M	43750
McKaye	Mary	ICU	Registered Nurse	2002	F	64750
McKaye	Susan	ICU	Registered Nurse	1997	F	88229
Mikkola	Claudia	Cardiology	Paramedic	1984	F	49531
Nelsen	Beth	Cardiology	Physical Therapist	1985	F	86589
Nelsen	John	Cardiology	Physical Therapist	1985	F	79589
Nelson	Dale	Pediatrics	Physical Therapist	1996	M	69828
Neumann	Kenneth	Cardiology	Registered Nurse	2009	M	66631
Orr	Ellen	Pediatrics	Physical Therapist	2005	F	100000
Palermo	Sheryl	Pediatrics	Paramedic	2008	M	50867
Palermo	Tom	ICU	Registered Nurse	2013	M	67840
Parker	Mathew	Pediatrics	Physical Therapist	1982	M	67142
Parker	Tom	Pediatrics	Registered Nurse	2010	M	69973
Rais	Wendy	Pediatrics	Physical Therapist	2004	M	72025
Rais	Mary	Cardiology	Registered Nurse	2001	F	69750
Rothenberger	James	ICU	Physical Therapist	1998	M	86031
Scheib	Earl	ICU	Registered Nurse	1997	M	61639
Smith	Alicia	Pediatrics	Physical Therapist	1997	F	61639
Smith	William	ICU	Physical Therapist	2006	M	72079
Smythe	Janice	ICU	Paramedic	2007	F	41137
Stewart	Mark	Pediatrics	Surgical Tech	2007	M	51866
True	David	Pediatrics	Physical Therapist	1994	M	87431
Weaver	Roger	Pediatrics	Paramedic	1996	M	49867
Weaver	Robert	Pediatrics	Physical Therapist	1984	M	69025
Weeks	Jodie	Cardiology	Registered Nurse	1996	F	74302
Wolter	Christine	Pediatrics	Paramedic	1991	M	48968
Young	Jeff	ICU	Surgical Tech	1998	M	47763