Devine Confections, a candy company, produces 4 nut products: Whole, Cluster, Crunch The capacity of the company fell behind the demand. Competition and increasing raw m also placed pressure on the company's profit margins, so the company needs to operate The company's C-Level Management started planning for the upcomming (current) fisca they secured the required information. The following information is attached as works A. Sales & Marketing: Sales forecast -- most likely and upper limit sales projections; Pric B. Procurement Departement: The critical raw materials during this planning period (av C. Human Resources Department: The historical wage increase data for adjustements t D. Manufacturing Department (Operational): Machine times required per unit product; E. Prior year's financials (prior planning period): Revenues, variable costs, fixed costs, s The company controller wants to discontinue manufacturing and selling Cluster and Whole products. Develop an optimal production model for the company and analyize the controller's suggestion. Then, answer the following: 1. What is the optimal production mix? 2. Is the solution unique? 3. If the company needs to reduce the production of one product, which should it be? 4. If marketing wants to increase the marketing budget to increase the potential marke how much should you authorize, if any, if you are the CEO? 5. If the company had an opportunity to acquire additonal resources, what resource(s) should be increased and what is the max cost the manager should be willing to pay? 6. If the product manager working with the sales group wanted to "re-market" the Who premium candy by changing the pakaging without adding cost, what is the min price 7. The director of sales examined the cusstomer order that was accepted in advance du delivery during this period. Determine how much she should be willing to discount t in order to induce that customer to modify the (accepted) purchase order for a small 8. The original return-on-sales (ROS) and the new ROS after optimization. [ROS=Total N 9. The manufacturing engineering manager looked at updating the manufacturing proc Determine a viable target for the technical coefficients (minimum attractive values). HINT: Model Based On Marginal Analysis Since the fixed costs are independent of volumes and are not expected to be impacted by Add the fixed costs back AFTER running the model. Re-apportion the fixed cost based on Carry all calculations to a minimum of 3 decimal places, and then round the final results NOTE: Run SOLVER with all 3 REPORTS specified. Use the reports to answer the questions questions using the "trial and error" method, except to vealidate your answers. ation is attached as worksheet reports from the following functional departments: required per unit product; machine capacities; raw material required per unit of product. xpected to be impacted by inflation during the next planning cycle, develop a model to maximize contributio on the fixed cost based on the proportion of units manufactured (of a particular product) to the total units pr rts to answer the questions and use the numbers from the reports to develop additional equations as necessa el to maximize contribution margins (Revenues less variable expenses). Manufacturing Requirements and Resource Availability For Ne Minutes required per pound Cluster Crunch Roasted 1.00 1.00 1.00 1.50 1.00 1.75 0.70 0.20 0.00 1.60 1.25 0.70 Machine Use Hulling Roasting Coating Packaging Whole 1.00 2.00 1.00 2.50 Direct Materials Nuts Chocolate Proportion of Materials Per Pound Whole Cluster Crunch Roasted 0.60 0.40 0.20 1.00 0.40 0.60 0.80 0.00 See Procurmen See Procurmen Assume that the total fixed costs remain the same as the prior year. ( Assume that the total variable manufacturing overhead remains the s ailability For Next Period Hours Available 3000.00 3000.00 1000.00 2700.00 Pounds Available See Procurment Dept Data See Procurment Dept Data s the prior year. (Unaffected by inflation.) ead remains the same as the prior year. Based on a combination of time series forecasts, input from the senior sales sta the sales department woking with marketing's researchers determined the sale The optimistic figures also consider the firms market share of their adressable Sales Forecast (In 1000's of Units) Optimistic Most Likely Pessimestic Crunch Cluster Whole Roasted 100 25 10 50 75 20 8 45 N/A N/A N/A N/A Booked Orders (In 1000's of Units) Outstanding Sales Contracts for next quarter. (Purchase orders from customers confirmed with committed delivery.) (Note that this is a contractual obligation to deliver!) Accepted Customer P.O.s Crunch Cluster Whole Roasted 0 0 0.8 0 Unit Sales Pricing The director of sales will increase the prices offall products by 3% across the board. This will increase the revenues ahead of inflation on their raw materials. The purchasing department has identified 2 critical ingredients that will be in limited supp The information below indicates the quantities contractually committed to the company b The availability of additional quantities cannot be relied upon and any quantities procurab Purchasing "did their homework" 2 years earlier with long term pricing forecasts and then This limited the materials increases to the numbers listed below, which is below the indus Limited Raw Materials Nuts Chocolate Pounds Available 100,000 80,000 Increase in Costs Direct Materials (All) 1.50% pricing forecasts and then negotiating longer term contracts with suppliers. The human resource database includes information on the average increse in wages for w Your HR wage and salary consultant suggests that you adjust the prior year's direct labor f the prior 3 years of increases. Raises have not been determined yet for this planning per CAUTION: Compute carry your results to 3 decimal places in % and DO NOT confuse the Year -3 Year -2 Year -1 Year 0 Time Frame 3 Yr Prior 2 Yr Prior 1 Prior Yr Current Yr Increase 1.500% 1.350% 2.000% Assume that the total Selling and Administrative costs remains the sa he average increse in wages for workers. (Averaged across the workforce.) just the prior year's direct labor figures upwards for based on the average of Prior YR Financial Summary Whole Units Sold Sales Revenues Variable Costs Direct materials Direct labor Variable manufacturing overhead Variable selling & administrative Contribution Margin Allocated Fixed Costs Manufacturing overhead Selling & Administrative Net Profit Net Profit Per Unit 118,050 $197,340.00 $83,100.00 $79,200.00 $6,330.00 $8,400.00 $177,030.00 $20,310.00 $34,907.84 $14,018.54 $48,926.38 ($28,616.38) ($0.24) Cluster 50,000 Crunch 15,000 Roasted 30,000 $160,000.00 $51,000.00 $108,500.00 $56,000.00 $40,000.00 $1,400.00 $1,800.00 $99,200.00 $14,400.00 $9,600.00 $360.00 $620.00 $24,980.00 $32,000.00 $13,000.00 $900.00 $1,200.00 $47,100.00 $60,800.00 $26,020.00 $61,400.00 $14,785.19 $5,937.55 $20,722.74 $4,435.56 $1,781.26 $6,216.82 $8,871.12 $3,562.53 $12,433.64 $40,077.26 $19,803.18 $48,966.36 $0.80 $1.32 $1.63 Total 213,051 $516,840.00 $185,500.00 $141,800.00 $8,990.00 $12,020.00 $348,310.00 $63,000.00 $25,300.00 $88,299.59 $80,230.41 CONTRIBUTION MARGIN OBJECTIVE Whole Quantity Cluster Crunch Roasted Total Units MFG