Overview: Data analysis is an essential part of informing business decisions and helps provide a solid rationale to gain stakeholder buy-in. Knowing what data toanalyze is also important.

Prompt: You will now calculate and analyze data based on information provided in the scenario to inform and support the decisions necessary to resolve the organizational challenges. Your analysis will explore the production schedule and vendor optimization by running a linear programming model and sensitivity analysis. You will submit this section as an Excel spreadsheet to show your programming. Please refer to your syllabus for instructions on how to access your case study.

Specifically, you must address the critical elements listed below.

II. Data Analysis:

1. Calculate the labor stages and material requirements for each product.

2. Based on the results of your calculation, develop a summary table for linear programming analysis.

3. Next, calculate the production schedule, determining what will be produced, the quantity, and when the output will be produced.

4. Conduct a sensitivity analysis on the cost of the products using a decision tree.

5. Generate labeled graphs to demonstrate changes, if any, in product cost.

Transcribed Image Text:

Use in Exhibit 1: Product Production Requirements Criteria/Vender Labor (hour/carton) Stage 1 Stage 2 Material (pounds/carton) Water O Scents and Colors Emulsifiers Face Cream, Shift: Body Cream, Shift 1 Hand Cream Shift s Cost Labor 11 Labor 21 Laber 12 Laber 22 Water Ols Data Scents/Colors Emalifiers Amount (FC) Amount (c) Amount (HC) A Data Data Item Labor Stage 1 Labor Stage 2 Water als Scents/Colors Emaliers Total Item Labor Stage 1 Labor Stage 2 Water Oils Face Cream Body Cream Scentu/Color Emulsiders Total Item Labor Stage 1 Labor Stage 2 Water Oils 1.5 0.8 Scents/Colars Emulsifers Total 8.0 1.0 0.5 0.5 A. Labor Stages-Material Req 1.8 10 6.0 3.0 0.3 0.7 SHIFT DETAILS TRESOURCE Required 1.50 0.80 8.00 1.00 0.50 0.50 Resource Required Face Cream Shift 1 1.80 1.00 6.00 3.00 0.30 0.70 Resource Required Hand Cream 1.00 0.50 7.00 2.00 0.40 0.60 1.0 0.5 7.0 2.0 0.4 0.6 B. Summary Table 5/Unit Cost (5) 0.00 5/Unit Cost (5) 0.00 5/Unit Cost (51 0.00 C. Production Schedule Face Cream, Shift 2 Body Cream, Shift 2 Hand Cream, Shift 2 Item Labor Stage 1 Laber Stage 2 Water Oils A. Labor Stages-Material Reg B. Summary Table Scents/Colors Emulsifiers Total Item Labor Stage 1 Labor Stage 2 Water Ols Scents/Colors Emulsifiers Total Item Labor Stage 1 Laber Stage 2 Water Dils be A. Labor Stages - Material Req B. Summary Table C. Production Schedule Scents/Colon Emulsifiers Total D. Decision Tree C Production Schedule Resource Required S/Unit 1.50 0.80 8.00 1.00 0.50 0.50 Resource Required 18 1.80 1.00 6.00 3.00 0.30 0.70 Resource Required Linear Programming Model Formulation ( production variable, 11 constraints, minimization problem) Face Cream, Face Cream, Body Cream, Hand Cream, Hand Subcontracted Shift Face Cream, Face Cream, Body Cream, Body Cream, Subcontracted Shit Shift 2 shit 2 1.00 0.50 7.00 2.00 0.40 0.60 S/Unit $/Unit D. Decision Tree Cream D. Decision Tree mis S s S 3 5 Is s Is D 2 12 E. Graphs Cost (5) 0.00 Cost (5) 0.00 Cost (5) E. Graphs 0.00 E. Graphs + + + Use in Exhibit 1: Product Production Requirements Criteria/Vender Labor (hour/carton) Stage 1 Stage 2 Material (pounds/carton) Water O Scents and Colors Emulsifiers Face Cream, Shift: Body Cream, Shift 1 Hand Cream Shift s Cost Labor 11 Labor 21 Laber 12 Laber 22 Water Ols Data Scents/Colors Emalifiers Amount (FC) Amount (c) Amount (HC) A Data Data Item Labor Stage 1 Labor Stage 2 Water als Scents/Colors Emaliers Total Item Labor Stage 1 Labor Stage 2 Water Oils Face Cream Body Cream Scentu/Color Emulsiders Total Item Labor Stage 1 Labor Stage 2 Water Oils 1.5 0.8 Scents/Colars Emulsifers Total 8.0 1.0 0.5 0.5 A. Labor Stages-Material Req 1.8 10 6.0 3.0 0.3 0.7 SHIFT DETAILS TRESOURCE Required 1.50 0.80 8.00 1.00 0.50 0.50 Resource Required Face Cream Shift 1 1.80 1.00 6.00 3.00 0.30 0.70 Resource Required Hand Cream 1.00 0.50 7.00 2.00 0.40 0.60 1.0 0.5 7.0 2.0 0.4 0.6 B. Summary Table 5/Unit Cost (5) 0.00 5/Unit Cost (5) 0.00 5/Unit Cost (51 0.00 C. Production Schedule Face Cream, Shift 2 Body Cream, Shift 2 Hand Cream, Shift 2 Item Labor Stage 1 Laber Stage 2 Water Oils A. Labor Stages-Material Reg B. Summary Table Scents/Colors Emulsifiers Total Item Labor Stage 1 Labor Stage 2 Water Ols Scents/Colors Emulsifiers Total Item Labor Stage 1 Laber Stage 2 Water Dils be A. Labor Stages - Material Req B. Summary Table C. Production Schedule Scents/Colon Emulsifiers Total D. Decision Tree C Production Schedule Resource Required S/Unit 1.50 0.80 8.00 1.00 0.50 0.50 Resource Required 18 1.80 1.00 6.00 3.00 0.30 0.70 Resource Required Linear Programming Model Formulation ( production variable, 11 constraints, minimization problem) Face Cream, Face Cream, Body Cream, Hand Cream, Hand Subcontracted Shift Face Cream, Face Cream, Body Cream, Body Cream, Subcontracted Shit Shift 2 shit 2 1.00 0.50 7.00 2.00 0.40 0.60 S/Unit $/Unit D. Decision Tree Cream D. Decision Tree mis S s S 3 5 Is s Is D 2 12 E. Graphs Cost (5) 0.00 Cost (5) 0.00 Cost (5) E. Graphs 0.00 E. Graphs + + +

Answer rating: 100% (QA)

Calculate the labor stages and material requirements for each product To calculate the labor stage... View the full answer