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 (I&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 $12 and $24 (assume step size of $2). Show the net income values for Regular disks under the various combinations of I&M 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? Income Statement Revenue Regular High Flyer Tournament Supreme Total Units Sold Based on Word of Mouth 2100 2050 1750 1000 6900 Units Sold Based on Advertising 205 Total Units Sold Price per Unit 2305 $12.00 Total $27,664.60 205 2255 $20.00 $45,107.67 205 205 822 1955 $24.00 $46,929.21 1205 7722 $32.00 $22.00 $38,572.28 $158,273.76 Variable Expenses Regular High Flyer Tournament Supreme Total Units Produced To Cover Demand 2,535.92 2,480.92 2,150.92 1,325.92 8,493.69 Cost per Unit $5.00 Total $12,679.61 $10.00 $24,809.22 $16.00 $34,414.75 $20.00 $12.75 $26,518.44 $98,422.02 Fixed Expenses Regular High Flyer Insurance and maintenance $3,000.00 $5,000.00 Other Overhead $6,000.00 $8,500.00 Tournament Supreme Total $6,000.00 $10,000.00 $24,000.00 $12,000.00 $18,000.00 $44,500.00 Advertising $1,000.00 $1,000.00 Total $10,000.00 $14,500.00 $1,000.00 $1,000.00 $4,000.00 $19,000.00 $29,000.00 $72,500.00 Income Summary Regular High Flyer Tournament Total Revenue $27,664.60 $45,107.67 Total Expenses $22,679.61 $39,309.22 Net Income $4,984.99 $5,798.45 $46,929.21 Supreme Total $38,572.28||$158,273.76 $53,414.75 $55,518.44 $170,922.02 ($6,485.54) ($16,946.16)|| ($12,648.26) Advertising ($) Increase in Units Sold $0 0 Increase in Sales based on Advertising $500 75 $1,000 206 3,000 $1,500 408 $2,000 689 2,500 $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 Increase in Units Sold 2,000 1,500 1,000 $7,000 2,921 $7,500 2,928 500 $8,000 2,931 $8,500 2,932 $9,000 2,932 $0 $2,000 $4,000 $6,000 $8,000 $10,000 $9,500 2,932 $10,000 2,932 Advertising Dollars Regular Disk Income Statement Revenue Total units sold Price per unit Total units sold 4,980.39 $ 3,500.00 $3,750.00 $4,000.00 $4,250.00 $ 4,500.00 $4,750.00 $ 5,000.00 12.00 $ 2,305 14.00 $ 12.00 16.00 $27,660.00 18.00 $ 20.00 $12,679.61 22.00 $ 24.00 Variable Expenses Fixed Expenses Insurance and maintenance $ 3,000.00 Other overhead $ 6,000.00 $ 1,000.00 $10,000.00 Advertising Total Income Summary Total Revenue Total Expenses Net Income $27,660.00 $22,679.61 $ 4,980.39 Data Table ? Row input cell: Column input cell: OK Cancel