Here is a 1-2-3 spreadsheet that shows the breakdown of the costs. If you have your HP 95LX handy, key this in and check the numbers. After you enter Lotus press <MENU> Worksheet Global Format Currency 2 to format the worksheet.
A
B
C
1 Fixed Costs $500.00 Hardware
2 $70.00 Development Software
3 $11.86 Photo Ready Copy
4 $25.24 Review Copies
5 $435.00 Development time.
6 $1,042.10 TOTAL FIXED
7
8 Var Costs $0.90 Disk
9 $0.63 Mailer
10 $1.21 US Postage
11 $3.57 Printing
12 $6.31 TOTAL VAR
Figure 1
The formulas in cells B9 and B12 are, respectively, @SUM(B1..B5) and @SUM(B8..B11). While you're keying this in, press <MENU> Range Name Create FIXCOSTS for cell B9 and press <MENU> Range Name Create VARCOSTS for cell B12. This will let you link these cells with the following worksheet.
Save this spreadsheet as COSTS.WK1 * and then press <MENU> Worksheet Erase Yes to clear 1-2-3 to start a new worksheet.
The break-even analysis spreadsheet looks like this.
A
B C
D
1 BREAK-EVEN ANALYSIS
2 ___________________________
3 SELLING PRICE PER UNIT: $29.95
4
5 TOTAL FIXED COSTS:$ 1,042
6
7 VARIABLE COST PER UNIT: $6.31
8 ___________________________
9
10 BREAK EVEN QUANTITY: 44
11
12
13 X A B
14 BE SALES COST
15 0.0 00 1042.1
16 8.8 264.1 1097.7
17 17.6 528.1 1153.4
18 26.5 792.2 1209.0
19 35.5 1056.2 1264.7
20 44.1 1320.3 1320.3
21 52.9 1584.4 1375.9
22 61.7 1848.4 1431.6
23 70.5 2112.5 1487.2
24 79.4 2376.5 1542.9
25 88.2 2640.6 1598.5
Figure 2
Figure 3 below is a listing of formulas used to develop this spreadsheet. The Widths for columns A and B are 5 and 8. In the listing, the \- characters represent a series of dashes which have been copied across the cells in rows 2 and 8.
Note the formulas in cells D5 and D7. This type of formula is new to Lotus ver 2.2. The formulas link the cells in the current worksheet to a cell, or range of cells, in a worksheet that is on the disk. The general format is +<<Filename>>Cell (or range name).
The formula in cell D10 solves for the break even point. The cell is formatted to show only whole numbers with commas. The formula says "If the value in D3 (the selling price) is 0, then show "0". Otherwise show the result of dividing the Fixed Costs by the difference between the Price and the Variable Costs: D5/(D3-D7).
* Available on The HP Palmtop Paper on Disk, Mar/Apr 92
The bottom part of the spreadsheet consists of three columns of numbers. These are used to develop a graph of break-even analysis. You don't have to key in the formulas for each cell. Just key in the formulas and labels for the cells in rows 13 through 16. Wherever you see $D$7, type in D7 and press F4 to make the cell reference absolute. Copy the three cells from row 16 down to row 25.
To develop the graph, press <MENU> Graph Type Line. The X range is B15..B25. The A and B ranges are C15..C25 and D15..D25, respectively. Press <MENU> Graph Options Legend to set the A and B legends to SALES and COST. Press <MENU> Graph Options Title and make the First title BREAK-EVEN ANALYSIS. The X and Y axes are Units and Dollars, respectively.
When you're ready, press <CTRL>--< Backspace > to get back to the spreadsheet. Press F10 (GRAPH) and use the plus key (+) to view the graph. Readable graphs are one of the strong suits of Lotus 1-2-3. This graph shows the rising cost of producing items. The more quickly rising line shows the income. Where the income line cuts across the cost line, the break-even point is reached. A quick inspection shows that the break-even point is at 44.1 units.
To finish the worksheet, press <MENU> Worksheet Global Protect Enable. Then press <MENU> Range Unprotect with each cells (D3, D5, and D7).
Save this worksheet by pressing <MENU> File Save BRKVNAN *. Leave 1-2-3 running.