1 / 15

Lecture 15-2 Building Financial Models

Lecture 15-2 Building Financial Models. Material for Lecture 15-2 Read Chapters 13 and 14 Lecture 15-2 Farm.xlsx. Step 1: KOVs. What question is to be answered? Net cash income? Cash flow? Change in net worth? Number of years to payoff ? Something else as: NPV, B/C, IRR, EBIT

odell
Download Presentation

Lecture 15-2 Building Financial Models

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related