Q2 Solve the below-mentioned question and show the excel steps and formulas to get the result in highlighted region based on part 1 shown above .

Straopramen Hand Cream Straopramen Cosmetics Company has developed a special formula for hand cream. It can go into production for an initial investment in equipment of $10 million. The equipment will be depreciated using the straight line depreciation method with a 10- year economic life. The company anticipates that the project will come to an end in 8 years when competitors catch on with new products making the special hand cream formula obsolete. At that time the firm will stop production of the hand cream altogether and it expects to refurbish and retool the equipment for alternative use. It will use the equipment's book value as the opportunity cost of the refurbishment. This can be regarded as a kind of resale, albeit an internal one. You may assume that the market value of the resale is equal to the book value of the equipment at the end of the year at t=8. Market research indicates that the special hand cream demands a market price of $5 per 2-ounce bottle if sold today. Initial sales forecast for the first year is 4 million bottles. Unit sales are expected to grow at the following rates Year Sales Growth (%) 2 10% 3 10 4 15 5 20 6 0 7 -15 8 -15 The firm estimates that costs of good are approximately 80% of revenues and are expected to be constant through the years. To undertake the project, the firm must invest in working capital that amounts to 2.5% of the next year's sales. In other words, current-year working capital investment is 2.5% of next year's sales. Working capital investment will be fully recaptured when the project comes to an end in 8 years i.e., at the end of year of year 8). The firm's tax bracket is 40 percent. The inflation rate is expected to be 3% per annum. All nominal dollar values will be affected by inflation the same way. The nominal cost of capital for the firm is 20%. The nominal cost of capital for this project is 16%. Build an Excel template to calculate the NPV and IRR for this project. Perform sensitivity analysis using Data/Table. Perform breakeven analysis using Solver and Scenario Manager. . J A B D E F G 1) Should the firm undertake this project? Build a semi-automatic spreadsheet model 6 to answer this question. 7 8 All $ figures are in $1,000 (except price) 9 Initial Investment $ 10,000 10 COG as a % of Gross Sales 0.80 11 Depreciation method SLNI 12 Economic Life 10 13 Tax rate 0.40 14 Salvage value $ 15 Resale price @t=8 $ 2,000 16 Book value @t=8 $ 2,000 17 Nominal Cost of capital 16.00% 18 Inflation 3% 19 WC as a % of Sales 2.50% 20 Firm has the following assumptions on sales volumes and price. 21 t=0 t=1 t=2 t=3 t=4 t=5 22 Anticipated Price @t=0 $ 5 23 Anticipated unit sales growth 0.0% 0.0% 10.0% 10.0% 15.0% 20.0% 24 Anticipated unit sales (Q) 4400 4840 5566 6679 25 Firm will maintain working capital (W.C.) according to the following schedule: 26 t=0 t=1 t=2 t=3 t=4 t=5 27 Level of WC $ 515 $ 583 $ 661 $ 783 $ 968 $ 997 $ 28 Change in WC $ 515 $ 68 $ 78 $ 122 $ 185 $ 29 $ 29 30 Year 0 1 2 3 4 5 31 Initial Investment 32 Gross Sales $ 20,600 $ 23,340 $ 26.444 $ 31,323 $ 38,715 $ 33 Expenses $ 16,480 $ 18,672 $ 21,155 $ 25,058 $ 30,972 $ 34 Depreciation $ 1,000 $ 1,000 $ 1,000 $ 1,000 $ 1,000 $ 35 Pretax income $ 3,120 $ 3,668 $ 4,289 $ 5,265 $ 6,743 $ 36 Taxes $ 1,248 $ 1,467 $ 1,716 $ 2,106 $ 2,697 $ 37 Net Income $ 1,872 $ 2,201 $ 2,573 $ 3,159 $ 4,046 $ t=6 t=7 t=8 4000 0.0% 6679 -15.0% 5677 -15.0% 4826 t=8 t=6 873 $ (124) $ t=7 764 $ (109) $ (764) 6 7 8 39,877 $ 31,901 $ 1,000 $ 6,975 $ 2,790 $ 4,185 $ 34,912 $ 27,930 $ 1,000 $ 5,982 $ 2,393 $ 3,589 $ 30,565 24,452 1,000 5,113 2,045 3,068 B C D E F G H 38 39 Relevant Cash flow 40 Investment 41 Change in W.C 42 Opearting Cash Flow 43 Resale 44 Relevant Cash flow 45 46 NPV $ $ $ (10,000) (515) $ $ (68) $ 2,872 $ (78) $ 3,201 $ (122) $ 3,573 $ (185) $ 4,159 $ (29) $ 5,046 $ 124 $ 5,185 $ 109 $ 4,589 $ $ 4,698 $ 764 4,068 2,000 6,832 $ (10,515) $ 2,804 $ 3,123 $ 3,451 $ 3,974 $ 5,017 $ 5,309 $ $ 6,943 31.59% 47 IRR A B D F G H J K L M N O P Q R 49 50 51 52 53 2) Perform a sensitivity analysis to analyze the impact of inflation on the project's profitability. You must use Data Table in Excel to perform this analysis. You are free to choose the range of inflation rate. 54 55 56 Inflation IRR Inflation NPV IRR $ 6,943 $ 31.59% 1.00% 5,810 $ 29.56% 1.25% 5,947 $ 29.82% 1.50% 1.75% 2.00% 2.25% 2.50% 2.75% 3.00% 3.25% 6,085 $ 6,224 $ 6,365 $ 6,507 $ 6,651 $ 6,796 $ 6,943 $ 7,091 30.07% 30.32% 30.58% 30.83% 31.08% 31.34% 31.59% 31.85% 57 58 59 60 NPV $ 1.00% $ 1.25% $ 1.50% $ 1.75% $ 2.00% $ 2.25% $ 2.50% $ 2.75% $ 3.00% $ 3.25% $ 61 62 63 64 6,943 5,810 5,947 6,085 6,224 6,365 6,507 6,651 6,796 6,943 7,091 31.59% 29.56% 29.82% 30.07% 30.32% 30.58% 30.83% 31.08% 31.34% 31.59% 31.85% 65 66 67 68 Straopramen Hand Cream Straopramen Cosmetics Company has developed a special formula for hand cream. It can go into production for an initial investment in equipment of $10 million. The equipment will be depreciated using the straight line depreciation method with a 10- year economic life. The company anticipates that the project will come to an end in 8 years when competitors catch on with new products making the special hand cream formula obsolete. At that time the firm will stop production of the hand cream altogether and it expects to refurbish and retool the equipment for alternative use. It will use the equipment's book value as the opportunity cost of the refurbishment. This can be regarded as a kind of resale, albeit an internal one. You may assume that the market value of the resale is equal to the book value of the equipment at the end of the year at t=8. Market research indicates that the special hand cream demands a market price of $5 per 2-ounce bottle if sold today. Initial sales forecast for the first year is 4 million bottles. Unit sales are expected to grow at the following rates Year Sales Growth (%) 2 10% 3 10 4 15 5 20 6 0 7 -15 8 -15 The firm estimates that costs of good are approximately 80% of revenues and are expected to be constant through the years. To undertake the project, the firm must invest in working capital that amounts to 2.5% of the next year's sales. In other words, current-year working capital investment is 2.5% of next year's sales. Working capital investment will be fully recaptured when the project comes to an end in 8 years i.e., at the end of year of year 8). The firm's tax bracket is 40 percent. The inflation rate is expected to be 3% per annum. All nominal dollar values will be affected by inflation the same way. The nominal cost of capital for the firm is 20%. The nominal cost of capital for this project is 16%. Build an Excel template to calculate the NPV and IRR for this project. Perform sensitivity analysis using Data/Table. Perform breakeven analysis using Solver and Scenario Manager. . J A B D E F G 1) Should the firm undertake this project? Build a semi-automatic spreadsheet model 6 to answer this question. 7 8 All $ figures are in $1,000 (except price) 9 Initial Investment $ 10,000 10 COG as a % of Gross Sales 0.80 11 Depreciation method SLNI 12 Economic Life 10 13 Tax rate 0.40 14 Salvage value $ 15 Resale price @t=8 $ 2,000 16 Book value @t=8 $ 2,000 17 Nominal Cost of capital 16.00% 18 Inflation 3% 19 WC as a % of Sales 2.50% 20 Firm has the following assumptions on sales volumes and price. 21 t=0 t=1 t=2 t=3 t=4 t=5 22 Anticipated Price @t=0 $ 5 23 Anticipated unit sales growth 0.0% 0.0% 10.0% 10.0% 15.0% 20.0% 24 Anticipated unit sales (Q) 4400 4840 5566 6679 25 Firm will maintain working capital (W.C.) according to the following schedule: 26 t=0 t=1 t=2 t=3 t=4 t=5 27 Level of WC $ 515 $ 583 $ 661 $ 783 $ 968 $ 997 $ 28 Change in WC $ 515 $ 68 $ 78 $ 122 $ 185 $ 29 $ 29 30 Year 0 1 2 3 4 5 31 Initial Investment 32 Gross Sales $ 20,600 $ 23,340 $ 26.444 $ 31,323 $ 38,715 $ 33 Expenses $ 16,480 $ 18,672 $ 21,155 $ 25,058 $ 30,972 $ 34 Depreciation $ 1,000 $ 1,000 $ 1,000 $ 1,000 $ 1,000 $ 35 Pretax income $ 3,120 $ 3,668 $ 4,289 $ 5,265 $ 6,743 $ 36 Taxes $ 1,248 $ 1,467 $ 1,716 $ 2,106 $ 2,697 $ 37 Net Income $ 1,872 $ 2,201 $ 2,573 $ 3,159 $ 4,046 $ t=6 t=7 t=8 4000 0.0% 6679 -15.0% 5677 -15.0% 4826 t=8 t=6 873 $ (124) $ t=7 764 $ (109) $ (764) 6 7 8 39,877 $ 31,901 $ 1,000 $ 6,975 $ 2,790 $ 4,185 $ 34,912 $ 27,930 $ 1,000 $ 5,982 $ 2,393 $ 3,589 $ 30,565 24,452 1,000 5,113 2,045 3,068 B C D E F G H 38 39 Relevant Cash flow 40 Investment 41 Change in W.C 42 Opearting Cash Flow 43 Resale 44 Relevant Cash flow 45 46 NPV $ $ $ (10,000) (515) $ $ (68) $ 2,872 $ (78) $ 3,201 $ (122) $ 3,573 $ (185) $ 4,159 $ (29) $ 5,046 $ 124 $ 5,185 $ 109 $ 4,589 $ $ 4,698 $ 764 4,068 2,000 6,832 $ (10,515) $ 2,804 $ 3,123 $ 3,451 $ 3,974 $ 5,017 $ 5,309 $ $ 6,943 31.59% 47 IRR A B D F G H J K L M N O P Q R 49 50 51 52 53 2) Perform a sensitivity analysis to analyze the impact of inflation on the project's profitability. You must use Data Table in Excel to perform this analysis. You are free to choose the range of inflation rate. 54 55 56 Inflation IRR Inflation NPV IRR $ 6,943 $ 31.59% 1.00% 5,810 $ 29.56% 1.25% 5,947 $ 29.82% 1.50% 1.75% 2.00% 2.25% 2.50% 2.75% 3.00% 3.25% 6,085 $ 6,224 $ 6,365 $ 6,507 $ 6,651 $ 6,796 $ 6,943 $ 7,091 30.07% 30.32% 30.58% 30.83% 31.08% 31.34% 31.59% 31.85% 57 58 59 60 NPV $ 1.00% $ 1.25% $ 1.50% $ 1.75% $ 2.00% $ 2.25% $ 2.50% $ 2.75% $ 3.00% $ 3.25% $ 61 62 63 64 6,943 5,810 5,947 6,085 6,224 6,365 6,507 6,651 6,796 6,943 7,091 31.59% 29.56% 29.82% 30.07% 30.32% 30.58% 30.83% 31.08% 31.34% 31.59% 31.85% 65 66 67 68