Table of ContentsSheet TabDescriptionAuthor1Table of ContentsThis PageWinston Churchill2Basic ExerciseExercise with Titles and InstructionsCharles DeGaul3InstructionsSummary of Key Points in ExercisePabblo Picasso4Exercise with MinimumInclude Minimum Cash BalanceMichael Jackson5Exercise without TitlesExercise with Only InputsNelson MandellaFile Name:C:\A Files\Courses\Course Materials\1 Templates and Exercises Corporate Model\Exercises\Exercise 2 - Simple Corporate Model.xlsColour CodesDate11-Mar-09Note: Table of Contents Created with fm.xls File

Table of ContentsBasic ExerciseInstructionsExercise with MinimumExercise without Titles

Basic ExerciseTable of ContentsVerificationYear01234566AssumptionsOperatingEBITDA1200300400450450450450Base Case200300400450450450450Low Case150200300300300300300High Case200350500650650650650Depreciation Rate10%10%10%10%10%10%10%Capital Expenditures40040020050505050FinancingInterest Income Rate3.00%3.50%4.00%4.00%4.00%4.00%4.00%Interest Expense Rate5.00%5.50%6.00%6.00%6.00%6.00%6.00%Dividend Payout Ratio50%50%50%50%50%50%50%Long-Term DebtScheduled Repayment6060606060Interest Rate on Long-term Debt6.50%6.50%6.50%6.50%6.50%Initial Balance SheetCash0.0Plant Property and Equipment1,300.00Accumulated Depreciation300.00Total Assets1,000.00Long-Term Debt300.00Short-Term Debt100.00Equity600.00Total Liabilities and Capital1,000.00Fixed AssetsPlant BalanceOpening BalanceOpening = Prior Closing1,300.001,700.002,100.002,300.002,350.002,400.002,450.00Add: Capital ExpenditureFrom Input400.00400.00200.0050.0050.0050.0050.00Closing BalanceInitial from B/S; Later Sum1,300.001,700.002,100.002,300.002,350.002,400.002,450.002,500.00Depreciation RateFrom Input10%10%10%10%10%10%10%Depreciation Expense (Ending Balance)Closing x Depreciation Rate130.00170.00210.00230.00235.00240.00245.00Accumulated DepreciationOpening Balance300.00430.00600.00810.001,040.001,275.001,515.00Add: Depreciation Expense130.00170.00210.00230.00235.00240.00245.00Closing Balance300.00430.00600.00810.001,040.001,275.001,515.001,760.00Debt AnalysisDebt BalanceOpening BalanceOpening = Prior Closing300.00240.00180.00120.0060.000.000.00Less: RepaymentsFrom Input60.0060.0060.0060.0060.000.000.00Closing BalanceInitial from B/S; Later Sum300.00240.00180.00120.0060.000.00.00.0Interest Rate on Long-term DebtFrom Input6.50%6.50%6.50%6.50%6.50%0.00%0.00%Long-term Interest ExpenseOpening x Rate19.5015.6011.707.803.900.000.00Cash LESS Short-term Debt BalanceOpening BalancePrior Balance(100.00)(407.25)(651.25)(631.64)(424.49)(206.67)82.13Add: Net Cash FlowFrom Cash Flow Statement(307.25)(244.00)19.61207.15217.82288.80299.14Closing BalanceOpeining + Cash Flow(100.00)(407.25)(651.25)(631.64)(424.49)(206.67)82.13381.27Cash BalanceMax of Closing Balance or Zero0.00.00.00.00.00.082.13381.27Interest Income Rate3.00%3.50%4.00%4.00%4.00%4.00%4.00%Interest IncomeAverage of Cash x Rate0.00.00.00.00.00.03.29Short-term Debt BalanceMax of Neg Closing Balance or Zero100.00407.25651.25631.64424.49206.670.00.0Interest Expense Rate5.00%5.50%6.00%6.00%6.00%6.00%6.00%Short-term Interest ExpenseAverage of Debt x Rate5.0022.4039.0737.9025.4712.400.0Income StatementEBITDAFrom Above, Sensitivity200.00300.00400.00450.00450.00450.00450.00Less: DepreciationDepreciation Analysis130.00170.00210.00230.00235.00240.00245.00EBITEBITDA - Depreciation70.00130.00190.00220.00215.00210.00205.00Less: Short-term InterestEnd of Model, Leave until Done5.0022.4039.0737.9025.4712.400.00Less: Long-term InterestDebt Analysis Above19.5015.6011.707.803.900.000.00Plus: Interest IncomeEnd of Model, Leave until Done0.000.000.000.000.000.003.29Net IncomeEBIT - Interest + Interest Income45.5092.00139.23174.30185.63197.60208.29Cash Flow StatementNet IncomeFrom Income Statement45.5092.00139.23174.30185.63197.60208.29Add: Depreciation ExpenseFrom Income Statement130.00170.00210.00230.00235.00240.00245.00Cash from OperationsNet Income + Depreciation175.50262.00349.23404.30420.63437.60453.29Less: Capital ExpendituresFrom Above400.00400.00200.0050.0050.0050.0050.00Cash Before FinancingOperations - Capital Expenditures(224.50)(138.00)149.23354.30370.63387.60403.29Less: Debt RepaymentFrom Above60.0060.0060.0060.0060.000.000.00Less: DividendsDividend Payout x Net Income22.7546.0069.6187.1592.8298.80104.14Net Cash FlowCash Flow b/4 fin - Repayment - Div(307.25)(244.00)19.61207.15217.82288.80299.14Balance Sheet and Common EquityCommon Equity BalanceOpenining Balance600.00622.75668.75738.36825.51918.331,017.13Add: Net Income45.5092.00139.23174.30185.63197.60208.29Less: Dividends22.7546.0069.6187.1592.8298.80104.14Closing Balance600.00622.75668.75738.36825.51918.331,017.131,121.27AssetsCashFrom End of Model, Leave until Done0.000.000.000.000.000.0082.13381.27Plant Property and EquipmentFrom Plant Balance1,300.001,700.002,100.002,300.002,350.002,400.002,450.002,500.00Less: Accumulated DepreciationPrior Balance + Depreciation(300.00)(430.00)(600.00)(810.00)(1,040.00)(1,275.00)(1,515.00)(1,760.00)Total AssetsCash + Plant - Acc Dep1,000.001,270.001,500.001,490.001,310.001,125.001,017.131,121.27Liabilites and EquityLong-Term DebtFrom Debt Balance Above300.00240.00180.00120.0060.000.000.000.00Short-Term DebtFrom End of Model, Leave until Done100.00407.25651.25631.64424.49206.670.000.00EquityFrom Above600.00622.75668.75738.36825.51918.331,017.131,121.27Total Liabilities and CapitalSum of Above1,000.001,270.001,500.001,490.001,310.001,125.001,017.131,121.27Balance Sheet DifferenceAssets = Capital0.00.00.00.00.00.00.00.0Balance Sheet Test0.00.00.00.00.00.00.00.0Aggregate Test0.0

