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 1123456789 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 A B C D E F G OPERATIONS AND INVESTMENTS ASSUMPTIONS Year 0 1 2 3 4 Sales 400 600 800 900 COGS (% of sales) 50% 50% 50% 50% SG&A (% of sales) 30% 30% 30% Income tax rate 40% 40% 40% Inventory (% of COGS) 10% 10% 10% 10% Minimum cash balance (% of sales) 4% 4% 4% 4% Accounts receivable (% of sales) 12% 12% 12% 12% Accounts payable (% of COGS) 8% 8% 8% 8% Investment in PP&E 400 100 100 100 Depreciation -100 100 100 OPERATIONS AND INVESTMENTS CALCULATIONS Year 0 1 2 3 Average sales 200 500 700 850 Average COGS 100 250 350 425 Inventory 10 25 35 42.5 Minimum cash balance 8 20 28 34 Accounts receivable 0 60 84 102 Accounts payable 8 20 28 34 Note: There is a timing issue when comparing a balance sheet to an income statement, because the balance sheet is at a point in time whereas the income statement is cumulative over the preceding year. It is most natural to predict assets and liabilities based on the average of the income statement items over the same (=preceding) year and the following year. Page 2123456789 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 B C D E FINANCING ASSUMPTIONS Interest rate on long-term debt 11% Interest rate on notes payable 9% Year 0 1 2 3 Long-term debt outstanding 200 200 200 200 Bank notes payable -28 69 62 Net new equity capital raised 300 ---Dividends paid (as positive number) ---FINANCING CALCULATIONS Interest Expense Year 0 1 2 3 Long-term debt 22.0 22.0 22.0 Bank notes payable 1.3 4.4 5.9 Total 23.3 26.4 27.9 Cash Balance Year 0 1 2 3 Beginning cash balance -98 20 28 Plus cash flow from ops, invests, and fin 98 (78) 8 6 Ending cash balance 98 20 28 34 Less minimum cash balance required (8) (20) (28) (34) Excess cash 90.0 ---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 3123456789 10 11 12 13 14 15 16 17 18 19 20 21 22 23 A B C D E F INCOME STATEMENTS Year 0 1 2 3 Sales 400 600 800 Less COGS (200) (300) (400) Gross profit 200 300 400 Less SG&A expenses (120) (180) (240) Less depreciation (100) (100) (100) Earnings before interest and taxes (20) 20 60 Less interest expense (23) (26) (28) Pre-tax income (43) (6) 32 Cumulative pre-tax income (NOL) (43) (50) (18) Taxes ---Pre-tax income (43) (6) 32 Less taxes ---Net income (43) (6) 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 4123456789 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 A B C D E BALANCE SHEETS Year 0 1 2 3 Cash 98 20 28 34 Inventory 10 25 35 43 Accounts receivable -60 84 102 Total current assets 108 105 147 179 Gross property, plant & equipment 400 500 600 700 Less accumulated depreciation -(100) (200) (300) Net property, plant & equipment 400 400 400 400 Total assets 508 505 547 579 Accounts payable 8 20 28 34 Bank notes payable -28 69 62 Total current liabilities 8 48 97 96 Long-term debt 200 200 200 200 Shareholders equity 300 257 250 282 Total long-term debt and shareholders equity 500 457 450 482 Total liabilities 508 505 547 579 Page 5123456789 10 11 12 13 14 15 16 17 18 19 20 A B C D E STATEMENTS OF CASH FLOWS Year 0 1 2 3 Net income (43) (6) 32 Plus depreciation 100 100 100 Less increase in inventory (10) (15) (10) (8) Less increase in accounts receivable -(60) (24) (18) Plus increase in accounts payable 8 12 8 6 Cash flow from operations (2) (6) 68 113 Less investment (400) (100) (100) (100) Cash flow from operations and invests (402) (106) (32) 13 Plus net new equity capital raised 300 ---Less dividends paid ----Plus net new long-term debt 200 ---Plus net new bank borrowings -28 40 (7) Cash flow from ops, invests, and fin 98 (78) 8 6 Beginning cash balance -98 20 28 Ending cash balance 98 20 28 34 Page 6