英文财务分析模板

FINANCIALANALYSISMODELNote: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$8>4,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$8>4,SUM(F17:F20),"")OperatingProfits$85$89$216=IF($B$8>4,F15-F21,"")Less:InterestExpense$23$29$30$0$0NetProfitsBeforeTaxes$62$60$186=IF($B$8>4,F22-F23,"")Less:Taxes$10$12$15$0$0NetProfitAfterTaxes$52$48$171=IF($B$8>4,F24-F25,"")Less:Pref.StockDivds.$1$0$0$0$0EarningsAvailableforCommonStockholders$51$48$171IF($B$8>4,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$8>4,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$8>4,SUM(F39:F43),"")Less:AccumulatedDepreciation$52$63$65$0$0NetFixedAssets$128$132$135=IF($B$8>4,F44-F45,"")OtherAssets$0$0$0$0$0TotalAssets$490$553$581=IF($B$8>4,F37+F46,"")CurrentLiabilities:200520062007=IF($B$8>4,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$8>4,SUM(F51:F55),"")L/TDebt(Inc.FinancialLeases)$40$38$13$0$0TotalLiabilities$381$401$331=IF($B$8>4,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$8>4,SUM(F59:F62),"")TotalLiabs.&Stockhldrs'Equity$490$553$581=IF($B$8>4,F58+F63,"")ReconciliationTA&TL/SE$0$0$0=IF($B$8>4,F64-F48,"")NumberofCommonShares50050050000End-of-YearStockPrice$3.00$5.00$5.50$0.00$0.00RATIOANALYSISCurrentRatio1.061.161.40=IF($B$8>4,F37/F56,"")流动比率=流动资产/流动负债QuickRatio0.640.630.77=IF($B$8>4,(F37-F36)/F56,"")速动比率=速动资产/流动负责=（流动负债-存货）/流动负债InventoryTurnover6.905.395.20=IF($B$8>4,F14/F36,"")存货周转率=产品销售成本/存货AverageCollectionPeriod24.9635.3033.88=IF($B$8>4,F35/(F13/360),"")应收帐款周转天数=全年销售/360/应收帐款的余额=应收帐款的余额/平均销售额FixedAssetTurnover11.7211.7412.59=IF($B$8>4,F13/F46,"")TotalAssetTurnover3.062.802.93=IF($B$8>4,F13/F48,"")DebtRatio0.780.730.57=IF($B$8>4,F58/F48,"")Debt-to-Equity0.370.250.05=IF($B$8>4,F57/F63,"")TimesInterestEarned3.703.077.20=IF($B$8>4,F22/F23,"")GrossProfitMargin33.33%33.55%38.82%=IF($B$8>4,F15/F13,"")OperatingProfitMargin5.67%5.74%12.71%=IF($B$8>4,F22/F13,"")NetProfitMargin3.47%3.10%10.06%=IF($B$8>4,F26/F13,"")ReturnonTotalAssets(ROA)10.61%8.68%29.43%=IF($B$8>4,F26/F48,"")ReturnonEquity(ROE)47.71%31.58%68.40%=IF($B$8>4,F26/F63,"")EarningsPerShare$0.10$0.10$0.34=IF($B$8>4,F28/F66,"")Price/EarningsRatio29.4152.0816.08=IF(AND(F67>0,F89>0),F67/F89,"")DUPONTANALYSISNetProfitAT/Sales3.47%3.10%10.06%=IF($B$8>4,F26/F13,"")Sales/TotalAssets306.12%280.29%292.60%=IF($B$8>4,F13/F48,"")ROA10.61%8.68%29.43%=IF($B$8>4,F94*F95,"")NetProfitAT/TotalAssets10.61%8.68%29.43%=IF($B$8>4,F26/F48,"")TotalAssets/Stockhldrs.Equity449.54%363.82%232.40%=IF($B$8>4,F48/F63,"")ROE47.71%31.58%68.40%=IF($B$8>4,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