Show CommentsElvis Presley:

OBJECTIVE

To make sure that you are comfortable in linking the various financial statements and to assure that you understand how the short-term debt and surplus cash work in a financial model.

We also use the model to demonstrate circularity and managing the capital structure with the solver.

Procedure:

Work through all of the accounts. The accounts in light green relate to the short-term debt and surplus cash and are reconciled at the end of the sheet.

Selected Features: - The limits are created by simply hiding the columns - The scenarios are developed with a combo box and the rows are hidden with the ALT-Right Arrow - The input colours are created with a macro from the fm.xls file, - The macros are created simply by turning on the macro record button and working through the example.

Note: Do not insert rows if you want the macro to remain valid.Elvis Presley:

There are multiple EBITDA scenarios. Use the + key to see the scenarios.

Scenario selection is computed with:1. Combo Box that links to the names of the scenarios2. Index command that works with the number from the combo boxElvis Presley: This represents a debt schedule; in actual models, each debt issue would have a separate inputRoger Fedrer: This is a very simple working analysis; a real model would include detailed revenue and expense projectionsRoger Fedrer: Begin from the balance sheetRoger Fedrer: Begin from the balance sheetRoger Fedrer: Begin from the balance sheetElvis Presley:

This is the section where you compute the amounts related to short-term debt and surplus cash that were not entered above.

Every model has some kind of plug figure this section describes how you can enter the data.

Begin by constructing a cork-screw that groups the total amount of short-term debt plus the temporary securities.Elvis Presley: The initial balance sheet should from historyElvis Presley: The initial balance sheet should from historyInstructionsClearCompleteTable of Contents

InstructionsTable of ContentsInstructionsThis model contains fundamental calculations for creating a financial model. Some of the key concepts include:- Understand that the starting point for the model is the initial balance sheet- Separate the model into modules with Separate parts for fixed assets and for debt- The initial closing balance (in boxes) comes from the intial balance sheet- Use accounts with opening and closing balance- For the exercise, apply the positive number convention, meaning all of the numbers are positive- Make the depreciation expense and the interest on the opening balance, meaning all flows occur at the end of the year- Accounts in financial statements come from earlier modules or are sub-totals- The only exception is to use the MAX(net income x payout ratio,0) for dividends- Put the cash and short-term debt into a bucket and then split between debt and cash- Split the bucket into cash or investments using the MAX function -- MAX(bucket,0) for cash- Split the bucket into cash or investments using the MAX function -- MAX(-bucket,0) for debt- Create a common equtiy balance account- Link all of the balance sheet accouts- Test the balance sheetOther Notes:- Set up the sheet with SHIFT, CNTL ---> and SHIFT,ALT,----->- Use the combo box and the index command for scenarios

