Question

000 AutoSave OFF S u - Sensitivity Analysis View Share Comments conditional Formatting Home Insert AvX Paste 5 | A40 x v A B Page Layout Formulas Data Review % Format as Table Alignment Number Cell Styles Ov Draw A Font fx Cells Editing Ideas Sensitivity C D E F H I J K L M N Sensitivity Analysis Summary ($ millions, except per share values) Sales 1,496.2 Year 5 Cash Assets 482.7 $ 522.3 Equity 603.7 Base Scenario $ Net Inc $ 84.7 88.5 $ $ $ EBIT 105.8 110.3 107.3 120.8 107.3 1,530.6 1,548.2 1,496.2 1,496.2 497.6 454.0 537.1 499.3 Net Marg 5.66% 5.78% 5.52% 6.53% 5.76% 537.9 494.6 576.7 538.9 522.4 526.0 19.7 613.7 603.6 658.1 607.4 ROA 7.75% 7.99% 7.77% 8.51% 7.86% 85.5 R OE 14.03% 14.42% 14.17% 14.84% 14.18% Value per Share $ 100.88 104.57 98.41 111.19 103.77 11 a. Sales Growth Rate 12 b. 2 Additional Stores 13 c. Labor & Occupancy Exp 14 d. Capital Expenditures 16 e. Inventory Turns 17 f. Tax Rates 18 g. Interest Rates 20 h. Cost of Capital 21 i. Residual Growth Rate 1,496.2 1,496.2 1,496.2 105.8 105.8 105.8 105.8 105.8 483.0 486.4 480. 1 482.7 482.7 603.7 607.4 601.1 5.66% 5.73% 5.72% 7.75% 7.82% 7.85% 14.03% 14.12% 14.24% 100.87 101.74 100.88 85.6 84.7 5 1,496.2 1,496.2 522.3 522.3 603.7 603.7 5.66% 5.66% 7.75% 7.75% 14.03% 14.03% 83.27 122.40 Change from Base Scenario Net Incl $ 84.7 $ $ Sales 1,496.2 $ 34.4 52.0 EBIT 105.8 4.5 1.5 3.8 Year 5 Cash Assets 482.7 $ 522.3 $ 14.9 15.6 15.6 (28.7) (27.7) 54.4 54.4 16.6 16.6 0.8 13.0 1.5 Value per Equity Net Marg| ROA | ROE | Share 603.7 5.66% 7.75% 14.03% $ 100.88 10.0 0.12% 0.24% 0.39% 3.69 (0.1) -0.14% 0.02% 0.14% (2.47) 54.4 0.87% 0.76% 0.81% 10.31 0.10% 0.11% 0.15% 2.89 0.00% 0.00% 0.00% (0.01) 0.07% 0.07% 0.09% 0.86 (2.6) 0.06% 0.10% 0.21% 0.00% 0.00% 0.00% (17.61) 0.00% 0.00% 0.00% 21.52 Base Scenario 29 a. Sales Growth 30 b. 2 Additional Stores 31 c. Expenses 32 d. Capital Expenditures 34 f. Inventory Turns 35 g. Tax Rates 36 h. Interest Rates 38 i. Cost of Capital 39 j. Residual Growth Rate 3.7 0.3 1.1 3.7 3.7 3.7 (2.6) (2.6) Instructions Summary Page + Circular References @ - - + 100%
Analyze the results of the sensitivity analysis done for a company by doing certain changes to the input variables (rows in "Summary Page" worksheet) based on information provided in "Instructions" worksheet and that had effect on the target variables (columns in "Summary Page" worksheet) - what do the numbers signify?

Answer 1

The image of the sensitivity analysis done and as shown in the question and the instructions given for doing the sensitivity analysis are copied in Excel spreadsheet for easier reference. Same is interpreted for change of each variable in a text box. This text box is contains interpretations in red letters with respect to each change as instructed. Based on interpretations each change, overall interpretation is done in another box below all three boxes.In short, the picture below contains four boxes - 1/ Sensitivity Analysis as done in the question; 2/ Instructions given in the question to do the Sensitivity Analysis; 3/ Red box containing interpretation for each change in parameters considered and 4/ Black box containing overall interpretation based on interpretations in 3rd box in red.

