Business Case Financial Analysis Template

1、667a0e8ccf239299e71f367b07a63a02.xlsPage 1PROPOSAL CASH FLOW$ in 1000sDiscount rate 8.0%The discount rate entered here will be used on all worksheetsYear ending$ in 1000sJun 30Jun 30Jun 30Jun 30 TOTALBlue cells are for user input (unlocked)2009201020112012Yellow cells hold formulas and are calculate

2、d automatically (locked)BENEFITS / GAINSPositive numbers are cash inflowsNumbers in parenthesis are negative numbers (cash outflows)Benefit item 1.1200.01800.01900.02150.07050.0Benefit item 245.056.057.067.0225.0Benefit item 3.20.020.020.040.0100.0Benefit item 4.59.061.062.067.0249.0Total Benefits/G

3、ains1324.01937.02039.02324.07624.0COST ITEMS inflows (outflows)Personal Services Costs (Salaries & Benefits)State Perm Staff.(100.0)(120.0)(125.0)(60.0)(405.0)State Temp Staff.(400.0)(120.0)(100.0)(40.0)(660.0)State LD Staff.(30.0)(40.0)(50.0)(60.0)(180.0)Services & Supplies/Capital Outlay C

4、ostsState Data Center CostsConsulting Services.(60.0)(60.0)(60.0)(60.0)(240.0)Hosting(80.0)(95.0)(100.0)(110.0)(385.0)Storage.(20.0)(23.0)(25.0)(30.0)(98.0)Network.(40.0)(40.0)(60.0)(70.0)(210.0)Software CostsSW Purchase/Upgrade.(20.0)(40.0)(40.0)(50.0)(150.0)SW License Maintenance.(10.0)(10.0)(10.0

5、)(20.0)(50.0)Hardware CostsHardware Purchase/Upgrade(50.0)(50.0)(50.0)(50.0)(200.0)Hardware Ongoing Maint. (30.0)(30.0)(30.0)(30.0)(120.0)IT Professional ServicesProject Dev/Implementation.(20.0)(20.0)(20.0)(20.0)(80.0)Operational Staff.(110.0)(110.0)(110.0)(120.0)(450.0)Operational Augmentation.(30

6、.0)(40.0)(50.0)(40.0)(160.0)Other. (70.0)(70.0)(90.0)(100.0)(330.0)Total Costs(1070.0)(868.0)(920.0)(860.0)(3718.0)CASH FLOW SUMMARY inflows (outflows)Cash inflows (outflows)Benefits and Gains.1324.01937.02039.02324.07624.0Costs.(1070.0)(868.0)(920.0)(860.0)(3718.0)NET CASH FLOW254.01069.01119.01464

7、.03906.0Cumulative Net CF254.01323.02442.03906.03906.0Discounted Cash FlowNPVNPV At 8.0%.235.2916.5888.3996.43036.4667a0e8ccf239299e71f367b07a63a02.xlsPage 2ALTERNATIVE PROPOSAL CASH FLOW$ in 1000sDiscount rate 8.0%Discount rate is entered on Proposal CF WorksheetYear ending$ in 1000sJun 30Jun 30Jun

8、 30Jun 30 TOTALBlue cells are for user input (unlocked)2009201020112012Yellow cells hold formulas and are calculated automatically (locked)BENEFITS / GAINSPositive numbers are cash inflowsNumbers in parenthesis are negative numbers (cash outflows)Benefit item 1.900.01200.01500.01750.05350.0Benefit i

9、tem 242.651.059.060.0212.6Benefit item 3.15.014.017.030.076.0Benefit item 4.43.026.029.034.0132.0Total Benefits/Gains1000.61291.01605.01874.05770.6COST ITEMS inflows (outflows)Personal Services Costs (Salaries & Benefits)State Perm Staff.(110.0)(130.0)(135.0)(66.0)(441.0)State Temp Staff.(490.0)