BackTable of Contents

Exercise without TitlesTable of ContentsYear0123456AssumptionsOperatingEBITDA1200300400450450450Base Case200300400450450450Low Case150200300300300300High Case200350500650650650Depreciation Rate10%10%10%10%10%10%Capital Expenditures400400200505050FinancingInterest Income Rate3.00%3.50%4.00%4.00%4.00%4.00%Interest Expense Rate5.00%5.50%6.00%6.00%6.00%6.00%Minimum Cash Balance100100100100100100Dividend Payout Ratio50%50%50%50%50%50%Long-Term DebtRepayment6060606060Interest Rate on Long-term Debt6.50%6.50%6.50%6.50%6.50%Initial Balance SheetCash100.00Plant Property and Equipment1,300.00Accumulated Depreciation300.00Total Assets1,100.00Long-Term Debt300.00Short-Term Debt200.00Equity600.00Total Liabilities and Capital1,100.00

Table of Contents

Exercise with MinimumTable of ContentsVerificationYear01234566AssumptionsOperatingEBITDA1200300400450450450450Base Case200300400450450450450Low Case150200300300300300300High Case200350500650650650650Depreciation Rate10%10%10%10%10%10%10%Capital Expenditures40040020050505050Minimum Cash as Percent of EBITDA2%2%2%2%2%2%2%FinancingInterest Income Rate3.00%3.50%4.00%4.00%4.00%4.00%4.00%Interest Expense Rate5.00%5.50%6.00%6.00%6.00%6.00%6.00%Dividend Payout Ratio50%50%50%50%50%50%50%Long-Term DebtScheduled Repayment6060606060Interest Rate on Long-term Debt6.50%6.50%6.50%6.50%6.50%Initial Balance SheetCash0.0Plant Property and Equipment1,300.00Accumulated Depreciation300.00Total Assets1,000.00Long-Term Debt300.00Short-Term Debt100.00Equity600.00Total Liabilities and Capital1,000.00Fixed AssetsPlant BalanceOpening BalanceOpening = Prior Closing1,300.001,700.002,100.002,300.002,350.002,400.002,450.00Add: Capital ExpenditureFrom Input400.00400.00200.0050.0050.0050.0050.00Closing BalanceInitial from B/S; Later Sum1,300.001,700.002,100.002,300.002,350.002,400.002,450.002,500.00Depreciation RateFrom Input10%10%10%10%10%10%10%Depreciation Expense (Ending Balance)Closing x Depreciation Rate130.00170.00210.00230.00235.00240.00245.00Accumulated DepreciationOpening Balance300.00430.00600.00810.001,040.001,275.001,515.00Add: Depreciation Expense130.00170.00210.00230.00235.00240.00245.00Closing Balance300.00430.00600.00810.001,040.001,275.001,515.001,760.00Debt AnalysisDebt BalanceOpening BalanceOpening = Prior Closing300.00240.00180.00120.0060.000.000.00Less: RepaymentsFrom Input-60.00-60.00-60.00-60.00-60.000.000.00Closing BalanceInitial from B/S; Later Sum300.00240.00180.00120.0060.000.00.00.0Interest Rate on Long-term DebtFrom Input6.50%6.50%6.50%6.50%6.50%0.00%0.00%Long-term Interest ExpenseOpening x Rate19.5015.6011.707.803.900.000.00Cash LESS Short-term Debt BalanceOpening BalancePrior Balance(100.00)(407.25)(651.29)(631.74)(424.67)(206.95)81.75Add: Net Cash FlowFrom Cash Flow Statement(307.25)(244.04)19.55207.07217.72288.70299.14Closing BalanceOpeining + Cash Flow(100.00)(407.25)(651.29)(631.74)(424.67)(206.95)81.75380.89Minimum Cash BalanceEBITDA200.00300.00400.00450.00450.00450.00450.00Cash Balance Percent0.020.020.020.020.020.020.02Cash Balance Amount4.006.008.009.009.009.009.00Cash BalanceMax of Closing Balance or Zero0.04.006.008.009.009.0081.75380.89Interest Income Rate3.00%3.50%4.00%4.00%4.00%4.00%4.00%Interest IncomeAverage of Cash x Rate0.00.140.240.320.360.363.27Short-term Debt BalanceMax of Neg Closing Balance or Zero100.00411.25657.29639.74433.67215.950.00.0Interest Expense Rate5.00%5.50%6.00%6.00%6.00%6.00%6.00%Short-term Interest ExpenseAverage of Debt x Rate5.0022.6239.4438.3826.0212.960.0Income StatementEBITDAFrom Above, Sensitivity200.00300.00400.00450.00450.00450.00450.00Less: DepreciationDepreciation Analysis130.00170.00210.00230.00235.00240.00245.00EBITEBITDA - Depreciation70.00130.00190.00220.00215.00210.00205.00Less: Short-term InterestEnd of Model, Leave until Done5.0022.6239.4438.3826.0212.960.00Less: Long-term InterestDebt Analysis Above19.5015.6011.707.803.900.000.00Plus: Interest IncomeEnd of Model, Leave until Done0.000.140.240.320.360.363.27Net IncomeEBIT - Interest + Interest Income45.5091.92139.10174.14185.44197.40208.27Cash Flow StatementNet IncomeFrom Income Statement45.5091.92139.10174.14185.44197.40208.27Add: Depreciation ExpenseFrom Income Statement130.00170.00210.00230.00235.00240.00245.00Cash from OperationsNet Income + Depreciation175.50261.92349.10404.14420.44437.40453.27Less: Capital ExpendituresFrom Above400.00400.00200.0050.0050.0050.0050.00Cash Before FinancingOperations - Capital Expenditures(224.50)(138.08)149.10354.14370.44387.40403.27Less: Debt RepaymentFrom Above(60.00)(60.00)(60.00)(60.00)(60.00)0.000.00Less: DividendsDividend Payout x Net Income(22.75)(45.96)(69.55)(87.07)(92.72)(98.70)(104.14)Net Cash FlowCash Flow b/4 fin - Repayment - Div(307.25)(244.04)19.55207.07217.72288.70299.14Balance Sheet and Common EquityCommon Equity BalanceOpenining Balance600.00622.75668.71738.26825.33918.051,016.75Add: Net Income45.5091.92139.10174.14185.44197.40208.27Less: Dividends(22.75)(45.96)(69.55)(87.07)(92.72)(98.70)(104.14)Closing Balance600.00622.75668.71738.26825.33918.051,016.751,120.89AssetsCashFrom End of Model, Leave until Done0.004.006.008.009.009.0081.75380.89Plant Property and EquipmentFrom Plant Balance1,300.001,700.002,100.002,300.002,350.002,400.002,450.002,500.00Less: Accumulated DepreciationPrior Balance + Depreciation(300.00)(430.00)(600.00)(810.00)(1,040.00)(1,275.00)(1,515.00)(1,760.00)Total AssetsCash + Plant - Acc Dep1,000.001,274.001,506.001,498.001,319.001,134.001,016.751,120.89Liabilites and EquityLong-Term DebtFrom Debt Balance Above300.00240.00180.00120.0060.000.000.000.00Short-Term DebtFrom End of Model, Leave until Done100.00411.25657.29639.74433.67215.950.000.00EquityFrom Above600.00622.75668.71738.26825.33918.051,016.751,120.89Total Liabilities and CapitalSum of Above1,000.001,274.001,506.001,498.001,319.001,134.001,016.751,120.89Balance Sheet DifferenceAssets = Capital0.00.00.00.00.00.00.00.0Balance Sheet Test0.00.00.00.00.00.00.00.0Aggregate Test0.0

