Excel Data Analysis for Decision Optimization -- 2
Budget: £10 – £15 GBP
ssignment Overview
The assignment involves optimizing the production and supply chain decisions for Red Brand Canners, a tomato processing company. The key tasks include:
Model Building and Optimization:
Basic Model: Use Excel Solver to build a model to determine the optimal production quantities for whole canned tomatoes, tomato juice, and tomato paste.
Grade A Tomatoes: Modify the model to incorporate the option of purchasing additional Grade A tomatoes and determine the optimal allocation.
Scenario and Sensitivity Analysis:
Advertising Impact: Use sensitivity analysis to decide which product's demand should be increased through advertising and determine the potential value of such a campaign.
B Tomatoes: Evaluate the offer of additional B tomatoes and make recommendations based on sensitivity analysis.
Decision Making Under Uncertainty:
Yearly Variations: Analyze scenarios for different weather years (sunny, normal, poor) to decide the optimal quantity of tomatoes to purchase.
Scenario Analysis: Perform a scenario analysis based on probabilities of different crop quality years and recommend the optimal purchase strategy.
Skills Required
To successfully complete this assignment, the following skills are needed:
Data Analysis and Modeling: Proficiency in Excel, specifically in using Excel Solver for optimization problems.
Mathematical and Analytical Skills: Ability to develop algebraic models and perform sensitivity and scenario analyses.
Critical Thinking and Decision Making: Ability to interpret data, make recommendations, and assess risks under uncertainty.
Business Acumen: Understanding of supply chain management, cost analysis, and marketing strategies in a production environment.
This concise summary highlights the essential components of the assignment, making it easy for someone to understand what needs to be done and the skills required to complete it effectively.
Complete the exercises in the document below using the Excel template provided and submit your solutions in a single pdf file. You may answer the questions in question-answer format (so no fancy business reports here), with figures and tables as needed. Please do not submit any Excel files, but you need to clearly explain what you did to get to your answers.
The assignment involves optimizing the production and supply chain decisions for Red Brand Canners, a tomato processing company. The key tasks include:
Model Building and Optimization:
Basic Model: Use Excel Solver to build a model to determine the optimal production quantities for whole canned tomatoes, tomato juice, and tomato paste.
Grade A Tomatoes: Modify the model to incorporate the option of purchasing additional Grade A tomatoes and determine the optimal allocation.
Scenario and Sensitivity Analysis:
Advertising Impact: Use sensitivity analysis to decide which product's demand should be increased through advertising and determine the potential value of such a campaign.
B Tomatoes: Evaluate the offer of additional B tomatoes and make recommendations based on sensitivity analysis.
Decision Making Under Uncertainty:
Yearly Variations: Analyze scenarios for different weather years (sunny, normal, poor) to decide the optimal quantity of tomatoes to purchase.
Scenario Analysis: Perform a scenario analysis based on probabilities of different crop quality years and recommend the optimal purchase strategy.
Skills Required
To successfully complete this assignment, the following skills are needed:
Data Analysis and Modeling: Proficiency in Excel, specifically in using Excel Solver for optimization problems.
Mathematical and Analytical Skills: Ability to develop algebraic models and perform sensitivity and scenario analyses.
Critical Thinking and Decision Making: Ability to interpret data, make recommendations, and assess risks under uncertainty.
Business Acumen: Understanding of supply chain management, cost analysis, and marketing strategies in a production environment.
This concise summary highlights the essential components of the assignment, making it easy for someone to understand what needs to be done and the skills required to complete it effectively.
Complete the exercises in the document below using the Excel template provided and submit your solutions in a single pdf file. You may answer the questions in question-answer format (so no fancy business reports here), with figures and tables as needed. Please do not submit any Excel files, but you need to clearly explain what you did to get to your answers.