{ Fundamentals of Financial Modeling
  Master model

  Version 1.0 - First attempt at combining the P&L and Balance Sheet

}
'Column Control' = 'Last History Column' For c1
'Column Control' = 'Last History Column' COL 1 + 1.0 for c2  { This is the first projected column }
'Column Control' = LastColumn() for c3  { Last column in the matrix }
'Last History Column'

!Define For_History_Columns For c1 - Ind1
!Define For_Projected_Columns For Ind2 - Ind3

{*********************** Revenue Submodel *******************************************************}
'Total Sales' = 'Sales - Cars' SUM 'Sales - Airplanes' For_History_Columns
'Sales Forecast Override'
'Total Sales' = ForecastRegression ('Total Sales', 1, 'Last History Column' COL 1) For_Projected_Columns

{ Here is the override calculation:  }

ByColumn For_Projected_Columns
  If 'Sales Forecast Override' <> 0.0 THEN
    Begin
      'Total Sales' = 'Sales Forecast Override'
    END
EndByColumn

{ Enter a projection for "Total Sales", and then apply a mix percentage to allocate sales to product lines }

'Sales - Cars' = 'Sales Mix % - Cars' * 'Total Sales' / 100.0 For_Projected_Columns
'Sales - Trucks'= 'Sales Mix % - Trucks' * 'Total Sales' / 100.0 For_Projected_Columns
'Sales - Airplanes'= 'Sales Mix % - Airplanes' * 'Total Sales' / 100.0 For_Projected_Columns

 
 
{ Calculate the "Mix %" for historical time periods }

'Sales Mix % - Cars' = 'Sales - Cars' % 'Total Sales' For_History_Columns
'Sales Mix % - Trucks' = 'Sales - Trucks' % 'Total Sales' For_History_Columns
'Sales Mix % - Airplanes' = 'Sales - Airplanes' % 'Total Sales' For_History_Columns
'100% Mix check' = 'Sales Mix % - Cars' SUM 'Sales Mix % - Airplanes'

 
{ Calculate cost of sales as a percentage of sales for historical time periods: }
'COS % - Cars' = 'COS - Cars' % 'Sales - Cars' For_History_Columns
'COS % - Trucks' = 'COS - Trucks' % 'Sales - Trucks' For_History_Columns
'COS % - Airplanes' = 'COS - Airplanes' % 'Sales - Airplanes' For_History_Columns

{ Calculate cost of sales for the projected years only: }
'COS - Cars' = 'Sales - Cars' * 'COS % - Cars' / 100.0  For_Projected_Columns
'COS - Trucks' = 'Sales - Trucks' * 'COS % - Trucks' / 100.0 For_Projected_Columns
'COS - Airplanes' = 'Sales - Airplanes' * 'COS % - Airplanes' / 100.0 For_Projected_Columns

'Total Cost of Sales' = 'COS - Cars' SUM 'COS - Airplanes'
'Gross Profit' = 'Total Sales' - 'Total Cost of Sales'

'Overall COS %' = 'Total Cost of Sales' % 'Total Sales'
'Gross Margin %' = 'Gross Profit' % 'Total Sales'

{*******************  The Expense Sub Model ******************************}

'Headcount' { Input for all time periods }
'Avg Salary per Person' = 'Salaries & Wages' / 'Headcount' For_History_Columns
'Benefits as % of Salaries' = 'Employee Benefits' % 'Salaries & Wages' For_History_Columns

{ Expense assumptions: }

'Salaries & Wages' = 'Avg Salary per Person' * 'Headcount' For_Projected_Columns
'Employee Benefits' = 'Benefits as % of Salaries' * 'Salaries & Wages' / 100.0 For_Projected_Columns
'Occupancy'
'Telephone'
'Advertising & Promotion'
'Misc'

'Total Operating Expenses' = 'Salaries & Wages' SUM 'Misc'
'Operating Income' = 'Gross Profit' - 'Total Operating Expenses'
'Operating Margin %' = 'Operating Income' % 'Total Sales'

{********************** Calculation of Net Income *************************************}
ByColumn  // Need to start calculating column by column
          // This means that we calculate everything in 1998 before going
          // on to 1999.  We need to do this  because 1999's interest income
          // and expense depend on balances at the end of 1998.
'Int Rate (%) - Short Term Investment'
'Int Rate (%) - Short Term Debt'

'Interest Income' = 'Int Rate (%) - Short Term Investment' * ('Short Term Investment' LAG 1) / 100.0 For_Projected_Columns
'Interest Expense' = 'Int Rate (%) - Short Term Debt' * ('Short Term Debt' LAG 1) / 100.0 For_Projected_Columns

'Pretax Income' = 'Operating Income' + 'Interest Income' - 'Interest Expense'

'Loss Carry-Forward'='Pretax Income' Losscf 15.0
'Taxable Income'=0.0

If 'Pretax Income' GT 0.0 THEN
     'Taxable Income'='Pretax Income' - 'Loss Carry-Forward'

'Tax Rate (%)'
'Income Taxes' = 'Taxable Income' * 'Tax Rate (%)' /100.0 For_Projected_Columns
'Net Income After Tax' = 'Pretax Income' - 'Income Taxes'
'ROS' = 'Net Income After Tax' % 'Total Sales'

 
{**************************   Balance Sheet **************************************}
'Non wage expenses' = 'Total Operating Expenses' - 'Salaries & Wages' + &
    'Total Cost of Sales'

