Thanks!

Problem 2 Use the worksheets Disks and Advertising Model for this problem. Disks, Inc sells several types of professional Frisbee disks. Mike Smith, the general manager of the company has created an Income statement, based on the various data he has, in the worksheet named Disks. The Advertising Model worksheet shows how many units of the disks can be sold for different levels of advertising. Using Excel DSS capabilities, help Mike answer the following questions. 1. Mike Smith wants to know how the Insurance & Maintenance (O&M) expenses for Regular disks impacts the net income for Regular disks. The I&M expenses can vary between $3500 and $5000 (assume step size of $250) and the price per unit of Regular disks can vary between 512 and $24 (assume step size of $2). Show the net income values for Regular disks under the various combinations of 18M expenses and price per unit as above? a. What is the net income for Regular disks when I&M expense is $4500 and price per unit is $18? b. What Excel feature did you use to answer this question? 2. Mike wants to determine the impact of different advertising approaches and answer the following questions: a. How many units of Tournament disks should be sold by Word of Mouth in order for this disk type to break even? b. What should the Price per Unit be set at in order for the Supreme disks to break even? What Excel feature did you use to answer these questions? . 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 515000 and the company wants to spend at least 52000 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? Important Final step to Get Credit Complete your work by the deadline and upload the Word document and the Excel file through the appropriate Canvas link. Remember, the files have to be named as specified in this document to get credit for this assignment. No exceptions. G H A Revenue Units Sold Based on Word of Mouth Units Sold Based on Advertising Total Units Sold Price per Unit Total B C D E Regular High Flyer Tournament Supreme 2,100 2,050 1750 1,000 205 205 205 205 2.305 2,255 1.955 1,205 12 20 32 27,665 45,108 46,929 38,572 F Total 6.900 822 7,722 22.00 158,274 24 Variable Expenses Units Produced To Cover Demand Cost per Unit Total Regular High Fleet Tournament Supreme 2536 2,481 2,151 1326 5 10 16 20 12,680 24.809 34,415 26,518 Total 8,494 12.75 98.422 Fired Expenses Insurance and maintenance Other Overhead Advertising Total Regular High Flget Tournament Supreme 3,000 5.000 6,000 10,000 6,000 8,500 12,000 18.000 1,000 1.000 1.000 1000 10,000 14,500 19,000 29.000 Total 24,000 44,500 4,000 72,500 Income Summary Total Revenue Total Expenses Net Income Regular High Flyer Tournament Supreme 27.665 45,108 46,929 38,572 22.680 39,309 53.415 55,518 4,985 5.798 -6,486 -16,946 Total 158,274 170,922 -12,648 3 5 Two variable data-table 3 3 (12,648) 12 14 16 4,500 (1,521) 7,591 16,702 25,814 34,926 44,037 53,149 5,000 (705) 8,811 18,327 27,843 137.359 46,876 56,392 5,500 (359) 9.418 19,195 28,971 38,748 48,525 58,302 6,000 (371) 9,556 19,483 29,410 39,337 49,264 59,191 1,500 (618) 9,386 19,391 29,396 39,400 49.405 59.409 7,000 (1,002) 9,039 19,079 29,120 39,160 49,201 59.242 7,500 (1,453) 8,602 18,658 28,713 38,768 48,824 58,879 20 22 24 Increase in Sales based on Advertising 3,000 2.500 2,000 Advertising (S) lincrease in Units Sold SO 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 Increase in Units Sold 1,500 1,000 500 Plot Area 50 $2,000 58,000 $10.000 $4,000 $6,000 Advertising Dollars Cover Sheet Vehicles Disks Advertising Model