10、(170.0)(120.0)(60.0)(840.0)State LD Staff.(35.0)(46.0)(59.0)(67.0)(207.0)Services & Supplies/Capital Outlay CostsState Data Center CostsConsulting Services.(60.0)(60.0)(60.0)(60.0)(240.0)Hosting(89.0)(100.0)(110.0)(110.0)(409.0)Storage.(25.0)(33.0)(34.0)(50.0)(142.0)Network.(40.0)(40.0)(60.0)(70

11、.0)(210.0)Software CostsSW Purchase/Upgrade.(20.0)(50.0)(40.0)(60.0)(170.0)SW License Maintenance.(10.0)(30.0)(10.0)(20.0)(70.0)Hardware CostsHardware Purchase/Upgrade(50.0)(50.0)(60.0)(50.0)(210.0)Hardware Ongoing Maint. (30.0)(30.0)(30.0)(50.0)(140.0)IT Professional ServicesProject Dev/Implementat

12、ion.(20.0)(28.0)(30.0)(40.0)(118.0)Operational Staff.(120.0)(120.0)(120.0)(140.0)(500.0)Operational Augmentation.(30.0)(40.0)(50.0)(40.0)(160.0)Other. (70.0)(70.0)(90.0)(100.0)(330.0)Total Costs(1199.0)(997.0)(1008.0)(983.0)(4187.0)CASH FLOW SUMMARY inflows (outflows)Cash inflows (outflows)Benefits

13、and Gains.1000.61291.01605.01874.05770.6Costs.(1199.0)(997.0)(1008.0)(983.0)(4187.0)NET CASH FLOW(198.4)294.0597.0891.01583.6Cumulative Net CF(198.4)95.6692.61583.61583.6Discounted Cash FlowNPVNPV At 8.0%.(183.7)252.1473.9654.91197.2667a0e8ccf239299e71f367b07a63a02.xlsPage 3CURRENT STATE (BASELINE)

14、CASH FLOW$ in 1000sDiscount rate 8.0%Discount rate is entered on Proposal CF WorksheetYear ending$ in 1000sJun 30Jun 30Jun 30Jun 30 TOTALBlue cells are for user input (unlocked)2009201020112012Yellow cells hold formulas and are calculated automatically (locked)BENEFITS / GAINSPositive numbers are ca

15、sh inflowsNumbers in parenthesis are negative numbers (cash outflows)Benefit item 1.300.0400.0700.0750.02150.0Benefit item 230.030.034.034.0128.0Benefit item 3.12.012.012.012.048.0Benefit item 4.25.026.026.026.0103.0Total Benefits/Gains367.0468.0772.0822.02429.0COST ITEMS inflows (outflows)Personal

16、Services Costs (Salaries & Benefits)State Perm Staff.(120.0)(150.0)(160.0)(100.0)(530.0)State Temp Staff.(500.0)(180.0)(130.0)(70.0)(880.0)State LD Staff.(50.0)(50.0)(70.0)(90.0)(260.0)Services & Supplies/Capital Outlay CostsState Data Center CostsConsulting Services.(80.0)(80.0)(80.0)(80.0)

17、(320.0)Hosting (100.0)(110.0)(120.0)(130.0)(460.0)Storage.(50.0)(50.0)(50.0)(60.0)(210.0)Network.(50.0)(50.0)(65.0)(75.0)(240.0)Software CostsSW Purchase/Upgrade.(25.0)(45.0)(59.0)(55.0)(184.0)SW License Maintenance.(12.0)(12.0)(12.0)(30.0)(66.0)Hardware CostsHardware Purchase/Upgrade(100.0)(120.0)(

18、120.0)(130.0)(470.0)Hardware Ongoing Maint. (40.0)(40.0)(40.0)(40.0)(160.0)IT Professional ServicesProject Dev/Implementation.(30.0)(30.0)(30.0)(30.0)(120.0)Operational Staff.(120.0)(120.0)(120.0)(130.0)(490.0)Operational Augmentation.(34.0)(44.0)(54.0)(45.0)(177.0)Other. (75.0)(80.0)(99.0)(100.0)(3

19、54.0)Total Costs(1386.0)(1161.0)(1209.0)(1165.0)(4921.0)CASH FLOW SUMMARY inflows (outflows)Cash inflows (outflows)Benefits and Gains.367.0468.0772.0822.02429.0Costs.(1386.0)(1161.0)(1209.0)(1165.0)(4921.0)NET CASH FLOW(1019.0)(693.0)(437.0)(343.0)(2492.0)Cumulative Net CF(1019.0)(1712.0)(2149.0)(24

20、92.0)(2492.0)Discounted Cash FlowNPVNPV At 8.0%.(943.5)(594.1)(346.9)(252.1)(2136.7)667a0e8ccf239299e71f367b07a63a02.xlsPage 4INCREMENTAL CASH FLOW 2All figures represent (Proposal Value) - (Current State Value)$ in 1000sDiscount rate 8.0%Discount rate is entered on Proposal CF WorksheetYear ending$