'Days per Year'
'Days per Column'
'Annualized Sales' = 'Total Sales' * 'Days per Year' / 'Days per Column'
'Annualized Cost of Sales' = 'Total Cost of Sales' * 'Days per Year' / 'Days per Column'
'Annualized Non Wage Expenses' = 'Non wage expenses' * 'Days per Year' / 'Days per Column'

'Days of Receivables' = 'Accounts Receivable' / 'Annualized Sales' * 'Days per Year' For_History_Columns
'Days of Payables' = 'Accounts Payable' / 'Annualized Non Wage Expenses' * 'Days per Year' For_History_Columns
'Inventory Turns' = 'Annualized Cost of Sales' / 'Inventory' For_History_Columns

{ 1. Assets: }
'Cash'
'Accounts Receivable' = 'Days of Receivables' * 'Annualized Sales' / 'Days per Year' For_Projected_Columns
'Inventory' = 'Annualized Cost of Sales' / 'Inventory Turns' For_Projected_Columns
'Short Term Investment'
'Other Current assets'
'Total Current Assets' = 'Cash' SUM 'Other Current Assets'

 
'Fixed Assets - Additions'
'Fixed Assets - Retirements'

'Depr on Existing Fixed Assets'
'Depr Life - New assets'
'Depr Method - New Assets'
'Depr Params' = 'Depr Life - New assets' For c1
'Depr Params' = 'Depr Method - New Assets' Col 1 for c2
'Depr on New Fixed Assets' = 'Fixed Assets - Additions' DEPR 'Depr Params' For_Projected_Columns
'Depreciation' = 'Depr on Existing Fixed Assets' + 'Depr on New Fixed Assets'

'Fixed Assets - Gross' = 'Fixed Assets - Gross' Lag 1 + 'Fixed Assets - Additions' - &
   'Fixed Assets - Retirements'  For_Projected_Columns
'Accumulated Depreciation' = 'Accumulated Depreciation' Lag 1 + 'Depreciation' For_Projected_Columns
'Fixed Assets - Net' = 'Fixed Assets - Gross' - 'Accumulated Depreciation'
'Other Assets'
{ *** Version 4.0 - first calculate Total Assets excluding Short Term Investment.. }
'Total Assets excl. ST Invest' = 'Total Current Assets' + 'Fixed Assets - Net' + &
    'Other Assets' - 'Short Term Investment'

'Total Assets' = 'Total Current Assets' + 'Fixed Assets - Net' + 'Other Assets'

 
{ 2. Liabilities & Equity }

'Short Term Debt'
'Accounts Payable' = 'Days of Payables' * 'Annualized Non Wage Expenses'  / 'Days per Year' For_Projected_Columns
'Current Portion LT Debt'
'Other Current Liabilities'
'Total Current Liabilities' = 'Short Term Debt' SUM 'Other Current Liabilities'

 
'LT Debt - Principal Repayments'
'LT Debt - New'
'Long Term Debt' = 'Long Term Debt' Lag 1 + 'LT Debt - New' - 'LT Debt - Principal Repayments' For_Projected_Columns
'Other Liabilities'
'Total Liabilities' = 'Total Current Liabilities' + 'Long Term Debt' + &
   'Other Liabilities'

'Net Income After Tax'
'Common Stock'
'Retained Earnings' = 'Retained Earnings' LAG 1 + 'Net Income After Tax' For_Projected_Columns
'Total Equity' = 'Common Stock' + 'Retained Earnings'
'Total Liab & Equity' = 'Total Liabilities' + 'Total Equity'

{ Calculate Total Liab & Equity excluding Short Term Debt }
'Total Liab & Equity Excl ST Debt' = 'Total Liabilities' + 'Total Equity' - 'Short Term Debt'

{ Compute Cash Excess / (Shortfall).. }
'Cash Excess/(Shortfall)' = 'Total Liab & Equity Excl ST Debt' - &
                            'Total Assets excl. ST Invest' For_Projected_Columns

{ Put Excess/(Shortfall) into Short Term Investmen or Short Term Debt depending on its sign..}
  If 'Cash Excess/(Shortfall)' > 0.0 THEN
    BEGIN
      'Short Term Investment' = 'Cash Excess/(Shortfall)' For_Projected_Columns
      'Short Term Debt' = 0.0 For_Projected_Columns
    END
  ELSE
    BEGIN
      'Short Term Investment' = 0.0 For_Projected_Columns
      'Short Term Debt' = 0.0 - 'Cash Excess/(Shortfall)' For_Projected_Columns
    END
{ Now recompute any Totals and SubTotals dependent on Short Term Debt and
    Short Term Investment..}
'Total Current Assets' = 'Cash' SUM 'Other Current Assets'
'Total Assets' = 'Total Current Assets' + 'Fixed Assets - Net' + 'Other Assets'
'Total Current Liabilities' = 'Short Term Debt' SUM 'Other Current Liabilities'
'Total Liabilities' = 'Total Current Liabilities' + 'Long Term Debt' + &
   'Other Liabilities'
'Total Liab & Equity' = 'Total Liabilities' + 'Total Equity'
EndByColumn

'Balance Check' = 'Total Assets' - 'Total Liab & Equity'