Create a Simple Cash Flow Method Cost-Benefit Analysis utilizing Microsoft Excel. Before you work on this activity, review the 1.2 Readings and Resources page and refer to the Cost-Benefit Analysis example in Figure 1-12 in the textbook. You must utilize this Cost-Benefit Analysis Template (XLSX) Download Cost-Benefit Analysis Template (XLSX) to create a Cost-Benefit Analysis. Calculate the missing values and present the data in the yellow-highlighted spreadsheet cells. Leave the yellow highlights in the cells. Do NOT utilize a calculator to calculate the missing data. You must utilize an Excel formula to calculate the missing data. For example, if you desire to calculate the data in cell E4, you could use the following or another Excel formula: =D4+C4*D4 Your instructor will select the cells to validate that you utilized formulas to calculate the missing data. For the Cost-Benefit Analysis: Submit data for a 6-year timeframe (i.e., 2023-2028). Do NOT add any data in cells B4, B5, B6, or B7. Be sure to present the aggregate Column I Total data for all applicable rows, except for Rows 29 and 31. Do NOT present any decimal places in your data except for the ROI and BEP data.

Transcribed Image Text:

Simple Cash Flow Method Cost-Benefit Analysis Benefits Increased hours of data entry and order fulfillment Increased inventory balance and decreased stock-outs Increased inventory turnover ratio Increased warehouse organization Total Benefits 2023 Yearly Increase Factor 2024 2025 2026 2027 2028 Total 5% 21,000 22,050 23,153 24,310 25,526 116,038 2% 2% 5% 23,000 23,460 23,929 24,408 15,000 15,300 15,606 67,000 70,350 73,868 126,000 131,160 136,555 142,197 24,896 119,693 15,918 16,236 78,061 77,561 81,439 370,217 148,097 684,009 Development Costs Dell Poweredge rack-mounted server 4,900 Windows Surface Pro (5) (warehouse) 9,500 Windows SQL Server 2017 software (10 CALS) 2,800 Development team salaries/benefits 243,000 Total Development Costs 260,200 Operational Costs Hardware (utilities/maintenance) Software (security/malware protection) Operational labor (tech support/ system maintenance) Total Operational Costs Total Costs Total Benefits - Total Costs Cumulative Net Cash Flow Return On Investment Break-Even Point 2% 21,500 33 2% 380 NA 2% 13,000 34,880 21,930 388 13,260 35,578 22,369 395 13,525 13,796 36,289 37,015 22,816 23,272 90,387 403 411 1,598 14,072 54,653 37,755 146,637 260,200 0 34,880 35,578 36,289 37,015 37,755 146,637 (260,200) 0 160,880 166,738 172,844 179,212 185,852 830,646 YEARS Simple Cash Flow Method Cost-Benefit Analysis Benefits Increased hours of data entry and order fulfillment Increased inventory balance and decreased stock-outs Increased inventory turnover ratio Increased warehouse organization Total Benefits 2023 Yearly Increase Factor 2024 2025 2026 2027 2028 Total 5% 21,000 22,050 23,153 24,310 25,526 116,038 2% 2% 5% 23,000 23,460 23,929 24,408 15,000 15,300 15,606 67,000 70,350 73,868 126,000 131,160 136,555 142,197 24,896 119,693 15,918 16,236 78,061 77,561 81,439 370,217 148,097 684,009 Development Costs Dell Poweredge rack-mounted server 4,900 Windows Surface Pro (5) (warehouse) 9,500 Windows SQL Server 2017 software (10 CALS) 2,800 Development team salaries/benefits 243,000 Total Development Costs 260,200 Operational Costs Hardware (utilities/maintenance) Software (security/malware protection) Operational labor (tech support/ system maintenance) Total Operational Costs Total Costs Total Benefits - Total Costs Cumulative Net Cash Flow Return On Investment Break-Even Point 2% 21,500 33 2% 380 NA 2% 13,000 34,880 21,930 388 13,260 35,578 22,369 395 13,525 13,796 36,289 37,015 22,816 23,272 90,387 403 411 1,598 14,072 54,653 37,755 146,637 260,200 0 34,880 35,578 36,289 37,015 37,755 146,637 (260,200) 0 160,880 166,738 172,844 179,212 185,852 830,646 YEARS