四大之一的财务分析全套分析Excel模型

BusinessCaseAnalysisCompanyNameConstructionMachineryManufacturer#1ProjectNameMulti-LaneProjectPreparedbyNumberofYearsWACC8.69%ClientXCashFlowAnalysis(Yearly)GraphsMulti-LaneProjectYear1Year2Year3Year4Year5TotalCosts($630,605)($674,184)($225,215)($14,171)$0TotalBenefits$318,859$968,229$1,830,923$1,910,737$1,595,453($1,000,000)($500,000)$0$500,000$1,000,000$1,500,000$2,000,000$2,500,000Year1Year2Year3Year4Year5CashFlowforMulti-LaneProjectTotalCostsTotalBenefits($1,000,000)$0$1,000,000$2,000,000$3,000,000$4,000,000$5,000,000$6,000,00012345USDTime-YearsBreakevenAnalysis($800,000)($700,000)($600,000)($500,000)($400,000)($300,000)($200,000)($100,000)$012345USDYearAnnualCostConsumptionCommunication&TrainingSupplyChainProcessesCustomerFacingProcessesStandardMulti-LaneProcessandPackaging$0$500,000$1,000,000$1,500,000$2,000,000$2,500,00012345USDYearAnnualTotalBenefitRealizationTransportationInventoryQualityManufacturingMarginTotalGrossBenefitsbyBenefitCategoryLowHighAverage$2,101,615$7,814,867$4,958,241$45,444$91,105$68,274$12,370$13,607$12,989$1,032,234$1,562,207$1,297,221$183,017$391,934$287,476$3,374,680$9,873,721$6,624,201TotalProjected(Average)BenefitsbyYearYear1Year2Year3Year4Year5$4,958,241$318,859$637,718$1,120,543$1,454,605$1,426,516$68,274$0$6,207$12,414$24,827$24,827$12,989$0$0$1,443$4,330$7,216$1,297,221$0$324,305$648,610$324,305$0$287,476$0$0$47,913$102,670$136,893$6,624,201$318,859$968,229$1,830,923$1,910,737$1,595,453LowHighYear1Year2Year3Year4Year51.1:SalesUpliftfromPopularConfigurations$401,266$1,605,06525%50%75%100%100%1.2:Salesupliftfrompackagedconfigurations$200,633$601,89913%25%75%100%100%1.3:AdditionalParts&ServicesUplift$48,152$176,55713%25%50%25%0%1.4:ImprovedMarginfromReducedNeedforVariances$5,300$38,86818%35%50%100%100%Year1Year2Year3Year4Year5$132,342$264,685$478,151$619,238$607,200$505,375$1,010,751$1,762,936$2,289,972$2,245,833$318,859$637,718$1,120,543$1,454,605$1,426,516LowHighYear1Year2Year3Year4Year52.1:Reductionindirectlaborcostduetoincreasedthroughputfromproducingcommonconfigurations$98$2750%25%50%100%100%2.2:Reductioninreworklaborhoursforreworkandrepair$16,427$32,8540%25%50%100%100%Year1Year2Year3Year4Year5$0$4,131$8,262$16,525$16,525$0$8,282$16,565$33,129$33,129$0$6,207$12,414$24,827$24,827BenefitsAllocationMulti-LaneStrategyBusinessCaseProjectedValue%BenefitsRealized1.IncrementalMargin%BenefitsRealizedTotalAverage2.IncreaseinManufacturingEfficiency%BenefitsRealizedProjects%BenefitsRealizedLowHighBenefitsMarginRangeAverageTotalBenefitsProjectedBenefitsAverageBenefitsManufacturingQualityTransportationInventoryMarginManufacturingQualityInventoryTotalTransportationProjectedValueHighLowClientXCashFlowAnalysis(Yearly)BenefitandCostSummaryMulti-LaneProjectTotalYear1Year2Year3Year4Year512345COSTSStandardMulti-LaneProcessandPackaging($629,887)($534,557)($94,206)($1,124)CustomerFacingProcesses($168,280)($20,156)($148,124)SupplyChainProcesses($738,642)($74,119)($428,897)($221,828)($13,799)Communication&Training($7,366)($1,774)($2,956)($2,264)($372)TotalCosts($1,544,175)($630,605)($674,184)($225,215)($14,171)CumulativeCosts($630,605)($1,304,789)($1,530,004)($1,544,175)($1,544,175)BENEFITSMargin$4,958,241$318,859$637,718$1,120,543$1,454,605$1,426,516Manufacturing$68,274$6,207$12,414$24,827$24,827Quality$12,989$1,443$4,330$7,216Transportation$287,476$47,913$102,670$136,893TotalBenefits$5,326,980$318,859$643,924$1,182,313$1,586,431$1,595,453CumulativeBenefits$368,739$318,859$962,783$2,145,096$3,731,527$5,326,980INVENTORYIMPACTInventoryCashFlowImpact$1,297,221$324,305$648,610$324,305CASHFLOWNetCashFlow(NCF)$5,080,026($311,746)$294,046$1,605,708$1,896,565$1,595,453CumulativeNCF$5,080,026($311,746)($17,700)$1,588,008$3,484,573$5,080,026DISCOUNTEDSUMMARYDiscountedCashFlows(DCF)$3,623,401($286,822)$248,906$1,250,541$1,358,969$1,051,807CumulativeDCF$3,623,401($286,822)($37,915)$1,212,626$2,571,594$3,623,401PAYBACKDATAPaybackPeriodinYears3DiscountedPaybackPeriod3.27NPV$3,623,401BenefitFITTitle:1Description:FinancialAssumptionsCategory:SalesandMarginUpliftYear2One-Time/Recurring:RecurringBaselineDataDescriptionCurrentValueAverageRevenue$145,330AnnualSalesVolume871Parts/ServiceRevenue$11,626Contributionmargin32%Contributionmarginforpartsandservices50%Incentivepayments$25,595Category1.1:SalesUpliftfromPopularConfigurationsCalculationsDescriptionCurrentValueLowHighLowHighA1$126,582,430A21.0%4%RealizedbenefitsDescriptionCurrentValueLowHighLowHighB1935B2$1,265,824$5,063,297B332%B4$401,266$1,605,065Category1.2:SalesupliftfrompackagedconfigurationsCalculationsDescriptionCurrentValueLowHighLowHighA1$126,582,430AveragemarginpermachineProjectedmarginincreaseProjectedValue%ChangeCurrentannualmachinesalesSalesupliftduetoimprovedproductavailability(unitsperyear)Salesupliftduetoimprovedproductavailability(dollarsperyear)ProjectedValue%ChangeCurrentannualmachinesalesSalesuplift%duetoimprovedproductavailabilityfromPDC7Averageparts&servicesrevenuepermachineperyear3Averagemarginpermachine4Averagemarginforpartsandservice