21、 in 1000sJun 30Jun 30Jun 30Jun 30 TOTALAll values on this worksheet are derived from entries on other sheets2009201020112012Yellow cells hold formulas and are calculated automatically (locked)BENEFITS / GAINSPositive numbers are cash inflowsNumbers in parenthesis are negative numbers (cash outflows)

22、Benefit item 1.900.01400.01200.01400.04900.0Benefit item 215.026.023.033.097.0Benefit item 3.8.08.08.028.052.0Benefit item 4.34.035.036.041.0146.0Total Benefits/Gains957.01469.01267.01502.05195.0COST ITEMS inflows (outflows)Personal Services Costs (Salaries & Benefits)State Perm Staff.20.030.035

23、.040.0125.0State Temp Staff.100.060.030.030.0220.0State LD Staff.20.010.020.030.080.0Services & Supplies/Capital Outlay CostsState Data Center CostsConsulting Services.20.020.020.020.080.0Hosting20.015.020.020.075.0Storage.30.027.025.030.0112.0Network.10.010.05.05.030.0Software CostsSW Purchase/

24、Upgrade.5.05.019.05.034.0SW License Maintenance. 2.02.02.010.016.0Hardware CostsHardware Purchase/Upgrade50.070.070.080.0270.0Hardware Ongoing Maint.10.010.010.010.040.0IT Professional ServicesProject Dev/Implementation.10.010.010.010.040.0Operational Staff.10.010.010.010.040.0Operational Augmentati

25、on.4.04.04.05.017.0Other. 5.010.09.00.024.0Total Costs316.0293.0289.0305.01203.0CASH FLOW SUMMARY inflows (outflows)Cash inflows (outflows)Benefits and Gains.957.01469.01267.01502.05195.0Costs.316.0293.0289.0305.01203.0NET CASH FLOW1273.01762.01556.01807.06398.0Cumulative Net CF1273.03035.04591.0639

26、8.06398.0Discounted Cash FlowNPVNPV At 8.0%.1178.71510.61235.21328.25252.7667a0e8ccf239299e71f367b07a63a02.xlsPage 5PROPOSED ALTERNATIVE INCREMENTAL CASH FLOW All figures represent (AltProposal Value) - (Current State Value)$ in 1000sDiscount rate 8.0%Discount rate is entered on Proposal CF Workshee

27、tYear ending$ in 1000sJun 30Jun 30Jun 30Jun 30 TOTALAll values on this worksheet are derived from entries on other sheets2009201020112012Yellow cells hold formulas and are calculated automatically (locked)BENEFITS / GAINSPositive numbers are cash inflowsNumbers in parenthesis are negative numbers (c

28、ash outflows)Benefit item 1.600.0800.0800.01000.03200.0Benefit item 212.621.025.026.084.6Benefit item 3.3.02.05.018.028.0Benefit item 4.18.00.03.08.029.0Total Benefits/Gains633.6823.0833.01052.03341.6COST ITEMS inflows (outflows)Personal Services Costs (Salaries & Benefits)State Perm Staff.10.02

29、0.025.034.089.0State Temp Staff.10.010.010.010.040.0State LD Staff.15.04.011.023.053.0Services & Supplies/Capital Outlay CostsState Data Center CostsConsulting Services.20.020.020.020.080.0Hosting11.010.010.020.051.0Storage.25.017.016.010.068.0Network.10.010.05.05.030.0Software CostsSW Purchase/

