59..

J&J has given you $12 million to spend on advertising Huggys diapers during the next 12 months. At the beginning of January, Huggys has a 30% market share. During any month, 10% of the people who purchase Huggys defect to brand X, and a fraction of customers who usually buy brand X switch to Huggys, where a is the amount spent on advertising in millions of dollars. For example, if you spend $4 million during a month, 40% of brand Xs customers switch to Huggys. Your goal is to maximize J&Js average market share during the next 12 months, where the average is computed from each months ending share. Determine an appropriate advertising policy. (Hint: Make sure you enter a nonzero trial value for each months advertising expense or Solver might give you an error message.)

The first picture is what the problem looks like blank and the 2nd is the problem finished, I just need to know what formulas in excel to put in(Cell references) I WILL LEAVE A 5 STAR REVIEW! Thank you!!

L M N O 6 7 All dollar amounts must be $1000! Hurey's beginning mkt share 0.3 9 Percent who switch to brand X 0.1 Cava 11 % of non-huggy's customers who switch to Huggy's - 12 C= where a - amount of advertising (SMillion) and, C=0.2 presently. 0.2 13 Months 1 2 3 4 5 6 7 8 9 10 11 12 15 16 Advertising plan Amount spent (in $1000s) 18 Beginning market share 19 Market share for X 20 Gain in market share 21 Loss in marker share 22 Ending market share Row 18: Beginning market share is the ending market share from the previous month. Row 19: We are assuming there are only Hugey's and Brand X in the market, so the market that is not Hugey's is Brand X's market share. Row 20: 0) Gain Husey's come from the share of Brand X and is estimated by the formula given on the problem. The "a in the formula must be in Smillions, and the amount spent in advertising from row 18 is in $1000. (II) The formula only gives the proportion of Brand X's share that Huggy's will gain. So the actual gain is the result of the formula times the current share of Brand X. Row 21: Loss comes from the current market share of Hugey's at the rate given in the problem. Row 22: Ending market share is the beginning market share adjusted for the gain and loss during the month. Total spent on advertising Budget 12000 27 Average ending mkt share Available budget must be set up in $1000, Answer key: 0.61456 30 Caution: 31 Do not alter the structure of this 32 Excel file like adding/deleting columns/rows, moving contents from their 33 cells, or renaming sheet names. Use the specified output cell range. 34 You may change the column widths 35 slightly to accommodate proper formatting, but 36 changing the widths in a significant manner is prohibited. 37 Not following these instructions can result in serious loss of points. 38 All cells in all sheets must be properly formatted. 39 These include aligning contents, using appropriate 40 number format and decimal places for numerical contents, 41 use of bolding, italics, borders, etc. in an 42 appropriate fashion so that the readability of the content is enhanced. 1. Problem #59, page 415 Huggy's beginning mkt share 30% Percent who switch to brand X 10% % of non-huggy's customers who switch to HuggycsFva C= 0.2 where a = amount of advertising ($Million) and, C = 0.2 presently. Advertising plan Amount spent (in $1000s) 1 $3,146 2 $1,309 3 $1,220 4 $1,140 5 $1,062 Months 6 7 $978 $883 8 $771 9 $636 10 $472 11 $270 12 $114 Beginning market share Market share for X Gain in market share Loss in marker share Ending market share 30.0% 70.0% 24.8% 3.0% 51.8% 51.8% 48.2% 11.0% 5.2% 57.7% 57.7% 42.3% 9.3% 5.8% 61.3% 61.3% 38.7% 8.3% 6.1% 63.4% 63.4% 36.6% 7.5% 6.3% 64.6% 64.6% 35.4% 7.0% 6.5% 65.1% 65.1% 34.9% 6.5% 6.5% 65.2% 65.2% 34.8% 6.1% 6.5% 64.8% 64.8% 35.2% 5.6% 6.5% 63.9% 63.9% 36.1% 5.0% 6.4% 62.5% 62.5% 37.5% 3.9% 6.2% 60.1% 60.1% 39.9% 2.7% 6.0% 56.8% Total spent on advertising $12,000