Explore BrainMass

Explore BrainMass

    Must using Excel Solver and Scenario Manager

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    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"
    Amount Dish Items Type
    No. of
    per serve
    Impact *
    Cost per
    TV Fun
    Factor *
    Level per
    serve *
    Gary *
    George *
    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
    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
    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‐flavour ice‐cream Dessert 1 185 25 1 $20 3 3 1 1 2
    Items with a * use a scale of 1 to 5 (where 1=very low/bad; 5=very high/good)
    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
    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
    Cover Page Use Cover page from Course outline
    1. Executive
    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.
    2. Optimisation
    Model for Task
    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
    3. Optimisation
    Model for Task
    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.
    4. Optimisation
    Model for Task
    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.
    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).
    6. Formatting &
    Good report writing, Formatting of excel components (e.g.
    tables, etc), formatting of report; professionalism counts.

    © BrainMass Inc. brainmass.com October 1, 2020, 11:26 pm ad1c9bdddf


    Solution Summary

    The solution examines Excel Solver for a Scenario Manager.