INTRODUCTION TO FINANCE

SAMPLE CASH FLOW TEMPLATE*

* The attached template has been prepared so that you may feel comfortable preparing spreadsheet models. Recall in class I emphasized the cash flows for a typical year, and then years 0 and the final year of your analysis. The attached provides you a complete example and a template that executes the analysis. We have not given you the spreadsheet, but an image of it with detailed notes, because we want you to recreate a spreadsheet yourself. Doing so before executing the analsysis in some of the questions on the Assignments will hopefully be helpful.

Sample Cash Flow Template
Maize Medical Supply (MMS) is a leader in the production and sales of X‐ray machines. This past year, MMS posted $35 million (M) in revenue with a cost of goods sold of $22M and SG&A expenses of $2M. They are projecting a growth of these numbers of 8%, 6%, and 2%, respectively, over the next 5 years. When considering their short term assets and obligations, MMS has $4M in accounts receivable, $3M in accounts payable and $5 of inventory. The company's revenue growth will be largely driven by spending $20M today on an equipment upgrade, which will also lead to a $2M increase in accounts receivable next year. The expenditure for the equipment upgrade can be depreciated using the straight line method over the next 5 years. MMS faces a 35% tax rate, a 14% discount rate and they are projected to be worth $30M at the end of our valuation horizon of 5 years. What is the value of MMS today? Information given in the problem (all dollar figures in millions)
Initial Firm Conditions Revenue COGS SG&A Accounts Receivable Inventory Accounts Payable $ 35.00 $ 22.00 $ 2.00 $ 4.00 $ 5.00 $ 3.00 Future Firm Conditions Revenue Growth 8% COGS Growth 6% SG&A Growth 2% One‐time AR increase $ 2.00 Terminal Value $ 30.00 Terminal Value Year 5 Rates tax rate disc rate 35% 14%

Capital Expenditure Intitial investment $ 20.00 Expected life 5

Calculations
Annual depreciation during equipment life Time Working Capital Change in WC Time Revenue COGS SG&A Less Dep Operating Profit Tax NOPAT Plus Dep Change in Working Capital CAPX Free Cash Flow PV(FCF) PV(Terminal Value) Total NPV Total NPV 0 6 1 8 2 $ 4.00 2 8 0 2 $ 37.80 $ (23.32) $ (2.04) $ (4.00) $ 8.44 $ (2.95) $ 5.49 $ 4.00 $ ‐ $ ‐ $ 9.49 $ 7.30 3 8 0 3 $ 40.82 $ (24.72) $ (2.08) $ (4.00) $ 10.02 $ (3.51) $ 6.52 $ 4.00 $ ‐ $ ‐ $ 10.52 $ 7.10 4 8 0 4 $ 44.09 $ (26.20) $ (2.12) $ (4.00) $ 11.77 $ (4.12) $ 7.65 $ 4.00 $ ‐ $ ‐ $ 11.65 $ 6.90 5 8 AR + Inv ‐ AP 0 WC(this period) ‐ WC(last period) 5 $ 47.62 $ (27.77) $ (2.16) $ (4.00) $ 13.68 $ (4.79) $ 8.89 $ 4.00 $ ‐ $ ‐ $ 12.89 $ 6.69 $ 15.58

1 $ 35.00 $ (22.00) $ (2.00) $ (4.00) $ ‐ $ 7.00 $ ‐ $ (2.45) $ ‐ $ 4.55 $ 4.00 $ ‐ $ (2.00) ($20.0) $ ‐ $ (20.00) $ 6.55 $ (20.00) $ 5.75 $ ‐ $ ‐ $ ‐

0

(Last Year's Revenue) * (1 + Revenue Growth) (Last Year's COGS * (1 + COGS Growth) (Last Year's SG&A * (1 + SG&A Growth) Investment / Expected Life [if applicable] Sum of the above four items (Operating Profit) * (1 ‐ Tax Rate) Sum of the above two items Less INCREASES in WC from earlier calculation Sum of the above four items each year's FCF discounted to today's dollars terminal value discounted to today's dollars

$ 29.31 Summing present values of the free cash flows and the present value of the terminal value $ 29.31 Computing the NPV with help from Excel's NPV function