Link for excel file: http://www.mediafire.com/file/z0qx3hy09ovoqxu/file
I hope you can download the excel file and complete it and send it back to chrisjericho982
gmail com
The following are the instructions for completing the file
A company is assessing an opportunity to grow sales by implementing a robust customer relationship management (CRM) software tool that will improve their interaction with customers. See the attached file for the information you will need to complete the case study. Should the Company invest in this tool? Why or why not?
1. In order to determine the effect of implementing the CRM project you will need to isolate the incremental cash flows. This can be done by preparing a baseline projection for the time period in question. On the "Baseline Projection" sheet you will need to continue the historical income statement forward another five years. Using the model built for FYs 2015-2018 determine the appropriate projection values for each of the line items. For some items you will need to determine historical growth rates or ratio trends, for others you will simply need to continue the accounting formulas. By looking at the data and formulas in each of the cells and lines you will be able to work through these. As this is the baseline you will have no data in the "Incremental..." lines.
2. Now you must determine the effect of implementing the CRM project. Using your projections from the "Baseline Projection" you will now have to account for the incremental impact of the project. Consult the "Data and Assumptions" sheet and use that information to build out your "CRM Investment" sheet.
3. With full projections for the Baseline Projection and CRM Investment completed, you can now isolate the incremental changes in cash flow per year. Fill this information in on the "Calculation" sheet. You will then need to take that data and complete both an NPV and IRR analysis. The results of each of these analyses should influence your recommendation to pursue the project.
4. Submit a word document with a short report summarizing your process and recommendations. While there is no standard format you should follow use your own judgement on making this appear like a professional memo you would be sending to a boss or decision maker. Assume that this report is the first time they're hearing about the possible project.
Sign in X File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do Share AutoSum . Calibri 11 AA 20 Wrap Text General EX AY O Paste BIU- 3. A Merge & Center . $ - % " Conditional Format as Cell Insert Delete Format Fill - Formatting Table Styles Clear Sort & Find & Filter . Select Clipboard Font Alignment Number Styles Cells Editing D15 X V E C D Business Case Supporting Data Usefule life of the CRM software 5 Years Ancticipated incremental sales lift during the first year 2.00% Based on previous pilots, this could range from 0.5% to 3.0% CRM Tool Investment (all upfront - 80% capital investment and 20% operating expense] 0 In millions Variable cost rate 15.0% Note: this reflects the percentage that costs increase with incremental sales. For example, if an initiative drives $10M of incremental sales, $1.5M of variable costs will be incurred (on top of any project related costs). 7 Incremental corporate headcount required for software maintenance 2 Average salary and benefits for a Sr. Systems development resource 0.15 In millions Company's weighted average cost of capital 12.0% 10 Company's marginal tax rate 22.5% 11 Inventory turns are not expected to change going forward NA 12 Accounts receivable days are not expected change going forward NA 13 Accounts payable days are not expected to change going forward NA 17 18 19 Data and Assumptions Baseline Projection CRM Investment Calculation + Ready # + 709Sign in X File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do Share Nirmala UI - 10 AA 2p Wrap Text Custom EX AutoSum . Fill - AY O Paste BIU- 3. A Merge & Center . $ - % " Conditional Format as Cell Insert Delete Format Formatting Table Styles Clear Sort & Find & Filter . Select Clipboard Font Alignment Number Styles Cells Editing G7 X V fc A B C D E F G H J K L M N 0 P Company Financial Statement History and Projections ($ millions] Historical Income Statement Projected FY2015 FY2016 FY2017 FY2018 FY2019 FY2020 FY2021 FY2022 FY2023 Sales 8,500 8,755 9,324 9,930 Total Sales 8,500 8,755 9,324 9,930 Cost of Goods Sold 5,100) (5,428 15,688 (6,057) Total Costs (5,100] (5,428] (5.688) (6,057) Gross Profit 3,400 3,32 3.63 3.873 SG&A (2.550) (2.539 (2.611) (2.780) horamante/ SCCA Total SG$4 (2.550) (2.539) (2.611) (2.780] Depreciation & Amortization (425) (438) (466) (497) Total Depreciation & Amortization (425) (438) (466) (497) EBIT 425 350 559 596 Interest Expense (25) (25 (25) Earnings before Taxes (EBT) 400 (25) 325 534 571 Takes (110) (89) (147) (157) Net Income 290 236 387 414 Working Capital Projected FY2015 FY2016 FY2017 FY2018 FY2019 FY2020 FY2021 FY2022 FY2023 Inventory 838 892 935 93 Accounts Receivable 699 720 766 766 Accounts Payable 629 669 701 701 31 32 33 36 37 Data and Assumptions Baseline Projection CRM Investment Calculation + Ready + 75%Sign in X File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do Share Nirmala UI - 10 AA 20 Wrap Text Custom EX AutoSum Fill - AY O Paste BIU- 3. A Merge & Center $ - % " Conditional Format as Cell Insert Delete Format Formatting * Table Styles Clear Sort & Find & Filter . Select Clipboard Font Alignment Number Styles Cells Editing G7 X V B C D E F G H J K L M N 0 P Q Company Financial Statement History and Projections ($ millions] Historical Income Statement Projected FY2015 FY2016 FY2017 FY2018 FY2019 FY2020 FY2021 FY2022 FY2023 Sales 8,500 8,755 9.324 9,930 Total Sales 8,500 8,755 3,324 9,930 Cost of Goods Solo 15,100 15,428) (5,688) (6,057) Total Costs (5,100) (5,428) (5.688) (6,057) Gross Profit 3,400 3,327 3,63 3,873 SG&A (2.550 2,539 (2,611) (2,780) Total 5GSA (2.550) (2.539) (2.611) (2.780) Depreciation & Amortization (425) (438) (466) (497) Incremental depreciation Total Depreciation & Amortization (425) (438) (466) (497) EBIT 425 350 559 596 Interest Expense (25) (25) (25) (25) Earnings before Taxes (EBT) 400 325 534 571 Taxes (110) (89) (147)" (157) Net Income 290 236 387 414 25 26 Working Capital Projected 27 FY2015 FY2016 FY2017 FY2018 FY2019 FY2020 FY2021 FY2022 FY2023 28 838 892 935 935 29 Inventory Accounts Receivable 699 720 766 766 30 Accounts Payable 629 669 701 701 31 32 33 35 36 37 Data and Assumptions Baseline Projection CRM Investment Calculation + Ready + 729%Sign in X File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do Share 11 AA 20 Wrap Text Number EX AutoSum . Calibri Fill - AY O Paste BIU- 3. A Merge & Center - $ - % " Conditional Format as Cell Insert Delete Format Formatting * Table Styles Clear Sort & Find & Filter . Select Clipboard Font Alignment Number Styles Cells Editing X V A B C D E F G H K L M N O P Q R S W N - FY2019 FY2020 FY2021 F FY2022 FY2023 CRM Investment - Net Income Baseline Projection - Net Income 40 00 Change in Net Income Project Analysis 10 Period Cash Flow WACC 11 0 12 1 NPV 13 2 IRR 14 3 15 16 17 18 NPV - Pursue Project? 19 IRR - Pursue Project? 20 21 22 23 24 Data and Assumptions Baseline Projection CRM Investment Calculation + + 1009% Ready
