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 haven't 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, you'd 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, let's try running some different scenarios using theScenario 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 usingB6as 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.

Set all sheets to print professionally. Add a header on all sheets with your name, the file name and the sheet name.

Be sure to save your work when finished.

Grading Rubric for Independent Project Break Even Analysis:

Description

Points

All sheets are formatted professionally. Numbers are right aligned and formatted with appropriate number style. Borders, colors, fonts etc. are used appropriately so data is clear and easy to read.

5

All sheets are set to print. Headers with name, filename and sheet name are included on all sheets.

5

Range names added for B3:B6 and used in formulas.

10

Break-even formula entered in B6.

10

Revenue added in B15:B55 using formulas or a data table.

10

Expense added in C15:C55 using formulas or a data table.

10

Break-even line chart created, resized and formatted.

15

Four scenarios added using Scenario Manager.

20

Scenario Summary Report added with B3:B5 as changing cells and B6 as the result cell.

10

Scenarios analyzed and conclusions entered in blank row on Scenario Summary sheet.

5

Total Points

100

After completing the assignments, attach the file

Lastname_Firstname_IP_PhoneCases.xlsx and submit for grading.

All Cells Phone Covers Sales Price per Unit: Manufacturing Cost per Unit: Fixed Monthly Expense: Break-Even Point in Units: $19 $11 $35,000 Break Even Projections: Units Sold Revenue 0 500 1,000 1,500 2,000 2,500 3,000 3,500 4,000 4,500 5,000 5,500 6,000 6,500 7,000 7,500 8,000 8,500 9,000 9,500 10,000 10,500 11,000 11,500 12,000 12,500 13,000 13,500 14,000 14,500 15,000 15,500 16,000 16,500 17,000 17,500 18,000 Expenses 18,500 19,000 19,500 20,000