Must using Excel Solver and Scenario Manager

It is the new season of the "Masterchef Australia" 2 TV programme. In the grand
opening of the first episode, audiences are presented with a one-time-in-history-only
re-match cooking bout between Julie (last season's winner) and Poh (the runner up).
This time however, both contestants cook for a buffet (i.e. cook in bulk) as opposed to
à la carte (according to menu). Poh has been meditating on her narrow loss to Julie
last season and would really like a second-go at the title. Through Ken, she learns that
the project management students undertaking PMGT5887 are extremely smart and are
technical experts in the problem area of optimisation. She approaches you and begs to
provide her with technical advice for her cooking project. Based on her experience
last season, she knows various facts that will help her to win this season (although the
final call is made by the taste-buds of the judges! You need to satisfy their taste to
rank high). For instance, she knows the taste preferences of the three judges - Matt,
George and Gary; she also knows the range of dishes she can cook to surprise the
taste buds of the judges, the stress she is able to handle in the competition, and the
presentation impact of each dish which she artistically decorates. However, Poh is
also aware of other factors in the show that will affect her win such as cooking time
and TV ratings. Even better, based on last season's experience, she knows Julie inside
out and believes has all the information she needs to defeat Julie. All she needs to
know now is the combination of dishes it takes to satisfy a variety of her objectives in
the show, given the set amount of money to spend. Hopefully, you can assist Poh in
setting up a cooking plan which will ultimately lead to her winning in this new season.
All the best!
For the purpose of the assignment, you will need to use Scenario Manager and Excel
Solver to provide the advice they need.
General Information and Constraints
Poh has been given a fixed amount of $1,000 to spend. Being a conservative, Poh
would like to spend as less as possible and will only spend excessively if necessary.
From the standpoint of occupation health and safety (OHS), due to the intensive stress
and pressure undergone by contestants in the previous season (Julie's mental
breakdown, Justine's anguish, etc.), OHS groups have strongly protested against the
show and have decided to boycott the show unless the mental state of the contestants
are protected. To get the show going and keep the (annoying) OHS groups happy, the
directors of the programme have secretly installed a stress detector device that
monitors the stress levels of all contestants. The maximum stress level units have been
set to 100 units (anything above this causes contestants to go totally berserk &
mental!). Furthermore, the directors have also imposed these competition rules: (1) all
buffet dishes must total to at least 5,000 calories (to sustain hunger) for the
perpetually hungry judges and (2) that all the quantity of dishes totalled up must be
able to feed at least 150 people. Poh has listed all the information that she knows in
the table below. She brings this information to you and asks you to help her further.
She specifically informs you that certain items (cooking time, presentation impact,
TV fun & impact factor & the judges' ratings) are not affected by the planned
quantity of dish items.
2 This is a hypothetical case study - characters and concepts based on "MasterChef Australia"
3
Planned
Amount Dish Items Type
No. of
people
per
serve
Calories
per serve
Cooking
time
(minutes)
Presentation
Impact *
Cost per
serve
TV Fun
&
Impact
Factor *
Stress
Level per
serve *
Taste
Rating:
Judge
Gary *
Taste
Rating:
Judge
George *
Taste
Rating:
Judge
Matt *
Spring Roll Appetizer 1 320 40 2 $10 1 3 4 2 2
Fish Cakes Appetizer 1 260 35 1 $15 5 4 5 5 3
Finger Chips Appetizer 4 295 6 2 $10 3 2 4 3 5
Fish Fingers Appetizer 4 245 5 2 $10 3 3 2 1 5
Tom Yum Soup Appetizer 1 160 30 3 $20 4 5 2 2 3
Tomato Soup Appetizer 1 160 20 3 $15 4 2 3 5 2
Sweet Corn Soup Appetizer 1 180 20 3 $25 3 3 2 2 2
Chicken nibblets Appetizer 5 195 10 2 $15 4 4 3 1 1
Celery, Carrots & Dip Appetizer 4 150 5 1 $10 1 1 3 5 1
Total
Sage & Garlic Chicken Main 4 320 30 5 $45 5 4 5 1 5
Hainanese Chicken Rice Main 2 200 25 4 $30 5 3 1 4 5
Lamb Brain Cereal Main 4 200 30 5 $35 5 2 3 5 1
Smoked Chicken Pasta Main 1 350 40 5 $40 5 4 4 3 5
Lamb Roganjosh Main 4 300 50 3 $45 1 2 3 3 2
Stir Fry Vegetables Main 4 125 10 1 $10 2 2 5 2 2
Hospital Grade Steam Vegies Main 4 50 7 1 $5 3 1 3 3 1
BBQ Beef with shredded Fish Main 2 300 15 3 $40 5 5 1 4 1
Vanilla flavoured Lamb Chops Main 2 260 30 4 $60 5 5 5 5 3
Tandoori Chicken Main 5 275 60 4 $45 5 5 4 2 5
Thai style Beef BBQ Main 2 285 45 4 $45 5 3 4 4 4
Steamed Potato with salt Main 5 100 10 1 $5 2 1 3 5 1
Total
Braised Tomatoes with Chocolate Mousse Dessert 1 300 20 5 $50 5 2 2 2 2
Aria Chocolate Tart Dessert 1 250 20 5 $55 5 4 1 3 2
Vanilla Pannacotta with Macarons Dessert 1 400 20 5 $35 2 5 5 2 5
Strawberry Tart Dessert 1 250 10 4 $20 3 5 2 5 5
Crepe Raspberry Dessert 1 260 10 4 $25 5 3 2 4 2
Roasted Salmon&#8208;flavour ice&#8208;cream Dessert 1 185 25 1 $20 3 3 1 1 2
Total
Items with a * use a scale of 1 to 5 (where 1=very low/bad; 5=very high/good)
4
Your Tasks
In this assignment, you will model a professional plan (with the help of spreadsheets)
for Poh's decision making purposes. Given the above plan produced by Poh, you are
required to carry out the following 3 important integrative tasks:
Task 1:
You need to develop an optimisation model to help Poh optimise her cooking plans to
achieve her goal. Poh would like to develop a number of scenarios based on different
optimisation objectives:
1. Minimising cost
2. Minimising stress
3. Minimising calories
4. Maximising number of people served
It is very important that you need to develop and state clearly the objective function
and the constraints based on the information given for each objective. Based on the
optimisation model, you need to implement the optimisation model using solver to
help Poh optimise her initial plan based on the tasks above.
Task 2:
Based on last season's experience, Poh has knowledge of Julie as a conservative when
it comes to spending money. She reckons that Julie will only spend lesser than half of
the entire budget given. She estimates this amount to be $450. Poh also knows that
Julie is extremely bad in time-management (she admitted it herself in the show!) and
because of this comes stress and nothing but stress. She believes Julie's stress level
will be at 80 units, which is quite high. Also, knowing Julie's recipe for home-style
meals, she thinks her style of cooking will be around the 8000 calories mark that
caters for 370 people only. While Poh is confident that this time, she can win Julie in
all aspects, Poh is quite keen to prove herself as a better chef than Julie on two
grounds: that she can cook for more at a much lesser cost. Based on this information,
you need to revise your assessment of the cooking plans for Poh.
Finally, another primary sub-task is whether Poh can refine/improve her model further
without losing sight of other important factors for the show. You may make various
assumptions to improve Poh's plan further. You can be very creative about this and
make modifications to the plan developed just above. You also need to provide
justifications for your recommendations.
Task 3:
Now that task 2 is over and Julie is finally beaten, Poh no longer needs to consider
Julie's details in her plan and wants to focus on these important objectives instead.
While still keeping the general constraints of the show, Poh wants to see the outcomes
of the following objectives:
1. Maximising Presentation Impact
2. Minimising Cooking Time
3. Maximising TV Impact
4. Maximising Total of Judges' Ratings
5
The Deliverables
The presentation of your report (written in a document file (e.g. word) with excel
components pasted (e.g. tables)) should use the following structure:
Deliverable Description Marks
Allocated
Cover Page Use Cover page from Course outline
1. Executive
Summary
In one page, the first paragraph should summarise the what,
why, who and how of this project.
Then summarise the results and findings of tasks 1, 2 and 3 in
terms of the decision variables and key constraints. Write this
as though you are writing for a business client (i.e. Poh) for
whom you are performing the analysis. Close the summary by
briefly listing and describing the other sections in your report.
5
2. Optimisation
Model for Task
1
For Task 1, you should state clearly the objective function and
constraints of the each optimisation model (Hint: follow the
steps discussed in the lectures). Then you need to provide a
summary of your key findings in terms of the decision
variables and key constraints for each objective function
analysed.
20
3. Optimisation
Model for Task
2
For Task 2, you need to state the process (of running various
optimisation models) you undertook to beat Julie's plan. You
then need to justify the plan that you have chosen (as the best
plan to defeat Julie) and make a comparative analysis as well.
You may need to show certain tables of relevant information
here. Again, summarise your key findings and results in terms
of the decision variables and key constraints. You also need to
highlight adjustments you made as you refined the model and
provide a summary here.
30
4. Optimisation
Model for Task
3
For Task 3, state the process of developing each optimisation
model and how it was done in solver. Summarise each
optimisation model in terms of decision variables and key
constraints. Recommend one optimisation model which you
think will help Poh steal and win this Masterchef Australia
Buffet style Project once and for all.
30
5. Appendix This section is where you include your WHAT-IF analyses
using scenario manager to showcase each objective function
as a scenario for Poh to decide what's best for her. (Hint: you
first need to figure out all the possible scenarios for the
decision variables based on different optimisation models).
10
6. Formatting &
Writing
Good report writing, Formatting of excel components (e.g.
tables, etc), formatting of report; professionalism counts.
5

Purchase Solution

Solution provided by:

Education

Recent Feedback

"Your explanation to the answers were very helpful."

"What does 1 and 0 means in the repair column?"

"Went through all of the formulas, excellent work! This really helped me!"

"try others as well please"

"Thank you, this helped a lot. I was not sure how to plug in those numbers to a formula. This was a great help. Now I have to figure out how to explain cost of capital is used in net present value analysis, and how cost of capital is used in net present value analysis. This stuff gets confusing."