30、Upgrade.5.0(5.0)19.0(5.0)14.0SW License Maintenance. 2.0(18.0)2.010.0(4.0)Hardware CostsHardware Purchase/Upgrade50.070.060.080.0260.0Hardware Ongoing Maint.10.010.010.0(10.0)20.0IT Professional ServicesProject Dev/Implementation.10.02.00.0(10.0)2.0Operational Staff.0.00.00.0(10.0)(10.0)Operational

31、Augmentation.4.04.04.05.017.0Other. 5.010.09.00.024.0Total Costs187.0164.0201.0182.0734.0CASH FLOW SUMMARY inflows (outflows)Cash inflows (outflows)Benefits and Gains.633.6823.0833.01052.03341.6Costs.187.0164.0201.0182.0734.0NET CASH FLOW820.6987.01034.01234.04075.6Cumulative Net CF820.61807.62841.6

32、4075.64075.6Discounted Cash FlowNPVNPV At 8.0%.759.8846.2820.8907.03333.9Proposal Cash Flow20092010201120120.0 200.0 400.0 600.0 800.0 1000.0 1200.0 1400.0 1600.0 Fiscal YearAnnual Cash Flow (1,000s)Alternate Proposal CF2009201020112012(400.0)(200.0)0.0 200.0 400.0 600.0 800.0 1000.0 Fiscal YearAnnu

33、al Cash Flow (1,000s)Alternate Proposal Cumulative CF2009201020112012(500.0)0.0 500.0 1000.0 1500.0 2000.0 Fiscal YearCumulative Cash Flow ($1,000s)Current State Cash Flow2009201020112012(1200.0)(1000.0)(800.0)(600.0)(400.0)(200.0)0.0 Fiscal YearAnnual Cash Flow (1,000s)Current State Cumulative CF20

34、09201020112012(3000.0)(2500.0)(2000.0)(1500.0)(1000.0)(500.0)0.0 Fiscal YearCumulative Cash Flow ($1,000s)Proposal and Current State CF2009201020112012(1500.0)(1000.0)(500.0)0.0 500.0 1000.0 1500.0 2000.0 Fiscal YearAnnual Cash Flow (1,000s)Proposal CFCurrent St CFProposal, Alt Proposal, & Curre

35、nt State CF2009201020112012(1500.0)(1000.0)(500.0)0.0 500.0 1000.0 1500.0 2000.0 Fiscal YearAnnual Cash Flow (1,000s)Proposal CFAlt Proposal CFCurrent St CFCurrent State Cash Flow2009201020112012(1200.0)(1000.0)(800.0)(600.0)(400.0)(200.0)0.0 Fiscal YearAnnual Cash Flow (1,000s)Current State Cumulat

36、ive CF2009201020112012(3000.0)(2500.0)(2000.0)(1500.0)(1000.0)(500.0)0.0 Fiscal YearCumulative Cash Flow ($1,000s)DATA FOR GRAPHINGProposal ScenarioFiscal YearProposal CFFiscal YearProp Cumul CFAlternative Proposal ScenarioFiscal YearAlt Proposal CFFiscal YearAlt Prop Cumul CFCurrent State ScenarioF

37、iscal YearAnnual Cash FlowFiscal YearCurr St Cumul CFProposal Cumulative Cash Flow20092010201120120.0 500.0 1000.0 1500.0 2000.0 2500.0 3000.0 3500.0 4000.0 4500.0 Fiscal YearCumulative Cash Flow ($1,000s)Alternate Proposal Cumulative CF2009201020112012(500.0)0.0 500.0 1000.0 1500.0 2000.0 Fiscal Ye

38、arCumulative Cash Flow ($1,000s)Current State Cumulative CF2009201020112012(3000.0)(2500.0)(2000.0)(1500.0)(1000.0)(500.0)0.0 Fiscal YearCumulative Cash Flow ($1,000s)Proposal and Current State Cash FlowFiscal YearProposal CFCurrent State CFFiscal YearProp Cumul CFCurr St Cumul CFProposal, Alt Propo

