NEED ANSWERED ASAP!! BEEN STUCK ON THIS FOR HOURRSSSS!!!! Please make sure to answer all parts of the question. DO NOT JUST POST THE ANSWERS!
PLEASE PROVIDE STEP-BY-STEP ANSWERS ON HOW TO FIND THE ANSWERS USING EXCELS SOLVER!!! NOTHING CAN BE NEGATIVE!
\begin{tabular}{|r|r|} \hline Advertising ($) & Increase in Units Sold \\ \hline$0 & 0 \\ \hline$500 & 75 \\ \hline$1,000 & 206 \\ \hline$1,500 & 408 \\ \hline$2,000 & 689 \\ \hline$2,500 & 1,040 \\ \hline$3,000 & 1,432 \\ \hline$3,500 & 1,824 \\ \hline$4,000 & 2,175 \\ \hline$4,500 & 2,456 \\ \hline$5,000 & 2,658 \\ \hline$5,500 & 2,789 \\ \hline$6,000 & 2,864 \\ \hline$6,500 & 2,903 \\ \hline$7,000 & 2,921 \\ \hline$7,500 & 2,928 \\ \hline$8,000 & 2,931 \\ \hline$8,500 & 2,932 \\ \hline$9,000 & 2,932 \\ \hline$9,500 & 2,932 \\ \hline$10,000 & 2,932 \\ \hline \end{tabular} Increase in Sales based on Advertising Advertising Dollars 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? (6 points) b. What is the number of units produced to cover demand for High Flyer disks? (6 points) c. What is the net income for Tournament disks? (6 points) d. What is number of units sold based on advertising for Supreme disks? (6 points) e. What Excel feature did you use to answer these questions? (2 points) Income Statement \begin{tabular}{|c|r|r|r|r||r|} \hline \multicolumn{1}{|l|}{ Revenue } & \multicolumn{1}{c|}{ Regular } & \multicolumn{1}{c||}{ High Flyer } & \multicolumn{1}{|c|}{ Tournament } & \multicolumn{1}{|c|}{ Supreme } & \multicolumn{1}{|c|}{ Total } \\ \hline Units Sold Based on Word of Mouth & 2100 & 2050 & 1750 & 1000 & 6900 \\ \hline Units Sold Based on Advertising & 205 & 205 & 205 & 205 & 822 \\ \hline Total Units Sold & 2305 & 2255 & 1955 & 1205 & 7722 \\ \hline Price per Unit & $12.00 & $20.00 & $24.00 & $32.00 & $22.00 \\ \hline \hline Total & $27,664.60 & $45,107.67 & $46,929.21 & $38,572.28 & $158,273.76 \\ \hline \end{tabular} \begin{tabular}{|l|r|r|r|r||r||r|} \hline 0 & Variable Expenses & \multicolumn{1}{|c|}{ Regular } & \multicolumn{1}{|c||}{ High Flyer } & \multicolumn{1}{|c|}{ Tournament } & \multicolumn{1}{|c|}{ Supreme } & \multicolumn{1}{|c|}{ Total } \\ \hline 1 & Units Produced To Cover Demand & 2,535.92 & 2,480.92 & 2,150.92 & 1,325.92 & 8,493.69 \\ \hline 2 Cost per Unit & $5.00 & $10.00 & $16.00 & $20.00 & $12.75 \\ \hline \hline & Total & $12,679.61 & $24,809.22 & $34,414.75 & $26,518.44 & $98,422.02 \\ \hline \end{tabular} \begin{tabular}{|c|r|r|r|r||r||r|} \hline Fixed Expenses & \multicolumn{1}{|c|}{ Regular } & \multicolumn{1}{c|}{ High Flyer } & \multicolumn{1}{|c|}{ Tournament } & \multicolumn{1}{|c||}{ Supreme } & \multicolumn{1}{|c|}{ Total } \\ \hline Insurance and maintenance & $3,000.00 & $5,000.00 & $6,000.00 & $10,000.00 & $24,000.00 \\ \hline Other Overhead & $6,000.00 & $8,500.00 & $12,000.00 & $18,000.00 & $44,500.00 \\ \hline Advertising & $1,000.00 & $1,000.00 & $1,000.00 & $1,000.00 & $4,000.00 \\ \hline \hline Total & $10,000.00 & $14,500.00 & $19,000.00 & $29,000.00 & $72,500.00 \\ \hline \end{tabular} \begin{tabular}{|c|r|r|r|r||r|} \hline Income Summary & \multicolumn{1}{|c|}{ Regular } & \multicolumn{1}{c|}{ High Flyer } & \multicolumn{1}{c||}{ Tournament } & \multicolumn{1}{|c|}{ Supreme } & \multicolumn{1}{|c|}{ Total } \\ \hline Total Revenue & $27,664.60 & $45,107.67 & $46,929.21 & $38,572.28 & $158,273.76 \\ \hline Total Expenses & $22,679.61 & $39,309.22 & $53,414.75 & $55,518.44 & $170,922.02 \\ \hline \hline Net Income & $4,984.99 & $5,798.45 & ($6,485.54) & ($16,946.16) & ($12,648.26) \\ \hline \end{tabular} \begin{tabular}{|r|r|} \hline Advertising ($) & Increase in Units Sold \\ \hline$0 & 0 \\ \hline$500 & 75 \\ \hline$1,000 & 206 \\ \hline$1,500 & 408 \\ \hline$2,000 & 689 \\ \hline$2,500 & 1,040 \\ \hline$3,000 & 1,432 \\ \hline$3,500 & 1,824 \\ \hline$4,000 & 2,175 \\ \hline$4,500 & 2,456 \\ \hline$5,000 & 2,658 \\ \hline$5,500 & 2,789 \\ \hline$6,000 & 2,864 \\ \hline$6,500 & 2,903 \\ \hline$7,000 & 2,921 \\ \hline$7,500 & 2,928 \\ \hline$8,000 & 2,931 \\ \hline$8,500 & 2,932 \\ \hline$9,000 & 2,932 \\ \hline$9,500 & 2,932 \\ \hline$10,000 & 2,932 \\ \hline \end{tabular} Increase in Sales based on Advertising Advertising Dollars 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? (6 points) b. What is the number of units produced to cover demand for High Flyer disks? (6 points) c. What is the net income for Tournament disks? (6 points) d. What is number of units sold based on advertising for Supreme disks? (6 points) e. What Excel feature did you use to answer these questions? (2 points) Income Statement \begin{tabular}{|c|r|r|r|r||r|} \hline \multicolumn{1}{|l|}{ Revenue } & \multicolumn{1}{c|}{ Regular } & \multicolumn{1}{c||}{ High Flyer } & \multicolumn{1}{|c|}{ Tournament } & \multicolumn{1}{|c|}{ Supreme } & \multicolumn{1}{|c|}{ Total } \\ \hline Units Sold Based on Word of Mouth & 2100 & 2050 & 1750 & 1000 & 6900 \\ \hline Units Sold Based on Advertising & 205 & 205 & 205 & 205 & 822 \\ \hline Total Units Sold & 2305 & 2255 & 1955 & 1205 & 7722 \\ \hline Price per Unit & $12.00 & $20.00 & $24.00 & $32.00 & $22.00 \\ \hline \hline Total & $27,664.60 & $45,107.67 & $46,929.21 & $38,572.28 & $158,273.76 \\ \hline \end{tabular} \begin{tabular}{|l|r|r|r|r||r||r|} \hline 0 & Variable Expenses & \multicolumn{1}{|c|}{ Regular } & \multicolumn{1}{|c||}{ High Flyer } & \multicolumn{1}{|c|}{ Tournament } & \multicolumn{1}{|c|}{ Supreme } & \multicolumn{1}{|c|}{ Total } \\ \hline 1 & Units Produced To Cover Demand & 2,535.92 & 2,480.92 & 2,150.92 & 1,325.92 & 8,493.69 \\ \hline 2 Cost per Unit & $5.00 & $10.00 & $16.00 & $20.00 & $12.75 \\ \hline \hline & Total & $12,679.61 & $24,809.22 & $34,414.75 & $26,518.44 & $98,422.02 \\ \hline \end{tabular} \begin{tabular}{|c|r|r|r|r||r||r|} \hline Fixed Expenses & \multicolumn{1}{|c|}{ Regular } & \multicolumn{1}{c|}{ High Flyer } & \multicolumn{1}{|c|}{ Tournament } & \multicolumn{1}{|c||}{ Supreme } & \multicolumn{1}{|c|}{ Total } \\ \hline Insurance and maintenance & $3,000.00 & $5,000.00 & $6,000.00 & $10,000.00 & $24,000.00 \\ \hline Other Overhead & $6,000.00 & $8,500.00 & $12,000.00 & $18,000.00 & $44,500.00 \\ \hline Advertising & $1,000.00 & $1,000.00 & $1,000.00 & $1,000.00 & $4,000.00 \\ \hline \hline Total & $10,000.00 & $14,500.00 & $19,000.00 & $29,000.00 & $72,500.00 \\ \hline \end{tabular} \begin{tabular}{|c|r|r|r|r||r|} \hline Income Summary & \multicolumn{1}{|c|}{ Regular } & \multicolumn{1}{c|}{ High Flyer } & \multicolumn{1}{c||}{ Tournament } & \multicolumn{1}{|c|}{ Supreme } & \multicolumn{1}{|c|}{ Total } \\ \hline Total Revenue & $27,664.60 & $45,107.67 & $46,929.21 & $38,572.28 & $158,273.76 \\ \hline Total Expenses & $22,679.61 & $39,309.22 & $53,414.75 & $55,518.44 & $170,922.02 \\ \hline \hline Net Income & $4,984.99 & $5,798.45 & ($6,485.54) & ($16,946.16) & ($12,648.26) \\ \hline \end{tabular}