B C D E F G H I J K L Sensitivity Analysis Summary ($ millions, except per share values) Sales 1,496.2 Net Marg 5.66% ROA 7.75% $ S $ $ Net Inc 84.7 88.5 85.5 Base Scenario 11 a. Sales Growth Rate 12 b. 2 Additional Stores 13 c. Labor & Occupancy Exp 14 d. Capital Expenditures 16 e. Inventory Turns 17 f. Tax Rates 18 g. Interest Rates 20 h. Cost of Capital 21 i. Residual Growth Rate EBIT 105.8 110.3 107.3 120.8 107.3 105.8 105.8 105.8 105 105.8 1.530.6 1,548.2 1,496.2 1.496.2 1,496.2 1,496.2 1,496.2 1,496.2 1,496.2 Year 5 Cash Assets 482.7 $ 522.3 497.6 537.9 454.0 494.6 537.1 576.7 499.3 538.9 483.0 522.4 486.4 526.0 480.1 519.7 482.7 522.3 482.7 522.3 5.78% 5.52% 6.53% 5.76% Equity 603.7 613.7 603.6 658.1 607.4 603.7 607.4 601.1 603.7 603.7 7.99% 7.77% 8.51% 7.86% 7.75% 7.82% 7.85% 7.75% 7.75% Value per ROE Share 14.03% $ 100.88 14.42% 14.42% 104.57 14.17% 98.41 14.84% 111.19 14.18% 103.77 14.03% 100.87 14.12% 101.74 14.24% 100.88 14.03% 83.27 14.03% 122.40 5.66% 5.73% 5.72% 0 0 84.7 84.7 5.66% 5.66% Change from Base Scenario EBIT $ $ Sales 1,496.2 34.4 52.0 $ 4.5 1.5 15.0 Base Scenario 29 a. Sales Growth 30 b. 2 Additional Stores 31 c. Expenses 32 d. Capital Expenditures 34 f. Inventory Turns 35 g. Tax Rates 36 h. Interest Rates Net Inc 84.7 3.8 0.8 13.0 Year 5 Cash Assets 482.7 $ 522.3 14.9 15.6 (28.7) (27.7) 54.4 54.4 16.6 Equity 603.7 10.0 (0.1) 54.4 3.7 1.5 16.6 Net Marg 5.66% 0.12% -0.14% 0.87% 0.10% 0.00% 0.07% 0.06% 0.00% 0.00% ROAT 7.75% 0.24% 0.02% 0.76% 0.11% 0.00% 0.07% 0.10% 0.00% 0.00% Value per ROE I Share 14.03% $ 100.88 0.39% 3.69 0.14% (2.47) 0.81% 10.31 0.15% 2.89 0.00% (0.01) 0.09% 0.86 0.21% 0.00% (17.61) 0.00% 21.52 3.7 (2.6) 38 i. Cost of Capital 39 j. Residual Growth Rate 40 Sensitivity Analysis 1. After completing Parts A and B, use the base case to complete the following sensitivity analysis: a. Increase your same store sales growth rate by 1% (or 100 basis points) each year. Therefore, if your growth rates were 2% the first 2 years and 3% thereafter, the revised growth rates would be 3% and 4%, respectively. b. Increase the number of new store openings by 2 each year. So if you were opening 10 new stores for the first 2 years then 11 for the next 2 years, etc., you now would be opening 12 new stores new stores the first 2 years, 13 new stores the next 2 years, etc. c. Reduce your expenses (Labor and Occupancy Expense) by 1.0% of sales (or 100 basis points) each year. Therefore, if your Labor and Occupancy Expense as a percent of sales were 58.0% the first 3 years and 57.5% thereafter, the revised Labor and Occupancy Expense as a percent of sales would be 57.0% and 56.5%, respectively. d. Reduce refurbishing capital expenditures by 10% each year. c. Increase inventory turnover by one time each year. 1. Decrease tax rates by 1% (or 100 basis points) each year. Therefore if your tax rate were 21% the first 3 years and 20.5% thereafter, the revised tax rates would be 20% and 19.5%, respectively 8. Increase interest rates (borrowing and saving) by 1% (or 100 basis points) each year. h. Increase the cost of capital by 1% (or 100 basis points) i. Increase the residual growth rate by 1% (or 100 basis points). substitutetice...BRUTDA WEB elet t rotec t INTERPRETATION OF RESULTS OF SENSITIVITY ANALYSIS DONE ABOVE BASED ON INSTRUCTIONS IN ADJACENT BOX a. Increase in sales growth by 1% each year results in positive incremental growth in all parameters considered (ie, sales, EBIT, Net Income, Cash, Assets, Equity, Net Margin, ROA, ROE & Value per share). Ultimately, the value per share gets a positive boost by about 3.66% (i.e.3.69 / 100.88). b. Increase in number of stores by 2 each year results positive growth in sales, EBIT, Net Income, ROA and ROE yet the same is offset by negative impact on cash, assets, equity and net margin. Consequently, the value of share decreases by 2.45% c. Reduction in lab and occupancy expenses by 1% shows real good impact on all parameters except sales and bumper improvements in all margins and returns. This gets reflected in growth of 10.22% in value of shares. d. Reduction in refurbishing capital expenditure by 10% each year shows similar effect like scenario in cabove on a lesser grander scale and results in adding 2.87% to value of shares. e. Increase in inventory turnover by one time does not cause much of an effect on parameters considered and need not be considered. f. Decrease in tax rates by 1% each year results in in Improvement in cash, assets, equity, net margin, ROA and ROE and about 0.85% increase in value of shares. g. Increase in interest rates (borrowing and saving) by 1% results in improvement net income, margins, ROA and ROE which gets offset by reduction in cash, assets and equity and, consequently, this value of shares remain constant. h. Increase in cost of capital by 1% does not effect any of the parameters within company's control, but effects the value of shares negatively very badly with value of shares going down sharply by 17.5% almost. I. Increase in residual growth rate by 1%, just like increase in cost of capital, does not effect in any of the parameters under consideration except excellent positive impact on value of shares boosting the same by 21.33%. This means that the residual growth factor is some external factor such as positive government policy, monetary action of Feds, etc. Fach item is separate from the one before it. Please return to the base case after every sensitivity scenario! OVERALL INTERPRETATION BASED ON INTERPRETATION OF POINT WISE RESULTS OF SENSITIVITY ANALYSIS DONE ABOVE: Out of all parameters put to sensitivity test, value of shares is beyond full control of company and it is also governed by lot of external factors. When company does take positive steps and manages to implement them successfully, it results in positive impact on value of shares i.e. Shareholders' value). When some negative steps are taken or positive steps are not managed correctly, it results in overall negative impact & reduction in value of shares. For example, increase in sales growth at no change in costs and if accomplished with good reduction in costs results in bumper value addition to the Shareholders' value. Certain factors such as Govt policy, Fed action, tax rate determination, interest rates borrowing and saving are beyond the overall control of a company. Therefore, the company need to be in control of parameters within its grasp and manage the resources required for them efficiently. This ensures steady (if not bumper) overall value addition and promotes a stable image of the company.