美国版财务分析模板

精品资料网（）25万份精华管理资料，2万多集管理视频讲座1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071ABCDEFGHIJFINANCIALANALYSISMODELNote:EnterdatainBlue-codedcells;BlackcellsarecomputergeneratedANALYST'SNAMEDr.GlennL.StevensNAMEOFCOMPANYSampleFirmFIRSTCALENDARYEAROFDATA2005NUMBEROFYEARSOFDATA3INCOMESTATEMENT200520062007SalesRevenue$1,500$1,550$1,700$0$0Less:CostofGoodsSold$1,000$1,030$1,040$0$0GrossProfits$500$520$660=IF($B$84,F13-F14,)Less:OperatingExpenses:SellingExpense$140$150$155$0$0General/Admin.Exp.$260$270$275$0$0LeaseExpense$5$0$2$0$0DepreciationExpense$10$11$12$0$0TotalOperatingExpenses$415$431$444=IF($B$84,SUM(F17:F20),)OperatingProfits$85$89$216=IF($B$84,F15-F21,)Less:InterestExpense$23$29$30$0$0NetProfitsBeforeTaxes$62$60$186=IF($B$84,F22-F23,)Less:Taxes$10$12$15$0$0NetProfitAfterTaxes$52$48$171=IF($B$84,F24-F25,)Less:Pref.StockDivds.$1$0$0$0$0EarningsAvailableforCommonStockholders$51$48$171IF($B$84,F26-F27,)BALANCESHEETCurrentAssets:Cash$31$12$18$0$0MarketableSecurities$82$66$68$0$0AccountsReceivable$104$152$160$0$0Inventories$145$191$200$0$0TotalCurrentAssets$362$421$446=IF($B$84,SUM(F33:F36),)GrossFixedAssets(atcost):Land&Buildings$180$195$200$0$0MachineryandEquipment$0$0$0$0$0Furniture&Fixtures$0$0$0$0$0Vehicles$0$0$0$0$0Other(Inc.Fin.Leases)$0$0$0$0$0TotalGrossFixedAssets$180$195$200=IF($B$84,SUM(F39:F43),)Less:AccumulatedDepreciation$52$63$65$0$0NetFixedAssets$128$132$135=IF($B$84,F44-F45,)OtherAssets$0$0$0$0$0TotalAssets$490$553$581=IF($B$84,F37+F46,)CurrentLiabilities:200520062007=IF($B$84,F37+F46,)AccountsPayable$126$136$150$0$0NotesPayable$190$200$140$0$0Accruals$25$27$28$0$0TaxesPayable$0$0$0$0$0OtherCurrentLiabilities$0$0$0$0$0TotalCurrentLiabilities$341$363$318=IF($B$84,SUM(F51:F55),)L/TDebt(Inc.FinancialLeases)$40$38$13$0$0TotalLiabilities$381$401$331=IF($B$84,F56+F57,)PreferredStock$0$0$0$0$0CommonStock$20$20$20$0$0Paid-InCapitalInExcessofPar$30$30$30$0$0RetainedEarnings$59$102$200$0$0TotalStockholders'Equity$109$152$250=IF($B$84,SUM(F59:F62),)TotalLiabs.&Stockhldrs'Equity$490$553$581=IF($B$84,F58+F63,)ReconciliationTA&TL/SE$0$0$0=IF($B$84,F64-F48,)NumberofCommonShares50050050000End-of-YearStockPrice$3.00$5.00$5.50$0.00$0.00RATIOANALYSISCurrentRatio1.061.161.40=IF($B$84,F37/F56,)QuickRatio0.640.630.77=IF($B$84,(F37-F36)/F56,)72737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129ABCDEFGHIJInventoryTurnover6.905.395.20=IF($B$84,F14/F36,)AverageCollectionPeriod24.9635.3033.88=IF($B$84,F35/(F13/360),)FixedAssetTurnover11.7211.7412.59=IF($B$84,F13/F46,)TotalAssetTurnover3.062.802.93=IF($B$84,F13/F48,)DebtRatio0.780.730.57=IF($B$84,F58/F48,)Debt-to-Equity0.370.250.05=IF($B$84,F57/F63,)TimesInterestEarned3.703.077.20=IF($B$84,F22/F23,)GrossProfitMargin33.33%33.55%38.82%=IF($B$84,F15/F13,)OperatingProfitMargin5.67%5.74%12.71%=IF($B$84,F22/F13,)NetProfitMargin3.47%3.10%10.06%=IF($B$84,F26/F13,)ReturnonTotalAssets(ROA)10.61%8.68%29.43%=IF($B$84,F26/F48,)ReturnonEquity(ROE)47.71%31.58%68.40%=IF($B$84,F26/F63,)EarningsPerShare$0.10$0.10$0.34=IF($B$84,F28/F66,)Price/EarningsRatio29.4152.0816.08=IF(AND(F670,F890),F67/F89,)DUPONTANALYSISNetProfitAT/Sales3.47%3.10%10.06%=IF($B$84,F26/F13,)Sales/TotalAssets306.12%280.29%292.60%=IF($B$84,F13/F48,)ROA10.61%8.68%29.43%=IF($B$84,F94*F95,)NetProfitAT/TotalAssets10.61%8.68%29.43%=IF($B$84,F26/F48,)TotalAssets/Stockhldrs.Equity449.54%363.82%232.40%=IF($B$84,F48/F63,)ROE47.71%31.58%68.40%=IF($B$84,F97*F98,)STATEMENTOFCASHFLOWSCASHFLOWFROMOPERATINGACTIVITIES200520062007NetProfitAfterTaxes$48$171Depreciation$11$12DecreaseinAccts.Receiv.($48)($8)DecreaseinInventories($46)($9)DecreaseinOtherAssets$0$0IncreaseinAccountsPay.$10$14IncreaseinAccruals$2$1IncreaseinTaxesPayable$0$0IncreaseinOtherCur.Lia.$0$0CashFlowfromOper.Act.($23)$181CASHFLOWFROMINVESTMENTACTIVITIESIncreaseinGrossFixedAssets($15)($5)CashFlowfromInvestments($15)($5)CASHFLOWFROMFINANCINGACTIVITIESIncreaseinNotesPayable$10($60)IncreaseinLong-TermDebt($2)($25)ChangesinStockholders'Equity$0$0PreferredDividendsPaid$0$0CommonDividendsPaid(-)($5)CashFlowfromFinancingActivities$3($85)NetIncr.inCash/Mark.Securities($35)$91