{ Fundamentals of Financial Modeling
  Balance Sheet SubModel

  Version 3.0 - Improve calculations for the following:
                 1. Fixed assets
                 2. Depreciation & accumulated depreciation
                 3. Long term debt
                 4. Retained Earnings
}
'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 }

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

'Last History Column'

'Total Sales'
'Total Operating Expenses'
'Salaries & Wages'
'Total Cost of Sales'
'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'

{ *** Version 3.0 - allow for Fixed Asset additions / retirements }
'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'
{ *** }

{*** Version 3.0 - now calculate Fixed Assets - Gross, and Accumulated Depreciation..}
'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'
'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'

{ *** Version 3.0 - Separate out principal repayments and new debt issued..}
'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'
{ *** Version 3.0 - Calculate ending retained earnings based on Net Income..}
'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'

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