Kellpost Cereal Company sells four products: (1) Special L (a low-calorie, high-nutrition cereal); (2) Corn Bran (another low-calorie, high-nutrition cereal); (3) Admiral Smacks (a sugary cereal pitched at the children's market); and (4) Honey Pops (another sweet cereal pitched at the children's market). Kellpost has sufficient production capacity to produce a total of 10,000 boxes of cereal per month. For each of the past 16 months, Kellpost has kept track of the price and sales of each product. (These data are listed in the file P07_72.xlsx.) Market executives believe that Special L and Corn Bran might be substitutes for each other, as might be Admiral Smacks and Honey Pops. For example, this means that an increase in the price of Special L might raise the sales of Corn Bran. The variable cost of bringing a box of each cereal to market is as follows: Special L, $2.00; Corn Bran, $2.20; Admiral Smacks, $2.30; Honey Pops, $2.40. a. Use the given information to determine the price for each cereal that will enable Kellpost to maximize profits. If needed, round your answers to two decimal digits. Cereal Brands Unit Price Special L $ Corn Bran $ Admiral Smacks $ Honey Pops $ b. Now suppose that Kellpost can increase its monthly production capacity. The cost (per year) of doing this is $20,000 per 1000 boxes of added monthly capacity. Can you determine an optimal capacity level? If an answer is zero, enter "0". Extra capacity (boxes): Cereal data Historical data Prices Sales Month Special L Corn Bran Admiral Smacks Honey Pops Special L Corn Bran Admiral Smacks Honey Pops 1 $4.20 $4.10 $4.22 $4.35 3251 4933 3463 5467 2 $3.90 $4.30 $4.12 $4.18 3524 4629 3471 5607 3 $4.10 $4.50 $4.32 $4.22 3349 4456 3362 5494 4 $4.00 $4.40 $3.86 $4.13 3380 4497 3875 5507 5 $3.80 $3.80 $3.99 $4.56 3447 5193 3726 5084 6 $3.90 $3.90 $3.76 $3.78 3560 4885 3961 6063 7 $3.80 $3.70 $4.07 $3.89 3689 5344 3669 5953 8 $4.30 $3.60 $4.05 $3.97 3013 5522 3654 5959 9 $3.60 $3.95 $4.09 $4.22 3824 4935 3720 5470 10 $3.50 $4.06 $4.12 $4.13 3760 4845 3641 5659 11 $3.40 $4.12 $4.13 $4.50 4047 4848 3655 5183 12 $3.70 $4.12 $5.16 $4.00 3746 4706 2430 6110 13 $3.80 $4.20 $4.17 $4.06 3722 4665 3614 5683 14 $3.90 $4.28 $4.00 $4.22 3461 4653 3652 5464 15 $4.00 $4.10 $3.90 $4.12 3322 4847 3881 5660 16 $4.10 $3.90 $3.96 $3.94 3320 5090 3751 5932