Show CommentsClearCompleteElvis Presley:

OBJECTIVE

To make sure that you are comfortable in linking the various financial statements and to assure that you understand how the short-term debt and surplus cash work in a financial model.

We also use the model to demonstrate circularity and managing the capital structure with the solver.

Procedure:

Work through all of the accounts. The accounts in light green relate to the short-term debt and surplus cash and are reconciled at the end of the sheet.

Selected Features: - The limits are created by simply hiding the columns - The scenarios are developed with a combo box and the rows are hidden with the ALT-Right Arrow - The input colours are created with a macro from the fm.xls file, - The macros are created simply by turning on the macro record button and working through the example.

Note: Do not insert rows if you want the macro to remain valid.Elvis Presley:

There are multiple EBITDA scenarios. Use the + key to see the scenarios.

Scenario selection is computed with:1. Combo Box that links to the names of the scenarios2. Index command that works with the number from the combo boxElvis Presley: This represents a debt schedule; in actual models, each debt issue would have a separate inputRoger Fedrer: This is a very simple working analysis; a real model would include detailed revenue and expense projectionsRoger Fedrer: Begin from the balance sheetRoger Fedrer: Begin from the balance sheetRoger Fedrer: Begin from the balance sheetElvis Presley:

This is the section where you compute the amounts related to short-term debt and surplus cash that were not entered above.

Every model has some kind of plug figure this section describes how you can enter the data.

Begin by constructing a cork-screw that groups the total amount of short-term debt plus the temporary securities.Elvis Presley: The initial balance sheet should from historyElvis Presley: The initial balance sheet should from historyTable of Contents