Developing an Excel model and word document solution. Business Decision Modeling using Excel.

Swiss Chocolate Company

The Swiss Chocolate Company (SCC) makes four different products from macadamia nuts grown in Switzerland: chocolate-coated whole nuts (Whole), chocolate-coated nut clusters (Clusters), chocolate-coated nut crunch bars (Crunch), and plain roasted nuts (Roasted). The company is not able to keep up with demand for these products. However, increasing raw materials prices and foreign competition are forcing SCC to watch its margins to ensure it is operating in the most efficient manner possible. To meet marketing demands for the coming week, SCC needs to produce at least 1,000 pounds of the Whole product, between 400 and 500 of the Cluster product, no more than 150 pounds of the Crunch product, and no more than 200 pounds of the Roasted product.

Each pound of the Whole, Cluster, Crunch, and Roasted product contains, respectively, 60%, 40%, 20% and 100% macadamia nuts with the remaining weight made up of chocolate coating. The company has 1,100 pounds of macadamia nuts and 800 pounds of chocolate available for use in the next week. The various products are made using four different machines that hull the nuts, roast the nuts, coat the nuts in chocolate (if needed), and package the product. The following table summarizes the time required by each product on each machine. Each machine has 60 hours of time available in the coming week.

	Minutes Required Per Pound
Machine	Whole	Cluster	Crunch	Roasted
Hulling	1.00	1.00	1.00	1.00
Roasting	2.00	1.50	1.00	1.75
Coating	1.00	0.70	0.20	0.00
Packaging	2.50	1.60	1.25	1.00

The controller recently presented management with the following financial summary of SCC average weekly operations over the past quarter (next page). From this report, the controller is arguing that the company should cease producing its Cluster and Crunch products.

	Whole	Cluster	Crunch	Roasted	Total
Sales Revenue	$5,304	$1,800	$510	$925	$8,539
Variable Costs
Direct Materials	$1,331	$560	$144	$320	$2,355
Direct Labor	$1,092	$400	$96	$130	$1,718
Manufacturing	$333	$140	$36	$90	$599
Selling & Administrative	$540	$180	$62	$120	$902
Allocated Fixed Costs
Manufacturing Overhead	$688	$331	$99	$132	$1,250
Selling & Admin. Overhead	$578	$278	$83	$111	$1,050
Net Profit	$742	-$88	-$11	$22	$665
Pounds Sold	1,040	500	150	200	1,890
Net Profit Per Pound	$0.71	-$0.18	-$0.07	$0.11	$0.35

Tasks:

This assignment will involve two pieces: an Excel workbook and a Word document. The tasks outlined below are in order which they should be performed, and you will switch between each file to complete this assignment.

This report should be written in such a way that a decision maker could read the report and understand everything without having to open the Excel file. As such, please include screenshots as requested (the screenshots should be cropped to facilitate ease of understanding for the reader). Additionally, your answers to these questions should consist of complete sentences, not simple one word or one number answers. Your writing should be professional and grammatically correct. 10 points are allocated for clarity, grammar, and other writing and layout issues in the Word document. The remaining 90 points are allocated in the tasks below:

1. [MS Word, 5 points] Before building the Linear Optimization model, do you agree with the controller’s recommendation to cease production of the Cluster and Crunch products? Why or why not?
2. [MS Excel, 20 points] Build, but do NOT solve, your Linear Optimization model. Two hints:
   • Given that the Allocated Fixed Costs are assigned based on the proportion of total pounds produced, you should not consider these in your Optimization/Solver model as the values will change based on the optimal solution, and the necessary calculations would contain nonlinear aspects.
   • You will need to perform some intermediate calculations to find key values for use in the model, however, for the sake of clarity, these calculations should NOT appear on your Optimization model spreadsheet but can be included on a separate worksheet within your workbook and you can then copy/paste the values or use references in your model back to these results.
3. [MS Word, 20 points] Explain your Optimization Model. There are two different approaches you can take here: first, you can write the model out mathematically, but it should be clear what the equations represent; second, you can include a screenshot of the unsolved model (i.e. Decision Variable values are blank or zero) and explain the model in a more narrative manner. Using either approach, the reader should be clear what the model components (Decision Variables, Objective Function, and Constraints) are for this problem without having to open the Excel workbook for further examination.
4. [MS Excel Workbook, 5 points] Solve your Optimization model. Create both the Answer Report and Sensitivity Report for the optimal solution.
5. [MS Excel Workbook, 10 points] After finding your optimal solution, at the bottom of the model worksheet, perform the following additional work so that you can compare your results with controller’s information presented in the table above:
   • Allocate the two Fixed Costs proportionally across the four products based on the optimal production quantities.
   • Calculate the Net Profit for each of the four products, as well as the Total Net Profit.
   • Calculate the Net Profit Per Pound for each of the four products, as well as the average for all four products.
6. [MS Word, 30 points total, 5 points each] Paste screenshots of the optimal solution to the model (including the additional calculations from Task 5), the Answer Report, and the Sensitivity Report into your document. Answer the following questions about this information:
   1. If you could decrease the production of any product(s), which one(s) and why? Be precise with the numerical impact.
   2. If you could decrease the production of any product(s), which one(s) and why? Be precise with the numerical impact.
   3. Is the optimal solution limited by the raw materials? If you could acquire more of any raw material, which one would you acquire, how much would you acquire, and what is the value of these additional raw materials?
   4. Is the optimal solution limited by any of the machines? If you could add more time to any of the machines, to which machine would you add time, how much time would you add, and what is the value of this additional time?
   5. Management is considering raising the price of the Whole product by $0.25. Will this change the current optimal solution?
   6. Based on the optimal solution and your responses above, would your answer to the question in Task 1 be different now? Why or why not?

Notes:

• Each tab in your workbook should be clearly labeled and the order of the worksheets should flow logically. Your model should be clearly labeled, easy to understand, and follow the general guidelines for good spreadsheet design.
• Use at least 2 decimal places for any fractional value, both on your worksheets and in your written answers.
• Name your Excel workbook and Word document using your last name or project name.

Answer rating: 100% (QA)

Around the world there exist many programs and interventions developed to improve conditions in loca... View the full answer