Kellpost Cereal Company sells four products: (1) Special L (a low-calorie, high-nutrition cereal); (2) Corn Bran (another low-calorie, high-nutrition cereal); (3) Admiral Smacks (a sugary cereal pitched at the children's market); and (4) Honey Pops (another sweet cereal pitched at the children's market). Kellpost has sufficient production capacity to produce a total of 10,000 boxes of cereal per month. For each of the past 16 months, Kellpost has kept track of the price and sales of each product. (These data are listed in the file P07_72.xlsx.) Market executives believe that Special L and Corn Bran might be substitutes for each other, as might be Admiral Smacks and Honey Pops. For example, this means that an increase in the price of Special L might raise the sales of Corn Bran. The variable cost of bringing a box of each cereal to market is as follows: Special L, $2.00; Corn Bran, $2.20; Admiral Smacks, $2.30; Honey Pops, $2.40. a. Use the given information to determine the price for each cereal that will enable Kellpost to maximize profits. If needed, round your answers to two decimal digits. Cereal Brands Unit Price Special L $ Corn Bran $ Admiral Smacks $ Honey Pops $ b. Now suppose that Kellpost can increase its monthly production capacity. The cost (per year) of doing this is $20,000 per 1000 boxes of added monthly capacity. Can you determine an optimal capacity level? If an answer is zero, enter "0". Extra capacity (boxes): Cereal data Historical data Prices Sales Month Special L Corn Bran Admiral Smacks Honey Pops Special L Corn Bran Admiral Smacks Honey Pops 1 $4.20 $4.10 $4.22 $4.35 3251 4933 3463 5467 2 $3.90 $4.30 $4.12 $4.18 3524 4629 3471 5607 3 $4.10 $4.50 $4.32 $4.22 3349 4456 3362 5494 4 $4.00 $4.40 $3.86 $4.13 3380 4497 3875 5507 5 $3.80 $3.80 $3.99 $4.56 3447 5193 3726 5084 6 $3.90 $3.90 $3.76 $3.78 3560 4885 3961 6063 7 $3.80 $3.70 $4.07 $3.89 3689 5344 3669 5953 8 $4.30 $3.60 $4.05 $3.97 3013 5522 3654 5959 9 $3.60 $3.95 $4.09 $4.22 3824 4935 3720 5470 10 $3.50 $4.06 $4.12 $4.13 3760 4845 3641 5659 11 $3.40 $4.12 $4.13 $4.50 4047 4848 3655 5183 12 $3.70 $4.12 $5.16 $4.00 3746 4706 2430 6110 13 $3.80 $4.20 $4.17 $4.06 3722 4665 3614 5683 14 $3.90 $4.28 $4.00 $4.22 3461 4653 3652 5464 15 $4.00 $4.10 $3.90 $4.12 3322 4847 3881 5660 16 $4.10 $3.90 $3.96 $3.94 3320 5090 3751 5932

Practical Management Science

6th Edition

ISBN:9781337406659

Author:WINSTON, Wayne L.

Publisher:WINSTON, Wayne L.

Chapter2: Introduction To Spreadsheet Modeling

Section: Chapter Questions

Problem 20P: Julie James is opening a lemonade stand. She believes the fixed cost per week of running the stand...

See similar textbooks

Cereal Brands	Unit Price
Special L	$
Corn Bran	$
Admiral Smacks	$
Honey Pops	$

Cereal data

Historical data
	Prices				Sales
Month	Special L	Corn Bran	Admiral Smacks	Honey Pops	Special L	Corn Bran	Admiral Smacks	Honey Pops
1	$4.20	$4.10	$4.22	$4.35	3251	4933	3463	5467
2	$3.90	$4.30	$4.12	$4.18	3524	4629	3471	5607
3	$4.10	$4.50	$4.32	$4.22	3349	4456	3362	5494
4	$4.00	$4.40	$3.86	$4.13	3380	4497	3875	5507
5	$3.80	$3.80	$3.99	$4.56	3447	5193	3726	5084
6	$3.90	$3.90	$3.76	$3.78	3560	4885	3961	6063
7	$3.80	$3.70	$4.07	$3.89	3689	5344	3669	5953
8	$4.30	$3.60	$4.05	$3.97	3013	5522	3654	5959
9	$3.60	$3.95	$4.09	$4.22	3824	4935	3720	5470
10	$3.50	$4.06	$4.12	$4.13	3760	4845	3641	5659
11	$3.40	$4.12	$4.13	$4.50	4047	4848	3655	5183
12	$3.70	$4.12	$5.16	$4.00	3746	4706	2430	6110
13	$3.80	$4.20	$4.17	$4.06	3722	4665	3614	5683
14	$3.90	$4.28	$4.00	$4.22	3461	4653	3652	5464
15	$4.00	$4.10	$3.90	$4.12	3322	4847	3881	5660
16	$4.10	$3.90	$3.96	$3.94	3320	5090	3751	5932