Hey, Could you please help me for this report. I did the excel model and I need to use excel model to analysis and write report. But, I have no idea how to write it. Thanks for you helping

Dell Computers Case FSA Modeling-Demo for 1997 Assumptions Sales Growth Tax rate Assumed kd Cost of Sales Cash/Sales AR/Sales Inventory/Sales Other CA/Sales PPE/Sales AP/Sales Accrual/Sales Short Term Inv OP Expenses/Sales Retention Ratio Share Buyback Other FA/Sales Other LT Liab/Sales Other SE/Sales Debt Reduction Percent Sales Analysis Cost of Sales OP Expenses/Sales Cash/Sales Short term Investment Accounts Receivable Inventory/Sales Other CurrentAssets/Sales PPE/Sales Other Fixed Assets/Sales Accounts Payable/Sales Accruals/Sales Other Liabilities/Sales Other Shareholder Equity/Sales Values 50% 30% 4% 78.85% 1.04% 12.71% 8.10% 2.95% 3.38% 7.80% 8.93% As Is 13.03% $ $ 100% 500.00 0.23% 2.32% -0.62% 113.00 79.85% 13.03% 1.04% As Is 13.71% 8.10% 2.95% 3.38% 0.23% 8.80% 8.93% 2.32% -0.62% Income Statement Historical Fiscal Year 1992 1993 Sales $890 $2,014 Cost of Sales $608 $1,565 Gross Margin $282 $449 Operating Expenses $215 $310 Operating Income $67 $139 Financing & Other Income $7 $4 Income Taxes $23 $41 Net Profit $51 $102 Balance Sheet for Dell Computer Corporation (millions of dollars) Surplus Cash Short Term Investments Accounts Receivables, net Inventories Other Total Current Assets Property, Plant & Equipment, net Other Total Assets Current Liabilities: Accounts Payable Accrued and Other Liabilities Total Current Liabilities Long Term Debt New Long_Term Debt (Plug) Other Liabilities Total Liabilities Stockholders' Equity: Preferred Stock (Note a) Common Stock (Note a) Retained Earnings Results Profit Margin AFN AFN* (Debt Retire +Buyback) CCC ROTC FCF Fixed Asset Turnover $ $ $ Other Total Stockholders' Equity 1997 Total Liab +Shareholder Equity 5.87% (218.98) 394.02 39.85 66.90% 226.56 29.59 $ Impact of Changes in Assumptions Regarding Gro (219) 78.76% 79.26% 79.85% Err:522 Err:522 Err:522 Err:522 51% -222 -194 -161 52% -220 -192 -159 53% -218 -190 -156 54% -216 -188 -154 55% -214 -186 -152 Effect of Improvement in AR/AP on Cash Conver AP AR 39.85 13.71% 12.71% 11.71% 10.71% 9.70% 8.70% AR $ 8.80% 38.87 35.22 31.57 27.92 24.24 20.59 9.80% 34.24 30.59 26.94 23.29 19.61 15.96 10.80% 29.61 25.96 22.31 18.66 14.98 11.33 Effect of Improvement in AR/AP on Ability to Repay Debt and R AP 394.02 8.80% 9.80% 10.80% 13.71% $ 394 $ 315 $ 235 12.71% $ 315 $ 235 $ 156 11.71% 10.71% 9.70% 8.70% $ $ $ $ 235 156 75 (4) $ $ $ $ 156 76 (4) (83) $ $ $ $ 76 (3) (83) (163) Impact of Improving Profitability on Ability of Dell to Repay Debt and Repur NPM AFN* CCC 5.87% $ 394.02 39.85 79.00% 5.77% $ 402 38.95 78.00% 6.47% $ 347 38.43 77.00% 7.17% $ 291 37.89 76.00% 7.87% $ 236 37.34 75.00% 8.57% $ 180 36.78 74.00% 9.27% $ 124 36.20 73.00% 9.97% $ 69 35.61 72.00% 10.67% $ 13 35.00 71.00% 11.37% $ (43) 34.37 70.00% 12.07% $ (98) 33.72 Effect of Asset Efficiency (AR/Inv) on Ability of Dell to Repay Debt and Repu $ 394.02 8.10% 7.10% 6.10% 5.10% 4.10% $ $ $ $ $ 13.71% 473 394 315 235 156 $ $ $ $ $ 12.71% 394 315 235 156 76 $ $ $ $ $ 11.71% 315 235 156 76 (3) 1994 $2,873 $2,440 $433 $472 -$39 $0 -$3 -$36 1994 1995 $3,475 $2,737 $738 $489 $249 -$36 $64 $149 $ $ $ $ $ $ $ $ 1996 1997 Projected 5,296 $ 7,944 4,229 $ 6,264 1,067 $ 1,680 690 $ 1,035 377 $ 645 6.00 $ (21.32) 111 $ 200 272 $ 467 1995 1996 1997 219 83 591 1,010 644 234 2,561 269 18 3,066 55 334 411 220 80 1,048 87 5 1,140 43 484 538 293 112 1,470 117 7 1,594 55 591 726 429 156 1957 179 12 2148 $ $ $ $ $ $ $ $ $ 0 0 538 100 403 349 752 113 466 473 939 113 $ $ $ $ 31 669 77 942 123 1175 $ 620 710 1,329 113 0 185 1,627 0 0 0 120 242 311 6 430 570 6 430 1,037 0 471 1,140 (21) 652 1,594 (33) 973 2148 $ (33) 1,440 3,066 $ $ $ $ $ 2,847 3,066 (219) 2,453 394 AFN Projected assets Projected Liab AFN Liab*Adj AFN*Adj owth and Cost of Sales on AFN (Goal 1) 80.26% 80.76% 81.26% Err:522 Err:522 Err:522 -138 -110 -82 -136 -107 -79 -133 -105 -76 -131 -102 -74 -128 -100 -71 81.76% Err:522 -54 -51 -48 -45 -42 82% Err:522 -41 -38 -34 -31 -28 ersion Cycle 11.81% 24.94 21.29 17.64 13.99 10.30 6.65 12.81% 20.31 16.66 13.01 9.36 5.67 2.02 Repurchase $500m Equity P 11.81% 12.81% $ 155 $ 75 $ $ 75 $ (4) $ 13.81% 15.68 12.03 8.38 4.73 1.04 -2.61 Sensitivity of Cash Conversion to Improvements in A AR 14.81% (83) (163) AP 39.8516 15.40% 14.40% 13.40% 12.40% 11.60% 32.08 28.43 24.78 21.13 12.60% 27.45 23.80 20.15 16.50 13.60% 22.82 19.17 15.52 11.87 $ $ $ $ (4) (83) (164) (243) $ $ $ $ (83) (163) (243) (323) urchase $500 m Equity ROTC FCF 66.90% $ 226.56 66% $218.27 74% $273.88 82% $329.48 90% $385.09 99% $440.70 107% $496.31 115% $551.92 123% $607.52 132% $663.13 140% $718.74 purchase $500m Equity $ $ $ $ $ 10.70% 234 155 75 (4) (83) $ $ $ $ $ 9.70% 155 75 (4) (83) (163) $ $ $ $ (242) (322) (402) (481) 11.40% 10.40% 9.40% 8.40% 7.40% $ 394.02 15.40% 14.40% 13.40% 12.40% 11.40% 10.40% 9.40% 8.40% 7.40% 17.48 13.83 10.18 6.53 2.88 $ $ $ $ $ $ $ $ $ 12.85 9.20 5.55 1.90 -1.75 8.22 4.57 0.92 -2.73 -6.38 Sensitivity of AFN to improvements in AR/AP 11.60% 12.60% 13.60% 306 $ 226 $ 147 226 $ 147 $ 68 147 $ 68 $ (12) 68 $ (12) $ (91) (12) $ (91) $ (171) (91) $ (171) $ (250) (171) $ (250) $ (330) (250) $ (330) $ (409) (330) $ (409) $ (489) Sensitivity of NPM, CCC, FCF and Improvements in Cost of Sales NPM CCC FCF 5.87% 39.85 $ 226.56 60.00% 19.07% 28.51 $ 1,274.82 65.00% 15.57% 32.16 $ 996.78 70.00% 12.07% 35.29 $ 718.74 75.00% 8.57% 38.00 $ 440.70 80.00% 5.07% 40.37 $ 162.66 ements in AR/AP 14.60% 18.19 14.54 10.89 7.24 15.60% 13.56 9.91 6.26 2.61 16.60% 8.93 5.28 1.63 -2.02 3.59 -0.06 -3.71 -7.36 -11.01 -1.04 -4.69 -8.34 -11.99 -15.64 -5.67 -9.32 -12.97 -16.62 -20.27 s in AR/AP 14.60% $ 68 $ (12) $ (91) $ (171) $ (250) $ (330) $ (409) $ (489) $ (568) 15.60% (12) (91) (171) (250) (330) (409) (489) (568) (647) 16.60% (91) (171) (250) (330) (409) (489) (568) (647) (727) of Sales AFN* $ 394.02 $ (654.24) $ (376.20) $ (98.16) $ 179.88 $ 457.92 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Scenario Summary Current Values: Good Better Changing Cells: CostOfSales 79.85% 78.85% 77.85% AR 13.71% 12.71% 11.71% AP 8.80% 7.80% 6.80% Result Cells: AFN $ (163) $ (219) $ (275) $ AFNstar $ 450 $ 394 $ 338 $ ProfitMargin 5.17% 5.87% 6.57% Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray. Best 75.00% 11.71% 5.80% (354) 259 8.57% Dell Case Questions 1. Conduct a historical analysis on Dell and identify its key strengths and weaknesses. 2. Assume Dell sales will grow at 50% in 1997. Estimate Dell's pro-forma balance sheet and income statement for 1997 and determine the AFN needed. Assume that any additional external financing will be obtained via long-term debt at 4%, while surplus funds will be parked in short-term investments at 4% and the tax rate is 30%. Will Dell be able to fund its 50% growth in 1997 internally? 3. Based on your forecasting model for 1997, will Dell be able to retire its debt, buyback $500 million in shares while at the same time be able to grow at 50%? If not, estimate the extent of new funding required? 4. Identify potential areas of improvements Dell should focus on if it wants to be in a position to finance its target growth of 50% for 1997 while at the same time being able to retire its debt and buyback $500 million of shares. Specifically, identify the following? a. Profit margin needed for all three goals as described above to be accomplished b. Cash Conversion cycle at which all three goals are accomplished c. Fixed asset turnover at which all three goals will be accomplished d. Evaluate scenarios with best and worst case values for profit margin, cash conversion cycle, and fixed asset turnover and their impact on the likelihood of accomplishing the three goals