3. If any of the cell values had changed by working on problem 2 revert to the original values by clicking Undo. Under the original worksheet values, what should be spent on advertising for each disk type in order to maximize the Net Income for the whole company (i.e., for Disks, Inc.)? The constraints are as follows. The total advertising budget is limited to $15000 and the company wants to spend at least $2000 towards promoting each disk type. But, the company can only make a maximum of 5000 Supreme disks to sell. Mr. Smith also wants none of the individual disk types to make a loss. a. What is the amount spent on advertising for each disk type? b. What is the number of units produced to cover demand for High Flyer disks? C. What is the net income for Tournament disks? d. What is number of units sold based on advertising for Supreme disks? e. What Excel feature did you use to answer these questions? A G H 81 980 2,000 603 490 2,933 1,826 2,000 499 664 370 160 675 1,775 1,839 964 1,820 1,871 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 1455 1462 1476 1575 1585 1599 1602 1642 1643 1648 1649 1675 1678 1680 1733 1735 1776 1861 1878 1949 2020 2021 2063 2071 2084 2112 2124 2126 2128 2131 2132 2139 2141 2142 2153 2166 2167 2353 2364 2388 2412 2489 B D 1998 Ford Truck 1997 GMC Van 2002 Dodge Pickup 2000 Chevrolet Pickup 1999 Ford Sedan 1996 Chevrolet Van 1999 GMC Van 2003 Chevrolet Sedan 1998 Chevrolet Truck 1992 Chevrolet Van 2004 Chevrolet Pickup 1999 Chevrolet Pickup 2003 Ford Van 2003 Chevrolet Pickup 2005 Ford Pickup 1993 Chevrolet Sedan 1987 Chevrolet Sedan 2003 Chevrolet Van 1995 Chevrolet Van 2004 Chevrolet Van 2003 Ford Van 1999 Dodge Van 2001 Ford Sedan 2004 Ford Van 1997 GMC Van 1986 Ford Sedan 2002 Ford Van 2005 Ford Truck 1994 Ford Van 2001 Chevrolet Pickup 1998 Chevrolet Pickup 2004 Dodge Van 2005 Ford Van 2005 GMC Truck 2001 Chevrolet Van 1998 Chevrolet Van 2003 Ford Truck 2001 GMC Pickup 1990 Chevrolet Sedan 2001 Dodge Van 2000 Ford Van 2003 Chevrolet Van E F 76681 Housing & Residential Life 82091 Athletics 47222 Property 43896 Property 65404 Police & Security 52672 Printing Services 35860 Dining Services 44108 Property 43912 Housing & Residential Life 101712 Housing & Residential Life 2250 Printing Services 54741 Property 25126 Housing & Residential Life 23915 Housing & Residential Life 5272 Police & Security 110863 Property 81802 Housing & Residential Life 8993 Police & Security 56047 Housing & Residential Life 34474 Dining Services 22589 Housing & Residential Life 59800 Dining Services 62611 Police & Security 18755 Property 74309 Athletics 103123 Housing & Residential Life 36838 Printing Services 3670 Housing & Residential Life 99880 Athletics 31394 Housing & Residential Life 77104 Housing & Residential Life 10650 Property 5721 Housing & Residential Life 2800 Telecomm 32315 Telecomm 43038 Housing & Residential Life 14678 Dining Services 58570 Dining Services 92419 Athletics 33321 Housing & Residential Life 35497 Housing & Residential Life 26493 Property $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 12,850 $ 20,882 $ 11,232 $ 12,643 $ 22,674 $ 15,773 $ 16,356 $ 11,136 $ 7,200 $ 14,929 $ 20,680 $ 9,839 $ 12,957 $ 16,763 $ 15,914 $ 11,041 $ 14,993 $ 15,592 $ 15,930 $ 18,712 $ 22,045 $ 14,965 $ 18,872 $ 20,336 $ 14,187 $ 12,007 $ 11,518 $ 9,471 $ 15,197 $ 15,613 $ 14,019 $ 11,985 $ 22,546 $ 13,410 $ 14,109 $ 17,022 $ 21,743 $ 14,127 $ 11,630 $ 15,485 $ 9,025 $ 14,347 $ 1,383 1,025 379 1,948 751 402 559 1,350 2,000 105 1,975 450 594 1,245 1,118 257 732 1,200 202 1,255 2,257 118 191 1,002 A B D E F Regular 2100 205 2305 $12.00 $27,664.60 High Flyer 2050 205 2255 $20.00 $45,107.67 Tournament 1750 205 1955 $24.00 $46,929.21 Supreme Total 1000 6900 205 822 1205 7722 $32.00 $22.00 $38,572.28||$ 158,273.76 1 Income Statement 2 3 Revenue 4 Units Sold Based on Word of Mouth 5 Units Sold Based on Advertising 6 Total Units Sold 7 Price per Unit 8 Total 9 10 Variable Expenses 11 Units Produced To Cover Demand 12 Cost per Unit 13 Total 14 15 Fixed Expenses 16 Insurance and maintenance 17 Other Overhead 18 Advertising 19 Total 20 21 Income Summary 22 Total Revenue 23 Total Expenses 24 Net Income Regular 2,535.92 $5.00 $12,679.61 High Flyer 2,480.92 $10.00 $24,809.22 Tournament 2,150.92 $16.00 $34,414.75 Supreme 1,325.92 $20.00 $26,518.44 Total 8,493.69 $12.75 $98,422.02 Regular $3,000.00 $6,000.00 $1,000.00 $10,000.00 High Flyer $5,000.00 $8,500.00 $1,000.00 $14,500.00 Tournament $6,000.00 $12,000.00 $1,000.00 $19,000.00 Supreme $10,000.00 $18,000.00 $1,000.00 $29,000.00 Total $24,000.00 $44,500.00 $4,000.00 $72,500.00 Regular $27,664.60 $22,679.61 $4,984.99 High Flyer $45, 107.67 $39,309.22 $5,798.45 Tournament Supreme Total $46,929.21 $38,572.28 || $158,273.76 $53,414.75 $55,518.44 || $170,922.02 ($6,485.54) ($ 16,946.16) ($12,648.26) 25 B E F H I J 2 3 Increase in Sales based on Advertising 4 3,000 2,500 2,000 Advertising ($) Increase in Units Sold $0 0 $500 75 $1,000 206 $1,500 408 $2,000 689 $2,500 1,040 $3,000 1,432 $3,500 1,824 $4,000 2,175 $4,500 2,456 $5,000 2,658 $5,500 2,789 $6,000 2,864 $6,500 2,903 $7,000 2,921 $7,500 2,928 $8,000 2,931 $8,500 2,932 $9,000 2,932 $9,500 2,932 $10,000 2,932 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 1,500 Increase in Units Sold 1,000 500 $0 $2,000 $4,000 $6,000 $8,000 $10,000 Advertising Dollars 3. If any of the cell values had changed by working on problem 2 revert to the original values by clicking Undo. Under the original worksheet values, what should be spent on advertising for each disk type in order to maximize the Net Income for the whole company (i.e., for Disks, Inc.)? The constraints are as follows. The total advertising budget is limited to $15000 and the company wants to spend at least $2000 towards promoting each disk type. But, the company can only make a maximum of 5000 Supreme disks to sell. Mr. Smith also wants none of the individual disk types to make a loss. a. What is the amount spent on advertising for each disk type? b. What is the number of units produced to cover demand for High Flyer disks? C. What is the net income for Tournament disks? d. What is number of units sold based on advertising for Supreme disks? e. What Excel feature did you use to answer these questions? A G H 81 980 2,000 603 490 2,933 1,826 2,000 499 664 370 160 675 1,775 1,839 964 1,820 1,871 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 1455 1462 1476 1575 1585 1599 1602 1642 1643 1648 1649 1675 1678 1680 1733 1735 1776 1861 1878 1949 2020 2021 2063 2071 2084 2112 2124 2126 2128 2131 2132 2139 2141 2142 2153 2166 2167 2353 2364 2388 2412 2489 B D 1998 Ford Truck 1997 GMC Van 2002 Dodge Pickup 2000 Chevrolet Pickup 1999 Ford Sedan 1996 Chevrolet Van 1999 GMC Van 2003 Chevrolet Sedan 1998 Chevrolet Truck 1992 Chevrolet Van 2004 Chevrolet Pickup 1999 Chevrolet Pickup 2003 Ford Van 2003 Chevrolet Pickup 2005 Ford Pickup 1993 Chevrolet Sedan 1987 Chevrolet Sedan 2003 Chevrolet Van 1995 Chevrolet Van 2004 Chevrolet Van 2003 Ford Van 1999 Dodge Van 2001 Ford Sedan 2004 Ford Van 1997 GMC Van 1986 Ford Sedan 2002 Ford Van 2005 Ford Truck 1994 Ford Van 2001 Chevrolet Pickup 1998 Chevrolet Pickup 2004 Dodge Van 2005 Ford Van 2005 GMC Truck 2001 Chevrolet Van 1998 Chevrolet Van 2003 Ford Truck 2001 GMC Pickup 1990 Chevrolet Sedan 2001 Dodge Van 2000 Ford Van 2003 Chevrolet Van E F 76681 Housing & Residential Life 82091 Athletics 47222 Property 43896 Property 65404 Police & Security 52672 Printing Services 35860 Dining Services 44108 Property 43912 Housing & Residential Life 101712 Housing & Residential Life 2250 Printing Services 54741 Property 25126 Housing & Residential Life 23915 Housing & Residential Life 5272 Police & Security 110863 Property 81802 Housing & Residential Life 8993 Police & Security 56047 Housing & Residential Life 34474 Dining Services 22589 Housing & Residential Life 59800 Dining Services 62611 Police & Security 18755 Property 74309 Athletics 103123 Housing & Residential Life 36838 Printing Services 3670 Housing & Residential Life 99880 Athletics 31394 Housing & Residential Life 77104 Housing & Residential Life 10650 Property 5721 Housing & Residential Life 2800 Telecomm 32315 Telecomm 43038 Housing & Residential Life 14678 Dining Services 58570 Dining Services 92419 Athletics 33321 Housing & Residential Life 35497 Housing & Residential Life 26493 Property $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 12,850 $ 20,882 $ 11,232 $ 12,643 $ 22,674 $ 15,773 $ 16,356 $ 11,136 $ 7,200 $ 14,929 $ 20,680 $ 9,839 $ 12,957 $ 16,763 $ 15,914 $ 11,041 $ 14,993 $ 15,592 $ 15,930 $ 18,712 $ 22,045 $ 14,965 $ 18,872 $ 20,336 $ 14,187 $ 12,007 $ 11,518 $ 9,471 $ 15,197 $ 15,613 $ 14,019 $ 11,985 $ 22,546 $ 13,410 $ 14,109 $ 17,022 $ 21,743 $ 14,127 $ 11,630 $ 15,485 $ 9,025 $ 14,347 $ 1,383 1,025 379 1,948 751 402 559 1,350 2,000 105 1,975 450 594 1,245 1,118 257 732 1,200 202 1,255 2,257 118 191 1,002 A B D E F Regular 2100 205 2305 $12.00 $27,664.60 High Flyer 2050 205 2255 $20.00 $45,107.67 Tournament 1750 205 1955 $24.00 $46,929.21 Supreme Total 1000 6900 205 822 1205 7722 $32.00 $22.00 $38,572.28||$ 158,273.76 1 Income Statement 2 3 Revenue 4 Units Sold Based on Word of Mouth 5 Units Sold Based on Advertising 6 Total Units Sold 7 Price per Unit 8 Total 9 10 Variable Expenses 11 Units Produced To Cover Demand 12 Cost per Unit 13 Total 14 15 Fixed Expenses 16 Insurance and maintenance 17 Other Overhead 18 Advertising 19 Total 20 21 Income Summary 22 Total Revenue 23 Total Expenses 24 Net Income Regular 2,535.92 $5.00 $12,679.61 High Flyer 2,480.92 $10.00 $24,809.22 Tournament 2,150.92 $16.00 $34,414.75 Supreme 1,325.92 $20.00 $26,518.44 Total 8,493.69 $12.75 $98,422.02 Regular $3,000.00 $6,000.00 $1,000.00 $10,000.00 High Flyer $5,000.00 $8,500.00 $1,000.00 $14,500.00 Tournament $6,000.00 $12,000.00 $1,000.00 $19,000.00 Supreme $10,000.00 $18,000.00 $1,000.00 $29,000.00 Total $24,000.00 $44,500.00 $4,000.00 $72,500.00 Regular $27,664.60 $22,679.61 $4,984.99 High Flyer $45, 107.67 $39,309.22 $5,798.45 Tournament Supreme Total $46,929.21 $38,572.28 || $158,273.76 $53,414.75 $55,518.44 || $170,922.02 ($6,485.54) ($ 16,946.16) ($12,648.26) 25 B E F H I J 2 3 Increase in Sales based on Advertising 4 3,000 2,500 2,000 Advertising ($) Increase in Units Sold $0 0 $500 75 $1,000 206 $1,500 408 $2,000 689 $2,500 1,040 $3,000 1,432 $3,500 1,824 $4,000 2,175 $4,500 2,456 $5,000 2,658 $5,500 2,789 $6,000 2,864 $6,500 2,903 $7,000 2,921 $7,500 2,928 $8,000 2,931 $8,500 2,932 $9,000 2,932 $9,500 2,932 $10,000 2,932 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 1,500 Increase in Units Sold 1,000 500 $0 $2,000 $4,000 $6,000 $8,000 $10,000 Advertising Dollars