Break-even Analysis Part 1 Constructing a Spreadsheet

1. Break-even Analysis Part 1 Constructing a Spreadsheet

2. To publish or not to publish... The Babson Press must decide whether or not to publish a book. The estimated costs of publishing this book include a fixed cost (in leased equipment, rent, …) of $3,000 plus a variable cost (in materials, labor, …) of $10 for each copy printed. Projected sales figure suggest that the book can be sold at $16 per copy.Under what condition should they publish this book? Start with a problem to be formulated

3. cost function profit function revenue function Formulate the Model as a Break-even analysis problem Babson Press needs to make money. (This is a business college after all.) The decision on publishing the book will be based upon whether or not they can make a profit. Ifq = quantity of copies printed and sold the cost of printing q copies is the profit (revenue - cost) of printing and selling q copies is 10 q + 3,000 the revenue of selling q copies is (16 - 10)q - 3,000 16 q Babson Press wants to determine how many copies should be printed to break even (profit is zero).

4. Formulate the Model as a Break-even analysis problem Babson Press needs to make money. (This is a business college after all.) The decision on publishing the book will be based upon whether or not they can make a profit. Ifq = quantity of copies printed and sold the cost of printing q copies is the profit (revenue - cost) of printing and selling q copies is variable cost per unit 10 q + 3,000 fixed cost the revenue of selling q copies is (16 - 10)q - 3,000 selling price per unit 16 q Babson Press wants to determine how many copies should be printed to break even (profit is zero).

5. Preparing the Worksheet start with a blank sheet enter descriptions, labels, & values for the constants NOTE: From this point on, cells C1:C4 will be colored blue to indicate that the same spreadsheet can be used to analyze different instances of the problem by just modifying the contents of the blue cells Enter constants’ descriptions in cells A1:A4 Enter constants’ labels in cells B1:B4 Enter constants in cells C1:C4

6. Preparing the Worksheet name the constants in the blue cells Starting from the main menu bar click on Insert | Name | Create Highlight the eight cells B1:C4

7. Preparing the Worksheet name the constants in the blue cells Check the Left column box

8. Preparing the Worksheet name the constants in the blue cells Click on OK

9. Preparing the Worksheet name the constants in the blue cells Cell C1 is named v and its current value is 10 Click on C1 to check its name

10. Preparing the Worksheet name the constants in the blue cells Cell C2 is named F and its current value is 3000 Click on C2 to check its name

11. Preparing the Worksheet name the constants in the blue cells Cell C3 is named p and its current value is 16 Click on C3 to check its name

12. Preparing the Worksheet name the constants in the blue cells Cell C4 is named q_incr and its current value is 50 Click on C4 to check its name

13. Preparing the Worksheet enter description & label for the variable Enter variable’s description in cell E1 Enter variable’s label in cell E2

14. Preparing the Worksheet enter starting value for the variable Enter the variable’s starting value 0 in cell E3

15. Preparing the Worksheet enter the formula which defines the remaining values formula entered in E4 current value for the formula entered in E4 Click on cell E4 Click on cell E3 Click on cell C4 Type + Type = Enter NOTE: The formula adds the 50 in the blue cell C4 to the 0 of cell E3

16. Preparing the Worksheet enter the formula which defines the remaining values NOTE: From this point on, only the spreadsheet portion of the Excel window will be displayed

17. Preparing the Worksheet copy down the formula Click on cell E4 and grab the fill handle + in the lower right corner NOTE: Cells E3:E23 contain the possible quantities Copy the formula in E4 down to cell E23

18. Preparing the Worksheet name the values for the variable NOTE: From this point on, the Excel window and only part of the spreadsheet will be displayed Highlight cells E2:E23

19. Preparing the Worksheet name the values for the variable Starting from the main menu bar click on Insert | Name | Create

20. Preparing the Worksheet name the values for the variable Check the Top row box

21. Preparing the Worksheet name the values for the variable Click on OK

22. Preparing the Worksheet name the values for the variable NOTE: The range of cells C3:C23 is named q

23. Preparing the Worksheet enter descriptions and labels for the functions Enter functions’ description in cells F1:H1 Enter functions’ labels in cells F2:H2

24. Preparing the Worksheet enter formulas for the functions formula entered in F3 cost function 10q + 3000 q (cell E3) is currently 0 current value for the formula entered in F3 Click on cell F3 to check your formula Type =v*q+F Click on cell F3 Enter

25. Preparing the Worksheet enter formulas for the functions formula entered in G3 revenue function 16q q (cell E3) is currently 0 current value for the formula entered in G3 Click on cell G3 to check your formula Click on cell G3 Type =p*q Enter

26. Preparing the Worksheet enter formulas for the functions formula entered in H3 profit function (16 - 10)q -3000 q (cell E3) is currently 0 current value for the formula entered in H3 Click on cell H3 to check your formula Type =(p - v)*q - F Click on cell H3 Enter

27. Preparing the Worksheet enter formulas for the functions NOTE: From this point on, only the spreadsheet portion of the Excel window will be displayed

28. Preparing the Worksheet copy down the formulas Grab the fill handle + in the lower right corner of the highlighted cells Copy the formulas in C3:H3 down to C23:H23 Highlight cells F3:H3

29. Babson Press can print & sell 0 books at a cost of $ 3,000 with revenue of $ 0 and a profit of -$ 3,000 Babson Press can print & sell 900 books at a cost of $ 12,000 with revenue of $ 14,400 and a profit of $ 2,400 Babson Press can print & sell 500 books at a cost of $ 8,000 with revenue of $ 8,000 and a profit of $ 0 lucky Preparing the Worksheet interpret the table Babson Press will break even if it prints 500 copies QUESTION: What can you do if the break even quantity is not listed on the table? Interpret cells E21:H21 Interpret cells E13:H13 Interpret cells E3:H3