SOLVE IN EXCEL USING SOLVER SHOW ALL FORMULAS AND THE SOLVER:

P04_72:

> Based on Thomas (1971). A toy company produces toys at two plants and sells them in three regions. The current demands at these regions are given in the file P04_72.xlsx. Each plant can produce up to 4500 units. Each toy sells for $30, and the cost of producing and shipping a toy from a given plant to a region is given in the same file. The company can advertise locally and nationally. Each $1 spent on a local ad raises sales in a region by 0.3 units, whereas each $1 spent advertising nationally increases sales in each region by 0.2 units. a. Determine how the company can maximize its profit. b. If sales stimulated by advertising exhibits diminishing returns, how would you change your model? A B C D WN Region 1 2400 Region 2 1800 Region 3 1400 1 Toy advertising 2 3 Current demands 4 5 6 7 Unit production and shipping costs from plants to regions 8 9 Plant 1 10 Plant 2 Region 1 $10 $15 Region 2 $9 $6 Region 3 $18 $12