Lecture 15-2 Building Financial Models Material
Description: 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?
Related Topics
Download Presentation
"Lecture 15-2 Building Financial Models Material" is the property of its rightful owner. Permission is granted to download and print the materials on this website for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Presentation Transcript
slide1. Lecture 15-2 Building Financial Models Material for Lecture 15-2
Read Chapters 13 and 14
Lecture 15-2 Farm.xlsx<br>
slide2. 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
Scenarios to analyze
Financing, technology, machinery replacement
Marketing strategies, etc.
Risk ranking<br>
slide3. 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<br>
slide4. 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<br>
slide5. 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<br>
slide6. 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<br>
slide7. 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<br>
slide8. 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<br>
slide9. 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<br>
slide10. 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.<br>
slide11. 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<br>
slide12. 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<br>
slide13. 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<br>
slide14. 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<br>
slide15. Demonstrate Model Development Lecture 15-2 Farm.xlsx<br>
Read Chapters 13 and 14
Lecture 15-2 Farm.xlsx<br>
slide2. 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
Scenarios to analyze
Financing, technology, machinery replacement
Marketing strategies, etc.
Risk ranking<br>
slide3. 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<br>
slide4. 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<br>
slide5. 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<br>
slide6. 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<br>
slide7. 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<br>
slide8. 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<br>
slide9. 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<br>
slide10. 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.<br>
slide11. 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<br>
slide12. 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<br>
slide13. 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<br>
slide14. 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<br>
slide15. Demonstrate Model Development Lecture 15-2 Farm.xlsx<br>