Lecture 15-2 Building Financial Models

1. Lecture 15-2 Building Financial Models • Material for Lecture 15-2 • Read Chapters 13 and 14 • Lecture 15-2 Farm.xlsx

2. Step 1: KOVs • What question is to be answered? • Net cash income? • Cash flow? • Change in net worth? • Number of years to payoff? • Something elseas: NPV, B/C, IRR, EBIT • Scenarios to analyze • Financing, technology, machinery replacement • Marketing strategies, etc. • Risk ranking

3. Step 2: Financial Statements Required • Based on the purpose of the model what calculations are required? • Net cash income – detailed income statement • Sources of all receipts and expenses • Usually an annual model • Cash flow model – income and cash flow statements • Details for cash outflows • Requires a multi-year model • NPV or Change in Net Worth – income, cash flow, and balance sheet • Multi-year models

4. Step 3: What is in an Income Statement? • All sources of receipts • Show receipts by enterprise and type (government, insurance, sales, etc. • DO NOT include interest earned or outside income • All sources of CASH expenses • Show cash costs for each enterprise • Show fixed costs in detail (taxes, insurance, etc) • Show each interest expense, by loan • Net cash income = receipts – expenses

5. Step 4: Parts of a Cash Flow Statement • All sources of cash inflows • Start with beginning cash from t-1 • Net cash income • Interest earnings from cash reserves • Total inflows of cash • All sources of cash outflows • Owner salary and bonus or dividends for corp. • Income taxes, principal payments paid • Down payments for machinery & livestock • Total outflows of cash • Ending cash = Inflows - Outflows

6. Step 5: What is in the Balance Sheet? • Assets • Beginning cash January 1: IF (ending casht > 0) • Land, machinery, livestock market values • Total assets • Liabilities • Cash flow deficit loans: IF (ending casht <0) • Liabilities for land, machinery, livestock • Total liabilities • Net worth = Assets - Liabilities

7. Step 6: Important KOVs • Net present value = -Beginning Net Worth + ∑( dividends or cash withdrawalst/(1+i)t) + [Ending Net WorthT / (1+i)t] • P(Economic Success or NPV > 0) • P(Ending Casht > 0) • P(Net Cash Incomet > 0) • P(Increasing Real Net Worth) • P(Benefit to Cost Ratio > 1.0) • Number of Years to Payoff • Debt/Asset and many more

8. Now ready to Build the Model • Stochastic worksheet -- STOCH • Gather historical data for all stochastic variables • Develop forecasts of random variables • Estimate parameters for alternative distributions and select the best dist. for each variable • Determine whether multivariate or univariate • Simulate the random variables for all years using stochastic forecasts of the random variables • Validate the random variables

9. Program Equations in the Model • Use the stochastic values in the appropriate equations to calculate intermediate variables • Start by programming the equations that will go into the Income Statement, eg. • Receiptst = ∑(Priceit * Yieldit * Acresit) for i crops and each year t

10. Assemble the Financial Tables • Use cell references to map calculated values into the financial statements • The only calculated equations in the financial tables should be • Totals • Operating interest expense • Interest for carryover cash flow deficit loans • Dividends or cash withdrawals or bonus payments • USE IF statements to deal with ending cash in the Balance Sheet. It is either an asset or a liability.

11. What Was Left Out of the Model? • Non-cash expenses • Unpaid family labor • Depreciation • Two kinds of depreciation • Income tax deductions • Decreases in market value (this is included in the balance sheet as we use market value of assets) • Depreciation can be subtracted from net cash income to calculate net income. Has no place in a cash model • These variables do not belong in a cash basis model

12. Organization of a Model • Model worksheet • All input data at the top so you can see the assumptions and easily make changes • Equations for all intermediate financial variables • Pro Forma Financial tables • Debt amortization tables • Income tax schedules and calculations • Stoch worksheet • All forecasting and parameter estimation work with validation summaries

13. Multi-Year Financial Models – Applications • Financial risk management • Analysis of the economic impact of changes in the business plan for a firm on • Ability to repay loans on time • Ability to remain solvent • Ability to earn a satisfactory rate of return on investment • Analysis of alternative marketing schemes that use contracts, futures and options to manage price risk • Testing Portfolios • Analysis of alternative combinations of investment instruments (stocks, bonds, land, etc.) • Analyze enterprise mixes • A portfolio of investments is similar to a derivative in the investment world

14. Financial Risk Management • Uses of this type of model • Test ability of firm to repay operating debt under alternative assumptions about • Other income • Family/dividend withdrawal assumptions • Machinery replacement plans • Re-financing the initial machinery loans • Insurance, pricing, and marketing options for the crops • Farm program provisions • Costs of production including rental rates for land • Purchasing land rather than leasing • Users of this type of model • Lenders concerned about loan solvency • Borrowers concerned about impacts of growth or adding a family member

15. Demonstrate Model Development • Lecture 15-2 Farm.xlsx