Microsoft Excel 14.0 Answer Report Whether the maximum possible number of model planes should be ordered from the supplier. Which of the Solver reports helps you answer these questions? Objective Cell (Max) Cell Name $B$15 Revenue Original Value 7750 Final Value 87000 Variable Cells Cell Name Final Value Original Value 5 Integer Contin $B$6 30 $C$6 P 5 75 Contin Status Slack 0 Constraints Cell Name $B$18 Labour $B$19 Availability (Cars) $B$20 Availability (Planes) $B$21 Non-neg.C $B$22 Non-neg. P Cell Value Formula 300 $B$18=$D$21 75 $B$22>=$D$22 Binding Not Binding Binding Not Binding Not Binding 50 0 30 75 (d) (5 marks) My Hobby Shop is concerned that model cars are not popular in the current market. To encourage more sales, the manager wants to reduce the price of model cars to $500. Is this a good strategy? Give reasons for your answer. Calculate the new value of maximum monthly revenue if appropriate. Which of the Solver reports helps you answer this question? Explain briefly. Microsoft Excel 14.0 Sensitivity Report Variable Cells Final Allowable Reduced Cost Cell Name Objective Coefficient 650 Value 30 Increase Allowable Decrease 650 0 100 $B$6 $C$6 P 75 0 900 1E+30 120 Constraints Allowable Final Value 300 30 75 30 75 Shadow Price 260 0 Cell Name $B$18 Labour $B$19 Availability (Cars) $B$20 Availability (Planes) $B$21 Non-neg. C $B$22 Non-neg. P Constraint Allowable R.H. Side Increase 300 125 80 1E+30 75 25 0 30 Decrease 75 50 41.66666667 1E+30 120 0 0 0 75 1E+30 Home Insert Design Layout view Help Share Comments References Mallings Review Question 3 (Total of 23 marks) My Hobby Shop is the leading manufactures in toy industry. They specialize in model car and model plane for toy collectors. A special circuit that runs the power board of the model cars and planes are installed by the employees of My Hobby Shop. Estimated labour suggests that the employees will spend 2.5 hours installing the special circuit on a model car. They spend roughly 3 hours to complete this process for model planes. They expect to have about 300 hours available for this purpose each month. In addition, due to the detailed work required, My Hobby Shop will only take the first 80 orders of model cars per month to be manufactured in-house. A different supplier is contracted to supply at most 75 model planes per month. The company earns $650 on each model car it sells and $900 on each model plane. My Hobby Shop wishes to maximise the monthly revenue from the sales of these two products. For the questions that follow, use the following notation: C = Number of model cars sold per month P= Number of model planes sold per month For the questions that follow, the Excel Solver output given below may be of assistance. (a) (2 marks) Write an equation using the above notation for the objective function for this model, describing the maximum revenue. No calculations are required. (b) (2 marks) Formulate the labour constraint for the installation of the special circuit that runs the power board of the model cars and planes. (a) (2 marks) Describe the symmetry and modality of the distribution. Histogram of Amount Spent on Lunch 18 16 14 Frequency Amount Spent ($) Mean 6.864583 Standard Error 0.209577 Median 6.575 Mode 6 Standard Deviation 1.778321 Sample Variance 3.162425 Kurtosis 33.45997 Skewness 4.748056 Range 16.3 Minimum 2.95 Maximum 19.25 Sum 494.25 Count 72 Quartile 1 6 Quartile 3 7.3125 5 5.5 6 6.5 7 7.5 8 8.5 9 9.5 More Amount Spent(s) (f) (4 marks) My Hobby Shop is invited to participate in a hobby trade show. The fixed cost is projected to be $22,000. The manager decided that they will only take planes to the trade show for a special price of $800. The model plane will cost $500 to make. How many model planes does My Hobby Shop need to sell at the trade show to break even? (b) (4 marks) Use the 1.5xIQR rule to identify any outliers in this distribution. If yes, state the values of the outliers. Microsoft Excel 14.0 Answer Report Whether the maximum possible number of model planes should be ordered from the supplier. Which of the Solver reports helps you answer these questions? Objective Cell (Max) Cell Name $B$15 Revenue Original Value 7750 Final Value 87000 Variable Cells Cell Name Final Value Original Value 5 Integer Contin $B$6 30 $C$6 P 5 75 Contin Status Slack 0 Constraints Cell Name $B$18 Labour $B$19 Availability (Cars) $B$20 Availability (Planes) $B$21 Non-neg.C $B$22 Non-neg. P Cell Value Formula 300 $B$18=$D$21 75 $B$22>=$D$22 Binding Not Binding Binding Not Binding Not Binding 50 0 30 75 (d) (5 marks) My Hobby Shop is concerned that model cars are not popular in the current market. To encourage more sales, the manager wants to reduce the price of model cars to $500. Is this a good strategy? Give reasons for your answer. Calculate the new value of maximum monthly revenue if appropriate. Which of the Solver reports helps you answer this question? Explain briefly. Microsoft Excel 14.0 Sensitivity Report Variable Cells Final Allowable Reduced Cost Cell Name Objective Coefficient 650 Value 30 Increase Allowable Decrease 650 0 100 $B$6 $C$6 P 75 0 900 1E+30 120 Constraints Allowable Final Value 300 30 75 30 75 Shadow Price 260 0 Cell Name $B$18 Labour $B$19 Availability (Cars) $B$20 Availability (Planes) $B$21 Non-neg. C $B$22 Non-neg. P Constraint Allowable R.H. Side Increase 300 125 80 1E+30 75 25 0 30 Decrease 75 50 41.66666667 1E+30 120 0 0 0 75 1E+30 Home Insert Design Layout view Help Share Comments References Mallings Review Question 3 (Total of 23 marks) My Hobby Shop is the leading manufactures in toy industry. They specialize in model car and model plane for toy collectors. A special circuit that runs the power board of the model cars and planes are installed by the employees of My Hobby Shop. Estimated labour suggests that the employees will spend 2.5 hours installing the special circuit on a model car. They spend roughly 3 hours to complete this process for model planes. They expect to have about 300 hours available for this purpose each month. In addition, due to the detailed work required, My Hobby Shop will only take the first 80 orders of model cars per month to be manufactured in-house. A different supplier is contracted to supply at most 75 model planes per month. The company earns $650 on each model car it sells and $900 on each model plane. My Hobby Shop wishes to maximise the monthly revenue from the sales of these two products. For the questions that follow, use the following notation: C = Number of model cars sold per month P= Number of model planes sold per month For the questions that follow, the Excel Solver output given below may be of assistance. (a) (2 marks) Write an equation using the above notation for the objective function for this model, describing the maximum revenue. No calculations are required. (b) (2 marks) Formulate the labour constraint for the installation of the special circuit that runs the power board of the model cars and planes. (a) (2 marks) Describe the symmetry and modality of the distribution. Histogram of Amount Spent on Lunch 18 16 14 Frequency Amount Spent ($) Mean 6.864583 Standard Error 0.209577 Median 6.575 Mode 6 Standard Deviation 1.778321 Sample Variance 3.162425 Kurtosis 33.45997 Skewness 4.748056 Range 16.3 Minimum 2.95 Maximum 19.25 Sum 494.25 Count 72 Quartile 1 6 Quartile 3 7.3125 5 5.5 6 6.5 7 7.5 8 8.5 9 9.5 More Amount Spent(s) (f) (4 marks) My Hobby Shop is invited to participate in a hobby trade show. The fixed cost is projected to be $22,000. The manager decided that they will only take planes to the trade show for a special price of $800. The model plane will cost $500 to make. How many model planes does My Hobby Shop need to sell at the trade show to break even? (b) (4 marks) Use the 1.5xIQR rule to identify any outliers in this distribution. If yes, state the values of the outliers