Financial model投资银行财务模型和模版.xls

Version 3 2 manufacturing Version 3 2 manufacturing CompanyCompany namename FinancialFinancial Model Model TableTable ofof ContentsContents 1 1 PRELIMINARY STATEMENTS InstructionsPage 1 AssumptionsPage 2 2 2 SUMMARY STATEMENTS Income StatementPage 4 Balance SheetPage 5 Statement of Sources however the financial model can be tailored to suit specific business needs or operating procedures Structural changes to the model such as additional product lines extra departments or embedded sales forecasts require modifications of the underlying formulas To make such changes please contact an ECS Consultant 4 4 PRINTING THE FINANCIAL STATEMENTS To print the entire model choose Print from the FILE menu and select Entire Workbook Click OK To print an individual worksheet such as the Assumptions sheet only click on the tab of the sheet you would like to print choose Print from the FILE menu and select Selected Sheet Click OK Page Page 2 2 CompanyCompany namename FinancialFinancial Model Model AssumptionsAssumptions SALESSALES UNITSUNITS SALESSALES2 20 00 05 52 20 00 06 62 20 00 07 72 20 00 08 82 20 00 09 9 Product One Product Two Product Three Services AVERAGEAVERAGE UNITUNIT PRICEPRICE2 20 00 05 52 20 00 06 62 20 00 07 72 20 00 08 82 20 00 09 9 Product One Product Two Product Three Services COSTCOST OFOF GOODSGOODS AVERAGEAVERAGE UNITUNIT COSTCOST2 20 00 05 52 20 00 06 62 20 00 07 72 20 00 08 82 20 00 09 9 Product One Product Two Product Three Services Returns as a percentage of Product One revenue 0 Returns as a percentage of Product Two revenue 0 Returns as a percentage of Product Three revenue 0 Returns as a percentage of Services revenue 0 BALANCEBALANCE SHEETSHEET Accounts Receivable adjustable up to 360 days in days 60 days Accounts Payable fixed at 30 days in days 30 days Salaries Payable fixed at 15 days in days 15 days Taxes Payable fixed at 90 days in days 90 days Inventory adjustable up to 360 days in days 0 days Available Credit Line as a percentage of net accounts receivable 0 Maximum Credit Line Used amount borrowed not to exceed 0 Capital Equipment Lease Term 1 year minimum in years 3 years Long Term Borrowings Term 1 year minimum in years 5 years DEPRECIATIONDEPRECIATIONHardwareHardwareSoftwareSoftwareFurnFurn FixturesFixtures Content3 years3 years3 years Engineering3 years3 years3 years Sales Marketing3 years3 years3 years Administration3 years3 years3 years Page Page 3 3 CompanyCompany namename FinancialFinancial Model Model AssumptionsAssumptions EXPENSESEXPENSES HEADCOUNTHEADCOUNT2 20 00 05 52 20 00 06 62 20 00 07 72 20 00 08 82 20 00 09 9 Content Engineering Sales Marketing Administration TOTALTOTAL PERPER PERSONPERSON EXPENSESEXPENSESSuppliesSuppliesTravelTravel MealsMealsPhone PostagePhone Postage Content100500100 Engineering100500100 Sales Marketing1002 500300 Administration1001 000250 EQUIPMENTEQUIPMENT PURCHASESPURCHASESHardwareHardwareSoftwareSoftwareFurnFurn FixturesFixtures Content3 0001 5001 000 Engineering3 5001 5001 000 Sales Marketing2 5001 0001 000 Administration1 5001 0001 000 Benefits Taxes as a percentage of salaries 25 Salary Increases as an annual percentage 5 Sales Commissions as a percentage of sales 100 Total Sales Through Commissions as a percentage of total revenue 10 Business Insurance as a percentage of total revenue 1 Anticipated Bad Debt as a percentage of collections 1 Interest Revenue as a percentage of cash balance 4 Interest Expense On Credit Line as a percentage of outstanding balance 10 Interest Expense On Capital Equipment Lease as a percentage of outstanding balance 10 Interest Expense On Long Term Borrowings as a percentage of outstanding balance 10 Combined Federal State Tax Rate as a percentage of positive cumulative income 40 Office Rent per square foot 1 50 Minimum Office Space square footage per person 250 sq ft Term of Office Lease in months 12 mos Utilities Expense per square foot 0 15 Maintanence Expense per square foot 0 10 Page Page 9 9 RECEIPTSRECEIPTS DISBURSEMENTSDISBURSEMENTS CompanyCompany namename 14 Dec 04 4 30 PM BEGINNINGBEGINNING CASHCASH ReceiptsReceipts Collections Financings Borrowings Interest Revenue TotalTotal receiptsreceipts DisbursementsDisbursements Salaries Benefits Empl Taxes Incentives commissions Direct Material Returns Advertising Promotional Material Trade Shows Supplies Materials Travel Meals Telephone Postage Maintenance Repair Rent Utilities Insurance Consultants Professional Services Taxes Equipment Purchases Debt Repayments Interest Expense Prior Accounts Payable TotalTotal DisbursementsDisbursements ChangesChanges in in CashCash ENDINGENDING CASHCASH Month 1Month 2Month 3Month 4Month 5Month 6Month 7Month 8Month 9Month 10Month 11Month 12TotalTotal Jan 05Feb 05Mar 05Apr 05May 05Jun 05Jul 05Aug 05Sep 05Oct 05Nov 05Dec 0520052005 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 Page Page 1010 RECEIPTSRECEIPTS DISBURSEMENTSDISBURSEMENTS CompanyCompany namename 14 Dec 04 4 30 PM BEGINNINGBEGINNING CASHCASH ReceiptsReceipts Collections Financings Borrowings Interest Revenue TotalTotal receiptsreceipts DisbursementsDisbursements Salaries Benefits Empl Taxes Incentives commissions Direct Material Returns Advertising Promotional Material Trade Shows Supplies Materials Travel Meals Telephone Postage Maintenance Repair Rent Utilities Insurance Consultants Professional Services Taxes Equipment Purchases Debt Repayments Interest Expense Prior Accounts Payable TotalTotal DisbursementsDisbursements ChangesChanges in in CashCash ENDINGENDING CASHCASH Month 13Month 14Month 15Month 16Month 17Month 18Month 19Month 20Month 21Month 22Month 23Month 24TotalTotal Jan 06Feb 06Mar 06Apr 06May 06Jun 06Jul 06Aug 06Sep 06Oct 06Nov 06Dec 0620062006 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 Page Page 1111 RECEIPTSRECEIPTS DISBURSEMENTSDISBURSEMENTS CompanyCompany namename 14 Dec 04 4 30 PM BEGINNINGBEGINNING CASHCASH ReceiptsReceipts Collections Financings Borrowings Interest Revenue TotalTotal receiptsreceipts DisbursementsDisbursements Salaries Benefits Empl Taxes Incentives commissions Direct Material Returns Advertising Promotional Material Trade Shows Supplies Materials Travel Meals Telephone Postage Maintenance Repair Rent Utilities Insurance Consultants Professional Services Taxes Equipment Purchases Debt Repayments Interest Expense Prior Accounts Payable TotalTotal DisbursementsDisbursements ChangesChanges in in CashCash ENDINGENDING CASHCASH TotalTotalTotalTotalTotalTotal 200720072008200820092009 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 Page Page 1212 STATEMENTSTATEMENT OFOF SOURCESSOURCES ANDAND USESUSES CompanyCompany namename 14 Dec 04 4 30 PM BEGINNINGBEGINNING CASHCASH SourcesSources ofof CashCash Net Income Add Depr Amort Issuance of Preferred Stock Issuance of Common Stock PlusPlus ChangesChanges In In Accounts Payable 30 days Salaries Payable 15 days Taxes Payable 90 days Additions to Line of Credit 0 of net A R Additions to Capital Equipment Lease 3 years Additions to Long Term Debt 5 years TotalTotal SourcesSources ofof CashCash UsesUses ofof CashCash Buyback of Preferred Stock Buyback of Common Stock LessLess ChangesChanges In In Net Accounts Rec Inventory 0 days Gross Fixed Assets Reductions To Credit Line Reductions To Capital Equipment Lease Reductions To Long Term Debt TotalTotal UsesUses CHANGESCHANGES ININ CASHCASH ENDINGENDING CASHCASH Month 1Month 2Month 3Month 4Month 5Month 6Month 7Month 8Month 9Month 10Month 11Month 12TotalTotal Jan 05Feb 05Mar 05Apr 05May 05Jun 05Jul 05Aug 05Sep 05Oct 05Nov 05Dec 0520052005 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 Page Page 1313 STATEMENTSTATEMENT OFOF SOURCESSOURCES ANDAND USESUSES CompanyCompany namename 14 Dec 04 4 30 PM BEGINNINGBEGINNING CASHCASH SourcesSources ofof CashCash Net Income Add Depr Amort Issuance of Preferred Stock Issuance of Common Stock PlusPlus ChangesChanges In In Accounts Payable 30 days Salaries Payable 15 days Taxes Payable 90 days Additions to Line of Credit 0 of net A R Additions to Capital Equipment Lease 3 years Additions to Long Term Debt 5 years TotalTotal SourcesSources ofof CashCash UsesUses ofof CashCash Buyback of Preferred Stock Buyback of Common Stock LessLess ChangesChanges In In Net Accounts Rec Inventory 0 days Gross Fixed Assets Reductions To Credit Line Reductions To Capital Equipment Lease Reductions To Long Term Debt TotalTotal UsesUses CHANGESCHANGES ININ CASHCASH ENDINGENDING CASHCASH Month 13Month 14Month 15Month 16Month 17Month 18Month 19Month 20Month 21Month 22Month 23Month 24TotalTotal Jan 06Feb 06Mar 06Apr 06May 06Jun 06Jul 06Aug 06Sep 06Oct 06Nov 06Dec 0620062006 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 Page Page 1414 STATEMENTSTATEMENT OFOF SOURCESSOURCES ANDAND USESUSES CompanyCompany namename 14 Dec 04 4 30 PM BEGINNINGBEGINNING CASHCASH SourcesSources ofof CashCash Net Income Add Depr Amort Issuance of Preferred Stock Issuance of Common Stock PlusPlus ChangesChanges In In Accounts Payable 30 days Salaries Payable 15 days Taxes Payable 90 days Additions to Line of Credit 0 of net A R Additions to Capital Equipment Lease 3 years Additions to Long Term Debt 5 years TotalTotal SourcesSources ofof CashCash UsesUses ofof CashCash Buyback of Preferred Stock Buyback of Common Stock LessLess ChangesChanges In In Net Accounts Rec Inventory 0 days Gross Fixed Assets Reductions To Credit Line Reductions To Capital Equipment Lease Reductions To Long Term Debt TotalTotal UsesUses CHANGESCHANGES ININ CASHCASH ENDINGENDING CASHCASH TotalTotalTotalTotalTotalTotal 200720072008200820092009 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 Page Page 1515 INCOMEINCOME STATEMENTSTATEMENT CompanyCompany namename 14 Dec 04 4 30 PM RevenueRevenue Product One Product Two Product Three Services TotalTotal RevenueRevenue ExpensesExpenses CostCost ofof GoodsGoods SoldSold Direct Labor Other Direct Costs Direct Materials Returns TotalTotal COGSCOGS GrossGross MarginMargin of Sales OperatingOperating ExpensesExpenses ENGINEERINGENGINEERING Headcount Salary Incentives Benefits Taxes Consultants Supplies Materials Travel Meals Telephone Postage Operations Setup Depreciation TotalTotal ExpensesExpenses of Sales SALESSALES MARKETINGMARKETING Headcount Salary Incentives Commissions Benefits Taxes Supplies Travel Meals Telephone Postage Depreciation TotalTotal ExpensesExpenses of Sales Month 1Month 2Month 3Month 4Month 5Month 6Month 7Month 8Month 9Month 10Month 11Month 12TotalTotal Jan 05Feb 05Mar 05Apr 05May 05Jun 05Jul 05Aug 05Sep 05Oct 05Nov 05Dec 0520052005 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Page Page 1616 INCOMEINCOME STATEMENTSTATEMENT CompanyCompany namename 14 Dec 04 4 30 PM RevenueRevenue Product One Product Two Product Three Services TotalTotal RevenueRevenue ExpensesExpenses CostCost ofof GoodsGoods SoldSold Direct Labor Other Direct Costs Direct Materials Returns TotalTotal COGSCOGS GrossGross MarginMargin of Sales OperatingOperating ExpensesExpenses ENGINEERINGENGINEERING Headcount Salary Incentives Benefits Taxes Consultants Supplies Materials Travel Meals Telephone Postage Operations Setup Depreciation TotalTotal ExpensesExpenses of Sales SALESSALES MARKETINGMARKETING Headcount Salary Incentives Commissions Benefits Taxes Supplies Travel Meals Telephone Postage Depreciation TotalTotal ExpensesExpenses of Sales Month 13Month 14Month 15Month 16Month 17Month 18Month 19Month 20Month 21Month 22Month 23Month 24TotalTotal Jan 06Feb 06Mar 06Apr 06May 06Jun 06Jul 06Aug 06Sep 06Oct 06Nov 06Dec 0620062006 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Page Page 1717 INCOMEINCOME STATEMENTSTATEMENT CompanyCompany namename 14 Dec 04 4 30 PM RevenueRevenue Product One Product Two Product Three Services TotalTotal RevenueRevenue ExpensesExpenses CostCost ofof GoodsGoods SoldSold Direct Labor Other Direct Costs Direct Materials Returns TotalTotal COGSCOGS GrossGross MarginMargin of Sales OperatingOperating ExpensesExpenses ENGINEERINGENGINEERING Headcount Salary Incentives Benefits Taxes Consultants Supplies Materials Travel Meals Telephone Postage Operations Setup Depreciation TotalTotal ExpensesExpenses of Sales SALESSALES MARKETINGMARKETING Headcount Salary Incentives Commissions Benefits Taxes Supplies Travel Meals Telephone Postage Depreciation TotalTotal ExpensesExpenses of Sales TotalTotalTotalTotalTotalTotal 200720072008200820092009 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 0 0 0 0 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 0 0 0 0 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 00 00 0 0 0 0 0 0 0 Page Page 1818 INCOMEINCOME STATEMENTSTATEMENT CompanyCompany namename 14 Dec 04 4 30 PM Adv PromotionAdv Promotion Advertising Trade Shows Collateral TotalTotal Adv PromotionAdv Promotion of Sales ADMINISTRATIONADMINISTRATION Headcount Salary Incentives Benefits Taxes Office Supplies Travel Meals Telephone Postage Insurance Professional Services Bad Debt Office Rent Utilities Maint Repair Depreciation TotalTotal ExpensesExpenses of Sales TotalTotal OperatingOperating ExpExp of Sales IncomeIncome BeforeBefore IntInt TaxesTaxes of Sales InterestInterest Interest Revenue 4 of cash Interest Expense 10 of Credit Line Interest Expense 10 of Cap Equip Lease Interest Expense 10 of Long Term Debt NetNet InterestInterest RevenueRevenue IncomeIncome BeforeBefore TaxesTaxes Tax Exp NetNet IncomeIncome of Sales Month 1Month 2Month 3Month 4Month 5Month 6Month 7Month 8Month 9Month 10Month 11Month 12TotalTotal Jan 05Feb 05Mar 05Apr 05May 05Jun 05Jul 05Aug 05Sep 05Oct 05Nov 05Dec 0520052005 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0000000000000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0000000000000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0000000000000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Page Page 1919 INCOMEINCOME STATEMENTSTATEMENT CompanyCompany namename 14 Dec 04 4 30 PM Adv PromotionAdv Promotion Advertising Trade Shows Collateral TotalTotal Adv PromotionAdv Promotion of Sales ADMINISTRATIONADMINISTRATION Headcount Salary Incentives Benefits Taxes Office Supplies Travel Meals Telephone Postage Insurance Professional Services Bad Debt Office Rent Utilities Maint Repair Depreciation TotalTotal ExpensesExpenses of Sales TotalTotal OperatingOperating ExpExp of Sales IncomeIncome BeforeBefore IntInt TaxesTaxes of Sales InterestInterest Interest Revenue 4 of cash Interest Expense 10 of Credit Line Interest Expense 10 of Cap Equip Lease Inte