<FINANCIAL MODELING INSTRUCTIONS>
Please go to "Tools", "Options", "Calculation". In the "Calculation" tab, please set it to "manual" calculation and "iteration" of 500.
Assumptions
Income Statement
Revenue - Assume 5% growth rate in each year of the forecast period.
COGS as % of Sales - Assume 40% for each year of the forecast period.
Depreciation % as of Gross P,P&E - Assume 2% for each year of the forecast period. NOTE that Gross PP&E will
initially be $0, but Depreciation will automatically calculate once Gross PP&E is filled in later.
Amortization - No Amortization (enter $0 for each year).
SG&A as % of Sales - Assume 30% for each year of the forecast period.
Other Income (Expenses) - Assume $0 million for each year of the forecast period.
Tax Rate - Assume 40% for each year of the forecast period.
Balance Sheet
Days Accounts Receivable - Assume 30 days for each year of the forecast period.
Days Inventory - Assume 45 days for each year of the forecast period.
Other Current Assets - Assume $1.0 million for each year of the forecast period.
Amortization of Goodwill - Assume $0 million for each year of the forecast period.
Capex as % of Sales - Assume 5% for each year of the forecast period.
Asset Disposition - Assume $0 for each year of the forecast period.
Days Payable - Assume 50 Days for each year of the forecast period.
Accrued Liabilities as % of COGS - Assume 3% for each year of the forecast period.
Other Current Liabilities as a % of COGS - Assume 2% for each year of the forecast period.
Other Liabilities - Assume $2 million for each year of the forecast period.
Common Stock - Assume $10 million for each year of the forecast period.
LIBOR % (London InterBank Offered Rate) - It will increase 25 basis points (0.25%) every year after 2008.
Interest Earned on Cash - It will increase 25 basis points every year after 2008.
Revolver Interest Rate - Assume Libor + 2.0%
Term Loan Interest Rate - Assume Libor + 2.5%
Unsecured Debt Interest Rate - Assume 12.0% Interest Rate
Term Loan Amortization - Assume $20 million principal paydown (amortization) per year during the forecast period.
Unsecured Debt Amortization - Assume no paydown in principal during any year of the forecast period.
Page 1
, <FINANCIAL MODELING INSTRUCTIONS>
Integrated Financial Projections (Follow the Assumptions Page to Determine How to Calculate These Figures)
Income Statement
Calculate the relevant data and margins for each line item, using the relevant assumptions from the Assumptions page.
Link the Interest Expense and Interest Income line items to the corresponding line items at the bottom of the debt schedule.
NOTE: Until the entire model is complete, the values in these cells will be incorrect, but the links will be correct (we need to
calculate the debt amounts and create a "circular" model).
Balance Sheet
Link Cash to the 'Ending Cash' line item at the bottom of the Cash Flow Statement. As with the Interest Expense and Interest Income line items,
the value in the Cash cell will be incorrect until the model is complete, but the link will be correct.
For AR, Inventory, and AP, you'll need to use the respective turnover days assumptions and formulas to back in to each figure.
For example, the formula for AR days = AR/Sales * 360, If you have values only for sales and AR days, you can use algebra to calculate what AR
would be [AR = (AR Days * Sales)/360].
For Other Current Assets, Other Current Liabilities, and Other Liabilities, simply link back to the corresponding assumptions on the Assumptions page.
Gross PP&E is calculated by adding this year's Capex (from the Cash Flow Statement) to the prior year's Gross PP&E figure, and deducting asset dispositions.
As with Interest Expense, Interest Income, and Cash, the value in the Gross PP&E cell will be incorrect until capex is calculated in the CF statement,
but the link will be correct.
Accumulated Depreciation is calculated by adding the current year's depreciation expense to the prior year's Accumulated Depreciation figure.
Goodwill will remain unchanged throughout the forecast period (there will be no deductions or additions to goodwill in any year), per the Assumptions page.
Link all the debt balances to the respecting ending balances on the Debt and Interest Schedule (e.g. for Revolving Credit Facility, you'll link to the 'Ending Revolver
Balance' line in the Debt Schedule). These cell values will be incorrect until the Debt Schedule has been completed, but the links will be correct.
Retained Earnings is calculated by adding this year's Net Income to the prior year's Retained Earnings ending balance, and subtracting any dividends (none in this model).
Common Stock will remain unchanged throughout the forecast period.
Cash Flow
Net Income and Depreciation Expense should be sourced from the Income Statement.
Working Capital - Remember that year-over-year increases in asset accounts (i.e. AR, Inventory) are represented as uses (i.e. negative values) of cash on the cash flow
statement, and vice versa for liabilities.
Capex and Asset Dispositions calculated using the appropriate assumption per the Assumptions page.
For changes in items listed in the Financing section (Revolver, Term Loan, etc.), link to changes in these accounts on the Balance Sheet.
Change in Cash is equal to the sum of Cash from Operations, Cash from Investing, and Cash from Financing.
Beginning Cash Position is equal to the prior year's Ending Cash Position.
Change in Cash Position is equal to the Total Cash Flow line, above.
Debt and Interest Schedule
Revolver
Beginning Revolver Balance is equal to the prior year's Ending Revolver Balance.
Page 2
Please go to "Tools", "Options", "Calculation". In the "Calculation" tab, please set it to "manual" calculation and "iteration" of 500.
Assumptions
Income Statement
Revenue - Assume 5% growth rate in each year of the forecast period.
COGS as % of Sales - Assume 40% for each year of the forecast period.
Depreciation % as of Gross P,P&E - Assume 2% for each year of the forecast period. NOTE that Gross PP&E will
initially be $0, but Depreciation will automatically calculate once Gross PP&E is filled in later.
Amortization - No Amortization (enter $0 for each year).
SG&A as % of Sales - Assume 30% for each year of the forecast period.
Other Income (Expenses) - Assume $0 million for each year of the forecast period.
Tax Rate - Assume 40% for each year of the forecast period.
Balance Sheet
Days Accounts Receivable - Assume 30 days for each year of the forecast period.
Days Inventory - Assume 45 days for each year of the forecast period.
Other Current Assets - Assume $1.0 million for each year of the forecast period.
Amortization of Goodwill - Assume $0 million for each year of the forecast period.
Capex as % of Sales - Assume 5% for each year of the forecast period.
Asset Disposition - Assume $0 for each year of the forecast period.
Days Payable - Assume 50 Days for each year of the forecast period.
Accrued Liabilities as % of COGS - Assume 3% for each year of the forecast period.
Other Current Liabilities as a % of COGS - Assume 2% for each year of the forecast period.
Other Liabilities - Assume $2 million for each year of the forecast period.
Common Stock - Assume $10 million for each year of the forecast period.
LIBOR % (London InterBank Offered Rate) - It will increase 25 basis points (0.25%) every year after 2008.
Interest Earned on Cash - It will increase 25 basis points every year after 2008.
Revolver Interest Rate - Assume Libor + 2.0%
Term Loan Interest Rate - Assume Libor + 2.5%
Unsecured Debt Interest Rate - Assume 12.0% Interest Rate
Term Loan Amortization - Assume $20 million principal paydown (amortization) per year during the forecast period.
Unsecured Debt Amortization - Assume no paydown in principal during any year of the forecast period.
Page 1
, <FINANCIAL MODELING INSTRUCTIONS>
Integrated Financial Projections (Follow the Assumptions Page to Determine How to Calculate These Figures)
Income Statement
Calculate the relevant data and margins for each line item, using the relevant assumptions from the Assumptions page.
Link the Interest Expense and Interest Income line items to the corresponding line items at the bottom of the debt schedule.
NOTE: Until the entire model is complete, the values in these cells will be incorrect, but the links will be correct (we need to
calculate the debt amounts and create a "circular" model).
Balance Sheet
Link Cash to the 'Ending Cash' line item at the bottom of the Cash Flow Statement. As with the Interest Expense and Interest Income line items,
the value in the Cash cell will be incorrect until the model is complete, but the link will be correct.
For AR, Inventory, and AP, you'll need to use the respective turnover days assumptions and formulas to back in to each figure.
For example, the formula for AR days = AR/Sales * 360, If you have values only for sales and AR days, you can use algebra to calculate what AR
would be [AR = (AR Days * Sales)/360].
For Other Current Assets, Other Current Liabilities, and Other Liabilities, simply link back to the corresponding assumptions on the Assumptions page.
Gross PP&E is calculated by adding this year's Capex (from the Cash Flow Statement) to the prior year's Gross PP&E figure, and deducting asset dispositions.
As with Interest Expense, Interest Income, and Cash, the value in the Gross PP&E cell will be incorrect until capex is calculated in the CF statement,
but the link will be correct.
Accumulated Depreciation is calculated by adding the current year's depreciation expense to the prior year's Accumulated Depreciation figure.
Goodwill will remain unchanged throughout the forecast period (there will be no deductions or additions to goodwill in any year), per the Assumptions page.
Link all the debt balances to the respecting ending balances on the Debt and Interest Schedule (e.g. for Revolving Credit Facility, you'll link to the 'Ending Revolver
Balance' line in the Debt Schedule). These cell values will be incorrect until the Debt Schedule has been completed, but the links will be correct.
Retained Earnings is calculated by adding this year's Net Income to the prior year's Retained Earnings ending balance, and subtracting any dividends (none in this model).
Common Stock will remain unchanged throughout the forecast period.
Cash Flow
Net Income and Depreciation Expense should be sourced from the Income Statement.
Working Capital - Remember that year-over-year increases in asset accounts (i.e. AR, Inventory) are represented as uses (i.e. negative values) of cash on the cash flow
statement, and vice versa for liabilities.
Capex and Asset Dispositions calculated using the appropriate assumption per the Assumptions page.
For changes in items listed in the Financing section (Revolver, Term Loan, etc.), link to changes in these accounts on the Balance Sheet.
Change in Cash is equal to the sum of Cash from Operations, Cash from Investing, and Cash from Financing.
Beginning Cash Position is equal to the prior year's Ending Cash Position.
Change in Cash Position is equal to the Total Cash Flow line, above.
Debt and Interest Schedule
Revolver
Beginning Revolver Balance is equal to the prior year's Ending Revolver Balance.
Page 2
