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 B C D E F G H ID # 87 195 503 678 696 798 817 818 829 834 841 865 866 874 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 YEAR MAKE TYPE 2002 Ford Sedan 2004 Ford Truck 2003 Chevrolet Sedan 1995 Ford Pickup 2000 Dodge Van 2003 Ford Van 2004 Chevrolet Van 2000 Chevrolet Pickup 1995 Ford Pickup 1991 Chevrolet Van 1999 Ford Sedan 2000 GMC Van 2001 Ford Sedan 2004 Ford Van 1996 Chevrolet Pickup 1999 Ford Sedan 2005 Ford Truck 1996 Chevrolet Sedan 1997 Ford Pickup 2004 Ford Van 1993 Ford Van 2003 Ford Truck 2000 Ford Truck 1995 Ford Sedan 1998 Chevrolet Van 1995 Chevrolet Sedan 1999 Ford Pickup 1997 GMC Van 1996 Dodge Van ODOMETER DEPARTMENT ASSIGNED 37780 Police & Security 19870 Telecomm 8900 Police & Security 73419 Housing & Residential Life 43907 Police & Security 24575 Housing & Residential Life 16896 Housing & Residential Life 37786 Housing & Residential Life 32765 Housing & Residential Life 138456 Property 59378 Printing Services 42313 Housing & Residential Life 49973 Police & Security 43678 Telecomm 50580 Property 62743 Police & Security 12703 Housing & Residential Life 53383 Police & Security 52728 Housing & Residential Life 3500 Dining Services 93484 Printing Services 29781 Property 39292 Housing & Residential Life 78740 Police & Security 53560 Housing & Residential Life 137534 Police & Security 45608 Housing & Residential Life 89006 Athletics 56788 Telecomm PRICE MAINTENANCE 20,888 $ 1,933 18,682 $ 1,331 20,904 $ 939 11,866 $ 507 15,844 $ 566 19,846 $ 168 20,824 $ 705 13,783 $ 1,594 5,000 $ 1,370 11,449 $ 1,235 20,315 $ 473 12,500 $ 30 15,090 $ 567 21,580 $ 28 14,130 $ 279 22,413 $ 112 22,724 $ 1,591 16,539 $ 1,641 14,757 $ 2,000 22,438 $ 695 12,149 $ 378 19,634 $ 575 15,366 $ 3,491 15,871 $ 724 12,493 $ 1,299 20,453 $ 2,235 15,866 $ 72 16,748 $ 755 10,105 $ 345 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 877 888 897 904 1016 1055 1071 1140 1167 1172 1196 1374 1375 1415 1445 A B C D E G . 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 81 980 2,000 603 490 2,933 1,826 2,000 499 664 370 160 675 1,775 1,839 964 1,820 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 1,871 1,383 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 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 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 $ 2112 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 2124 2126 2128 2131 2132 2139 2141 2142 2153 2166 2167 2353 2364 2388 2412 2489 Van 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 Supreme $6,000.00 $10,000.00 $12,000.00 $18,000.00 $1,000.00 $1,000.00 $19,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) A B D E F G H I J Increase in Sales based on Advertising 206 3,000 2,500 2,000 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 Advertising ($) Increase in Units Sold $0 0 $500 75 $1,000 $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 Units Sold 1,500 Increase 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 B C D E F G H ID # 87 195 503 678 696 798 817 818 829 834 841 865 866 874 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 YEAR MAKE TYPE 2002 Ford Sedan 2004 Ford Truck 2003 Chevrolet Sedan 1995 Ford Pickup 2000 Dodge Van 2003 Ford Van 2004 Chevrolet Van 2000 Chevrolet Pickup 1995 Ford Pickup 1991 Chevrolet Van 1999 Ford Sedan 2000 GMC Van 2001 Ford Sedan 2004 Ford Van 1996 Chevrolet Pickup 1999 Ford Sedan 2005 Ford Truck 1996 Chevrolet Sedan 1997 Ford Pickup 2004 Ford Van 1993 Ford Van 2003 Ford Truck 2000 Ford Truck 1995 Ford Sedan 1998 Chevrolet Van 1995 Chevrolet Sedan 1999 Ford Pickup 1997 GMC Van 1996 Dodge Van ODOMETER DEPARTMENT ASSIGNED 37780 Police & Security 19870 Telecomm 8900 Police & Security 73419 Housing & Residential Life 43907 Police & Security 24575 Housing & Residential Life 16896 Housing & Residential Life 37786 Housing & Residential Life 32765 Housing & Residential Life 138456 Property 59378 Printing Services 42313 Housing & Residential Life 49973 Police & Security 43678 Telecomm 50580 Property 62743 Police & Security 12703 Housing & Residential Life 53383 Police & Security 52728 Housing & Residential Life 3500 Dining Services 93484 Printing Services 29781 Property 39292 Housing & Residential Life 78740 Police & Security 53560 Housing & Residential Life 137534 Police & Security 45608 Housing & Residential Life 89006 Athletics 56788 Telecomm PRICE MAINTENANCE 20,888 $ 1,933 18,682 $ 1,331 20,904 $ 939 11,866 $ 507 15,844 $ 566 19,846 $ 168 20,824 $ 705 13,783 $ 1,594 5,000 $ 1,370 11,449 $ 1,235 20,315 $ 473 12,500 $ 30 15,090 $ 567 21,580 $ 28 14,130 $ 279 22,413 $ 112 22,724 $ 1,591 16,539 $ 1,641 14,757 $ 2,000 22,438 $ 695 12,149 $ 378 19,634 $ 575 15,366 $ 3,491 15,871 $ 724 12,493 $ 1,299 20,453 $ 2,235 15,866 $ 72 16,748 $ 755 10,105 $ 345 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 877 888 897 904 1016 1055 1071 1140 1167 1172 1196 1374 1375 1415 1445 A B C D E G . 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 81 980 2,000 603 490 2,933 1,826 2,000 499 664 370 160 675 1,775 1,839 964 1,820 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 1,871 1,383 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 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 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 $ 2112 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 2124 2126 2128 2131 2132 2139 2141 2142 2153 2166 2167 2353 2364 2388 2412 2489 Van 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 Supreme $6,000.00 $10,000.00 $12,000.00 $18,000.00 $1,000.00 $1,000.00 $19,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) A B D E F G H I J Increase in Sales based on Advertising 206 3,000 2,500 2,000 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 Advertising ($) Increase in Units Sold $0 0 $500 75 $1,000 $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 Units Sold 1,500 Increase 1,000 500 $0 $2,000 $4,000 $6,000 $8,000 $10,000 Advertising Dollars