Annualized net cash flow analysis


Case Study - Department Store, Inc Digital Supply Chain Transformation

Your Senior Manager leading the engagement has outlined the deliverables that he requires from you along with a few suggested formats that you can use in preparing your analysis. He has asked you to prepare the following.

Annualized Net Cash Flow Analysis for the years 2009 through 2018. He asked that you build this model in Excel which provides native functionality for calculating NPV. He asked that you start with Alternative 1 which will represent the majority of the work. Once you complete Alternative 1 it will be fairly straight forward to modify certain values and timing of events to reflect the specifics of the other 3 alternatives. He has asked that when you create this model that you include the following line item elements labeled by DFC.

Sales to capacity analysis for each alternative that illustrates the ratio of DFC network capacity to sales by year for 2010 through 2014. This analysis should also include views that consider two sensitivity scenarios - 1) sales grow 25% annually, and 2) sales grow 35% annually. A graphical or similar visual presentation of the analysis is preferred.

Risk assessment of each alternative in a tabular format that considers the impact of change, complexity, operational skills, potential schedule slippages, and business continuity.

Decision Matrix that provides a weighted score for each alternative based on the following weighted criteria as established by DSI leadership 

o Adequate Capacity to Enable Sales Growth (with consideration for timeline & sales volume sensitivity) -

o Operational Risk (ability to manage change & achieve targeted productivity) -

o Economic Value (total return to the business) -

Each alternative will receive a score of 1 to 3 against each of the three criteria based on how well it supports DSI objectives -

o High - 3

o Medium - 2

o Low - 1

Ultimately, each alternative will receive a total weighted score by applying the weightings to the individual criteria scores.

Based on your recommended alternative, a Change Impact Assessment that consists of - a) a list of the top 5 areas of change, b) Change Assessment Matrix that analyzes the type and severity of change for 3 impacted business functions, and c) 3 to 4 potential change strategies to address the impacted functions.

PowerPoint presentation slides that address the following storyboard flow.

o Cash flow analysis and NPV calculation for each alternative pasted into PPT from the Excel source file (Part 1)

o Sales to capacity sensitivity analysis as outlined above (Part 1)

o Risk Assessment table as outlined above (Part 2)

o Pros & Cons Assessment table as outlined above (Part 2)

o Decision Matrix table as outlined above (Part 2)

o Change Impact Assessment as outlined above (Part 2)

o Recommended alternative with supporting rationale as to why and any key actions that DSI should take in conjunction with pursuing this alternative (Part 2)

o Any tweaks that DSI should make to the recommended alternative that would further enhance the balance of economic value and business risk (Part 2)

Excel file that provides the detail back-up for your cash flow / NPV model along with your sales to capacity sensitivity analysis (Part 1)

Attachment:- DSI Case Study.rar

Request for Solution File

Ask an Expert for Answer!!
Case Study: Annualized net cash flow analysis
Reference No:- TGS03050513

Expected delivery within 24 Hours