39、sal, & Current State Cash FlowFiscal YearProposal CFAlt Proposal CFCurrent State CFFiscal YearProp Cumul CFAlt Prop Cumul CFCurr St Cumul CFProposal and Current State Cumulative CF2009201020112012(3000.0)(2000.0)(1000.0)0.0 1000.0 2000.0 3000.0 4000.0 5000.0 Fiscal YearCumulative Cash Flow ($1,0

40、00s)Prop Cumul CFCurr State Cumul CFProposal, Alt Proposal, aand Current State Cumulative CF2009201020112012(3000.0)(2000.0)(1000.0)0.0 1000.0 2000.0 3000.0 4000.0 5000.0 Fiscal YearCumulative Cash Flow ($1,000s)Prop Cumul CFAlt Prop Cumul CFCurr State Cumul CFCurrent State Cumulative CF200920102011

41、2012(3000.0)(2500.0)(2000.0)(1500.0)(1000.0)(500.0)0.0 Fiscal YearCumulative Cash Flow ($1,000s)2009201020112012254.01069.01119.01464.02009201020112012254.01323.02442.03906.02009201020112012(198.4)294.0597.0891.02009201020112012(198.4)95.6692.61583.62009201020112012(1019.0)(693.0)(437.0)(343.0)20092

42、01020112012(1019.0)(1712.0)(2149.0)(2492.0)DATA FOR GRAPHINGProposal ScenarioAlternative Proposal ScenarioCurrent State Scenario2009201020112012254.01069.01119.01464.0(1019.0)(693.0)(437.0)(343.0)2009201020112012254.01323.02442.03906.0(1019.0)(1712.0)(2149.0)(2492.0)2009201020112012254.01069.01119.0

43、1464.0(198.4)294.0597.0891.0(1019.0)(693.0)(437.0)(343.0)2009201020112012254.01323.02442.03906.0(198.4)95.6692.61583.6(1019.0)(1712.0)(2149.0)(2492.0)Proposal and Current State Cash FlowProposal, Alt Proposal, & Current State Cash FlowTOTAL COST OF OWNERSHIP ANALYSISComprehensive Cost Model for

44、All Scenarios:Project andImplementationOperations, Maintenance,Ongoing SupportPersonalServicesSalaries& Benefits State Perm Staff State Temp Staff State LD StaffServices& Supplies& CapitalOutlayStateData Center Consulting Services Hosting Storage NetworkSoftware Software purchase / Upgra

45、des Software license maintenanceHardware Hardware purchase / Upgrades Hardware ongoing maintIT ProfessionalServices Project Devel/Implementation Project Devel/ImplementationPROPOSAL SCENARIO COSTSPositive numbers are savingsTotals include FY 2009 through FY 2012Negative numbers in () are net costs$

46、in 1,000sProject andImplementationOperations, Maintenance,Ongoing SupportPersonalServicesSalaries& Benefits:($1,245.0)Services& Supplies& CapitalOutlayStateData Center:($933.0)Software($150.0)($50.0)Hardware($200.0)($120.0)IT ProfessionalServices($80.0)($940.0)Total($430.0)($3,288.0)%11.

47、6%88.4%ALTERNATE PROPOSAL SCENARIO COSTSPositive numbers are savingsTotals include FY 2009 through FY 2012Negative numbers in () are net costs$ in 1,000sProject andImplementationOperations, Maintenance,Ongoing SupportPersonalServicesSalaries& Benefits($1,488.0)Services& Supplies& Capital

48、OutlayStateData Center($1,001.0)Software($170.0)($70.0)Hardware($210.0)($140.0)IT ProfessionalServices($118.0)($990.0)Total($498.0)($3,689.0)%11.9%88.1%CURRENT STATE (BASELINE) SCENARIO COSTSPositive numbers are savingsTotals include FY 2009 through FY 2012Negative numbers in () are net costs$ in 1,

49、000sProject andImplementationOperations, Maintenance,Ongoing SupportPersonalServicesSalaries& Benefits($1,670.0)Services& Supplies& CapitalOutlayStateData Center($1,001.0)Software($184.0)($66.0)Hardware$270.0($160.0)IT ProfessionalServices($120.0)($1,021.0)Total($34.0)($3,918.0)%0.01.0INCREMENTAL PROPOSAL COSTSPositive numbers are savingsTotals include FY 2009 through FY 2012Negative numb