There are three steps to using this spreadsheet: (1) Make assumptions about operations in the Operations worksheet. (2) Make assumptions about financing in the Financing worksheet. (3) Use the Solver in the Financing worksheet to minimize bank notes payable subject to maintaining the minimum cash balance required. This step is explained in the Financing worksheet. The only cells that should be changed are in the Operations Assumptions and Financing Assumptions sections. The spreadsheet will generate income statements, balance sheets, and statements of cash flows.
Kerry Back John M. Olin School of Business 13-Jan-97
Page 1
A B C D E F 1 OPERATIONS AND INVESTMENTS ASSUMPTIONS 2 3 4 Year 0 1 2 3 4 5 6 Sales 400 600 800 900 7 COGS (% of sales) 50% 50% 50% 50% 8 SG&A (% of sales) 30% 30% 30% 9 Income tax rate 40% 40% 40% 10 11 Inventory (% of COGS) 10% 10% 10% 10% 12 Minimum cash balance (% of sales) 4% 4% 4% 4% 13 Accounts receivable (% of sales) 12% 12% 12% 12% 14 Accounts payable (% of COGS) 8% 8% 8% 8% 15 16 Investment in PP&E 400 100 100 100 17 Depreciation 100 100 100 18 19 20 OPERATIONS AND INVESTMENTS CALCULATIONS 21 22 Year 0 1 2 3 23 24 Average sales 200 500 700 850 25 Average COGS 100 250 350 425 26 Inventory 10 25 35 42.5 27 Minimum cash balance 8 20 28 34 28 Accounts receivable 0 60 84 102 29 Accounts payable 8 20 28 34 30 31 Note: There is a timing issue when comparing a balance sheet to an income statement, 32 because the balance sheet is at a point in time whereas the income statement is cumulative 33 over the preceding year. It is most natural to predict assets and liabilities based on the 34 average of the income statement items over the same (=preceding) year and the following 35 year.
G
Page 2
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42
A FINANCING ASSUMPTIONS
B
C
D
E
Interest rate on long-term debt Interest rate on notes payable Year Long-term debt outstanding Bank notes payable Net new equity capital raised Dividends paid (as positive number)
11% 9% 0 200 300 1 200 28 2 200 69 3 200 62 -
FINANCING CALCULATIONS Interest Expense Year Long-term debt Bank notes payable Total Cash Balance Year Beginning cash balance Plus cash flow from ops, invests, and fin Ending cash balance Less minimum cash balance required Excess cash 98 98 (8) 90.0 0 1 98 (78) 20 (20) 2 20 8 28 (28) 3 28 6 34 (34) 0 1 22.0 1.3 23.3 2 22.0 4.4 26.4 3 22.0 5.9 27.9
It is undesirable to hold excess cash when there are bank notes outstanding. The cash should be used to reduce the bank loan. To do this, minimize the present value of the interest expense subject to excess cash being nonnegative. Click on Solver (from the Tools menu), click on Solve in the next box that appears, and click on OK in the next box. Present value of interest on bank notes 9.38
Page 3
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
A INCOME STATEMENTS Year Sales Less COGS Gross profit Less SG&A expenses Less depreciation Earnings before interest and taxes Less interest expense Pre-tax income Cumulative pre-tax income (NOL) Taxes Pre-tax income Less taxes Net income
B
C
D
E
F
0
1 400 (200) 200 (120) (100) (20) (23) (43) (43) (43) (43)
2 600 (300) 300 (180) (100) 20 (26) (6) (50) (6) (6) -
3 800 (400) 400 (240) (100) 60 (28) 32 (18) 32 32
Note: Net operating losses can be carried forward or backwards. There is a limit on the number of years they can be carried forward or backwards, but this is not incorporated into the spreadsheet.
Page 4
A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 BALANCE SHEETS
B
C
D
E
Year Cash Inventory Accounts receivable Total current assets Gross property, plant & equipment Less accumulated depreciation Net property, plant & equipment Total assets Accounts payable Bank notes payable Total current liabilities Long-term debt Shareholders equity Total long-term debt and shareholders equity Total liabilities
0 98 10 108 400 400 508 8 8 200 300 500 508
1 20 25 60 105 500 (100) 400 505 20 28 48 200 257 457 505
2 28 35 84 147 600 (200) 400 547 28 69 97 200 250 450 547
3 34 43 102 179 700 (300) 400 579 34 62 96 200 282 482 579
Page 5
A 1 STATEMENTS OF CASH FLOWS 2 3 4 Year 5 6 Net income 7 Plus depreciation 8 Less increase in inventory 9 Less increase in accounts receivable 10 Plus increase in accounts payable 11 Cash flow from operations 12 Less investment 13 Cash flow from operations and invests 14 Plus net new equity capital raised 15 Less dividends paid 16 Plus net new long-term debt 17 Plus net new bank borrowings 18 Cash flow from ops, invests, and fin 19 Beginning cash balance 20 Ending cash balance
B
C
D
E
0
1 (43) 100 (15) (60) 12 (6) (100) (106) 28 (78) 98 20
2 (6) 100 (10) (24) 8 68 (100) (32) 40 8 20 28
3 32 100 (8) (18) 6 113 (100) 13 (7) 6 28 34
(10) 8 (2) (400) (402) 300 200 98 98
Page 6