Perform a sensitivity analysis on four parameters: Select four parameters from the list below and explain why those parameters are important to analyze for your company. Make sure your explanation makes sense for YOUR company. Please include excel formulas

Question: Suppose you are the financial manager, if you are asked to use limited resources to refine the assumption on ONLY ONE of the above four parameters, which one should you choose and why? (fill in the blanks highlighted in yellow)

A B C D E F G H I J K I M N 0 P Copy your baseline projections to this sheet, so that your R S T U sensitivity analysis won't alter your baseline result in the NPV sheet. Sensitivity Analysis Parameter Initial Assumption Worst Case Best Case Initial Parameter Worst Case Assumption Best Case 11 Sales in Year 1 (in $30,000 thousands) $27,000 $33,000 12 NPV Sales Growth through|6% 0% Year 6 10% 13 Cost of Goods Sold 72% 77% (% of Sales) 67% 14 NPV Accounts Receivable 15% % of Next Year Sales 20% 10% 15 Inventory % of Next 20% Year COGS 25% 15% NPV Accounts Payable % 15% 10% of Next Year COGS 20% 17 Initial NWC $6,000 $7,500 $4,500 NPV Sales Growth After Question: Suppose you are the financial manager, if you are asked to use limited resources to refine the 2% 0% 6% Year 6 assumption on ONLY ONE of the above four parameters, which one should you choose and why? (fill in the blanks highlighted in yellow) 19 Free Cash Flow 2% 0% 4% growth After Year 6 20 Initial Capital $9,000 21 Expenditure $11,000 $7,000 28 Question: Why are the four assumptions you choose appropriate for your company?A B C D E F G H I J K I M N 0 P 22 Year Year Year Year Year Year Year Year 23 Unlevered Income Statements 0 2 3 5 6 7 24 Sales 30,000,000 31,800,000 33,708,000 35,730,480 37,874,309 40,146,767 40,949,703 25 y-0-y growth 6% 6% 6% 6% 6% 2% 26 COGS 21,600,000 22,896,000 24,269,760 25,725,946 27,269,502 28,905,672 29,483,786 27 S,G & A Expenses 5,000,000 5,000,000 5,000,000 5,000,000 5,000,000 5,000,000 5,000,000 28 Depreciation 1,800,000 1,800,000 1,800,000 1,800,000 1,800,000 29 EBIT 1,600,000 2,104,000 2,638,240 3,204,534 3,804,806 6,241,095 6,465,917 30 Taxes 480,000 631,200 791,472 961,360 1,141,442 1,872,328 1,939,775 31 32 33 34 Unlevered Net Income 1,120,000 1,472,800 1,846,768 2,243,174 2,663,365 4,368,766 4,526,142 35 36 Working Capital Calculations 37 Inventory 4,579,200 4,853,952 5,145,189 5,453,900 5,781,134 5,896,757 38 Accounts Receivable 4,770,000 5,056,200 5,359,572 5,681,146 6,022,015 6,142,455 39 Accounts Payable 3,434,400 3,640,464 3,858,892 4,090,425 4,335,851 4,422,568 10 NWC Level 6,000,000 5,914,800 6,269,688 6,645,869 7,044,621 7,467,299 7,616,645 41 Change in NWC 6,000,000 -85,200 354,888 376,181 398,752 422,677 149,346 -7,616,645 12 CF from Change in NWC 6,000,000 85,200 -354,888 -376,181 -398,752 -422,677 -149,346 7,616,645 43 44 Unlevered Cash Flows 15 Unlevered Net Income 1,120,000 1,472,800 1,846,768 2,243,174 2,663,365 4,368,766 4,526,142 16 Add Back: Depreciation 1,800,000 1,800,000 1,800,000 1,800,000 1,800,000 17 CF from Change in NWC 6,000,000 85,200 -354,888 -376,181 398,752 -422,677 -149,346 7,616,645 18 CF from Capital Expenditure -9,000,000 19 Free Cash Flow -15,000,000 3,005,200 2,917,912 3,270,587 3,644,422 4,040,687 4,219,420 12,142,786 50 Terminal Value (Year 5) 23,441,225 51 52 Discount Factor 0.833 0.694 0.579 0.482 0.402 0.335 53 FCF Present Value -15,000,000 2,504,333 2,026,328 1,892,701 1,757,534 1,623,862 54 NPV 4,225,259 55 in which PV(Terminal Value of FCFs in Year 6 and 56 Beyond) 57 58 59