|
|
|
|
Break-even Analysis
Although the questions above seem diverse, you can get a good start on making the correct decision by applying the same basic spreadsheet technique to each. The basic approach is to describe the alternatives mathematically and compare "what happens if…?" You can model each scenario alternative in Excel by building a table for various values of the interested inputs. You can graph the table for a better visual representation of your options. Yes, I know this paragraph has been vague but it will become much clearer as we do some examples. A Basic Break-even Analysis Model Cost-Volume-Profit Model This first example is the classic break-even model that examines the cost-volume-profit relationship of a business. What we want to determine is how many units of some product we should produce in order to start making a profit. The point at which we sell enough units of our product so that our profit equals our costs is the break-even point. As with many of our models, this example must be simple enough to introduce the basic concepts without getting bogged down in details that are inherent in real world problems. Consequently we must make some horrendously unrealistic assumptions. Despite these assumptions, the cost-volume-profit relationship and decision making methodology is extremely valuable and can easily be extended to more complex problems. Some assumptions for our basic model
Before we start number crunching it is important that you understand the concepts and problem solving approaches. With practice you will be able to recognize a situation as one fitting the break-even paradigm, identify the salient information, structure the problem appropriately, put the problem into a spreadsheet format, develop tables and graphs that display the situation accurately, draw some conclusions from your analysis, and finally, explain your analysis to an audience that may not be familiar with the situation. A very basic mathematical representation of the cost-volume-profit model is: Profit (P) = Revenue (R) - Cost (C) Revenue (R) = Selling Price per unit (p) x Quantity Sold (S) Cost has two basic components, Fixed Costs and Variable Costs. In our model we calculate the Variable Costs as Production Cost per unit x Quantity Produced which yields the following mathematical expression for cost Cost(C) = Fixed Costs (F) + [Production Cost per unit(c) x Quantity Produced(Q)] Or P = R - C = pS - [F + cQ] The selling price, p, the production cost per unit, c, and the fixed costs, F, are constants. Under our assumptions, they are fixed and known. The quantity sold, S, can be called an uncontrollable variable since it varies with (and, from our assumptions, is always equal to) the quantity produced. The quantity produced, Q, is our controllable variable; it is the decision variable as our primary objective is to find out how many units we need to produce in order to break even. An Example C-V-P Problem Our product sells for $20 per unit and the production costs are $12 per unit. Fixed costs are $30,000 per day. How many units do we need to produce each day in order to "break-even"? Your spreadsheet should look something like this:
Cells B4:B6 contain constants. Make sure you read and understand this next paragraph. Cell B9 should have "=B12". This puts whatever is in cell B12 into B9. Recall that we assumed that the quantity sold would be exactly the same as the number we produced. If you don't do this your model won't work correctly. As you change B12, B9 should also change. We can put whatever we want into B12 as it is the decision variable. I have arbitrarily chosen to produce 4000 units in the spreadsheet example above. If I have typed in the correct formulas into cells B15:B17 then my revenue,
cost, and profit amounts shown above reflect the decision to make 4000
units. The correct formulas are: Try some other values in cell B12. B15:B17 should change as you "produce" different amounts. Make sure that B9 changes also. For example, if you type 5000 into B12, your profit should be $10,000. At this point we still don't know what the break-even amount is. To determine that, we are going to make a "What-if" Table. This table will show us what our revenue, cost, and profit will be if we produce at different levels of X number of units. Here's what it will look like when you are done.
This section introduces an Excel feature that can be especially useful if you are conducting a complex analysis using spreadsheets. Most of you know other ways to get the correct numbers into a "what-if" table but try this method; it might come in handy in the future. Pay close attention!! After typing in the various column headings in rows 19 and 20, type =B15 into cell C21; =B16 into cell D21; and =B17 in cell E21. All this does is put the contents of B15:B17 into C21:E21. To double-check, change the quantity produced in cell B12 and the contents of all 6 of these cells should change accordingly. Next, use Edit-Fill-Series to fill in B22:B42. What we want to do is to determine what our revenue, cost, and profit will be if we produce 3000, 3100, 3200, 3300, …, 5000 units. We are going to use this "new" feature of Excel to do this. Highlight cells B21:E42 (all four columns and all 22 rows, including the row that you just typed the formula into, but not the rows with headings) then go to Data-Table on the pull-down menus. Type in B12 in the Column input cell of the Table dialog box. Click on OK and your table should look just like the one shown above.
Here is what happened. By typing B12 in the Column input box, you "told" Excel to start looking at the numbers in the leftmost column of the area you just highlighted, that is, cells B22:B42. (It ignores row 21, but you have to highlight it) Excel then "takes" each number in that column and "puts" it into cell B12, one at a time. As each number is put into B12, the revenue, cost, and profit change in cells B15:B17 and hence C21:E21. As each number is put into B12, Excel takes whatever values are in cell C21:E21 (the uppermost row of the area highlighted) and copies those values into the cells to the right of the respective B column cell. For example, the first step effectively inserts 3000 (the value of cell B22) into cell B12, the values $60000, $66000, and -$6000 show up in B15:B17 and C21:E21 respectively. Those results in C21:E21 are copied into C22:E22. Then the next number in the column, 3100, is put into cell B12, $62000, $67200, and -$5200 show up in cells C21:E21 and are copied into C23:E23, the three cells to the right of 3100. This process continues until the results for the entire column of numbers, B22:B42, are evaluated. Of course, you can't see any of this happening; you only see the final results.
The break-even point, the point where revenue equals cost, has a profit of zero. By looking at the profit column in the what-if table, you can see that the break-even point is between 3700 and 3800 units. If we produce 3700 units, we have a loss of $400, if we produce 3800 units we have a profit of $400. How did I know to make the table values for the units produced range from 3000 to 5000? Answer - Trial and error! How could we modify the table to find the break-even point to within $1? Break-even analysis is shown even better graphically. I used a scatterplot chart with lines to make the graph below. You should be able to duplicate it!
Setting up the spreadsheet, making the what-if table, and producing a
graph for any situation should be a matter of routine after some practice.
What is even more important is that you can relate the table and graph
to the scenario. Solving for the break-even point mathematically. At breakeven, revenue equals the costs (R = C), so we can write pS = F + (cQ). Substituting the values (recall we sell, all we make so S = Q): 20Q = 30000 + 12Q or 8Q = 30000 so Q = 3750 units, the breakeven amount.
A metal shop is frequently hired to drill holes in metal parts. For one particular part, a company always sends orders of between 1 and 6 batches with 100 parts in each batch. The metal shop has two choices for drilling holes. It can use an automatic robot. It takes 4 hours of special setups and computer programming to get the robot ready to drill but once it is ready it works very fast and can drill 50 parts per hour. The shop can also elect to drill the holes manually. A skilled machinist requires no setup time but takes 3 hours to drill 100 parts manually. For what order size do the two methods take approximately the same amount of time?
You should see that the breakeven point (some texts call it the point of indifference) is at 400 parts. Using either method takes 12 hours. If we have less than 400 parts the analysis indicates that the manual method is quicker; greater than 400 part, the robot is quicker. Assignments: For a maximum grade of B-
|
|
|
 |
