Need helping completing excel. Please let me know if it is set up correctly and please include excel formulas.
Thank you very much.
Establishing a Base Case Christopher's initial analysis established the expected price point for retailers at $22 per unit for Advanced Scal, compared to $18 and $13 per unit for P&G's Premium and Basic offering, respectively. Christopher had worked with his supply chain leaders to estimate the cost structure. The new technology would run at a cost of $5 per unit cost more than the current Premium product offering, such that the gross profit for Advanced Seal would be lower than for Premium. Exhibit 6 provides the summary assessments that had coalesced regarding the unit price and cost for the Crest Whitestrips products. The forecasting models suggested a base case annual forecast of 2 million units for Advanced Scal. The analysis also suggested that cannibalization of existing Crest Whitestrips products would be high, on the order of 50% to 60% for Premium units and 15% for Basic units. Such cannibalization rates meant that 65% to 75% of Advanced Seal's 2 million expected units was coming straight out of existing P&G sales. Preliminary discussions around advertising spending indicated an expected launch budget of $6 million per year. He estimated that the cannibalized Premium and Basic products already received $4 million per year in advertising support that would no longer be required after the launch. This meant the group would have to spend an incremental $2 million in advertising to support the launch. He also needed to include $1 million per year for incremental selling general, and administrative expenses. Based on the amount of time R&D felt it would take a competitor to match the product innovation, Christopher expected a project life of four years, over which time annual unit sales were expected to be relatively constant. For this type of decision, P&G used an 8% discount rate and a 40% tax rate. Manufacturing partners expected to spend $4 million in capital expenditures and incur $1.5 million in one- time development expenses to get the project going. Regarding capital expenditure depreciation, he conferred with an accounting team, which recommended the five-year accelerated schedule for tax purposes and the Page 4 of 12 Page 5 UVA-F-1670 straight-line schedule for reporting purposes. Engineering indicated that the equipment likely would need to be replaced at the end of the project life, and they did not expect it to have any residual value. Christopher also know that he had to factor in any incremental working capital required to support the project. For the Whitestrips business, net working capital tumover typically ran at a rate of between 8 and 10 times. The project would require that at least this amount be on hand prior to the market launch date. It was P&G's policy to model the recovery of any working capital investment at the end of the project life. 1) Use the base case assumptions (Pg. 4) as well as the information presented in the case to build a four-year discounted cash flow model for Advanced Seal given a 50% Cannibalization rate for the Premium Product and a 15% Cannibalization rate for the Basic Product. What are your NPV and IRR results? Please use the "Basic Template from the Excel file provided for the project. (15 pts) 2) Calculate (1) again using a 55% Cannibalization for the Premium Product and a 15% Cannibalization rate for the Basic Product Show your NPV and IRR results. Also, calculate (1) again using a 60% Cannibalization rate for the Premium Product and a 15% Cannibalization rate for the Basic Product. Show your NPV and IRR results. (10 pts) C D E 2 Discounted Cash Flow Valuation Premium Product Basic Product $18 $7 $11 -50% $13 $6 $7 -15% Year Year 2 Year 3 Year 4 20% 32%. 19.2% 11.52% 4 Assumptions 5 Advanced Seal 6 Per unit revenue and costs 7 Revenue $22 8 COGS $12 9 Gross profit $10 10 Cannibalization rate 11 12 Year o 13 Volume 14 Adv Seal 2,000,000 15 Premium -1,000,000 16 Basic -300,000 17 Incremental 18 19 SG&A expenses $1,000,000 20 Advertising (Adv Seal) $2,000,000 21 Foregone advertising (Prem + Basi $4,000,000 22 23 Capital investment $4,000,000 24 Startup costs $1,500,000 25 Depreciation schedule 0 26 Networking capital turnover 9 27 Tax rate 40% 28 Discount rate 87 29 30 Revenue 31 Adv Seal 44,000,000 32 Premium+Basic -21,900,000 33 Incremental revenue 34 35 Gross profit 36 Adv Seal 20,000,000 37 Premium+Basic -13,100,000 38 Incremental gross profit 39 40 Incremental advertising exp 2,000,000 41 SG&A expenses 1,000,000 42 Depreciation 43 Incremental EBIT 44 Taxes 45 NOPAT 46 47 Networking capital 48 Net PP&E 49 50 Free Cash Flow 51 NOPAT 52 + Depreciation 53 - Capital expenditures 54 - Investment in NWC 55 Free cash flow 56 57 NPV 58 IRR Establishing a Base Case Christopher's initial analysis established the expected price point for retailers at $22 per unit for Advanced Scal, compared to $18 and $13 per unit for P&G's Premium and Basic offering, respectively. Christopher had worked with his supply chain leaders to estimate the cost structure. The new technology would run at a cost of $5 per unit cost more than the current Premium product offering, such that the gross profit for Advanced Seal would be lower than for Premium. Exhibit 6 provides the summary assessments that had coalesced regarding the unit price and cost for the Crest Whitestrips products. The forecasting models suggested a base case annual forecast of 2 million units for Advanced Scal. The analysis also suggested that cannibalization of existing Crest Whitestrips products would be high, on the order of 50% to 60% for Premium units and 15% for Basic units. Such cannibalization rates meant that 65% to 75% of Advanced Seal's 2 million expected units was coming straight out of existing P&G sales. Preliminary discussions around advertising spending indicated an expected launch budget of $6 million per year. He estimated that the cannibalized Premium and Basic products already received $4 million per year in advertising support that would no longer be required after the launch. This meant the group would have to spend an incremental $2 million in advertising to support the launch. He also needed to include $1 million per year for incremental selling general, and administrative expenses. Based on the amount of time R&D felt it would take a competitor to match the product innovation, Christopher expected a project life of four years, over which time annual unit sales were expected to be relatively constant. For this type of decision, P&G used an 8% discount rate and a 40% tax rate. Manufacturing partners expected to spend $4 million in capital expenditures and incur $1.5 million in one- time development expenses to get the project going. Regarding capital expenditure depreciation, he conferred with an accounting team, which recommended the five-year accelerated schedule for tax purposes and the Page 4 of 12 Page 5 UVA-F-1670 straight-line schedule for reporting purposes. Engineering indicated that the equipment likely would need to be replaced at the end of the project life, and they did not expect it to have any residual value. Christopher also know that he had to factor in any incremental working capital required to support the project. For the Whitestrips business, net working capital tumover typically ran at a rate of between 8 and 10 times. The project would require that at least this amount be on hand prior to the market launch date. It was P&G's policy to model the recovery of any working capital investment at the end of the project life. 1) Use the base case assumptions (Pg. 4) as well as the information presented in the case to build a four-year discounted cash flow model for Advanced Seal given a 50% Cannibalization rate for the Premium Product and a 15% Cannibalization rate for the Basic Product. What are your NPV and IRR results? Please use the "Basic Template from the Excel file provided for the project. (15 pts) 2) Calculate (1) again using a 55% Cannibalization for the Premium Product and a 15% Cannibalization rate for the Basic Product Show your NPV and IRR results. Also, calculate (1) again using a 60% Cannibalization rate for the Premium Product and a 15% Cannibalization rate for the Basic Product. Show your NPV and IRR results. (10 pts) C D E 2 Discounted Cash Flow Valuation Premium Product Basic Product $18 $7 $11 -50% $13 $6 $7 -15% Year Year 2 Year 3 Year 4 20% 32%. 19.2% 11.52% 4 Assumptions 5 Advanced Seal 6 Per unit revenue and costs 7 Revenue $22 8 COGS $12 9 Gross profit $10 10 Cannibalization rate 11 12 Year o 13 Volume 14 Adv Seal 2,000,000 15 Premium -1,000,000 16 Basic -300,000 17 Incremental 18 19 SG&A expenses $1,000,000 20 Advertising (Adv Seal) $2,000,000 21 Foregone advertising (Prem + Basi $4,000,000 22 23 Capital investment $4,000,000 24 Startup costs $1,500,000 25 Depreciation schedule 0 26 Networking capital turnover 9 27 Tax rate 40% 28 Discount rate 87 29 30 Revenue 31 Adv Seal 44,000,000 32 Premium+Basic -21,900,000 33 Incremental revenue 34 35 Gross profit 36 Adv Seal 20,000,000 37 Premium+Basic -13,100,000 38 Incremental gross profit 39 40 Incremental advertising exp 2,000,000 41 SG&A expenses 1,000,000 42 Depreciation 43 Incremental EBIT 44 Taxes 45 NOPAT 46 47 Networking capital 48 Net PP&E 49 50 Free Cash Flow 51 NOPAT 52 + Depreciation 53 - Capital expenditures 54 - Investment in NWC 55 Free cash flow 56 57 NPV 58 IRR
