Business Management 2321 – Group Exercise
2024 Spring Semester
Due Monday, April 22nd at 11:59pm
. File Name (1%) – Save your solution as “BusOBA 2321 – Group ” – inserting your 3-digit group number for the “___”
. Cover Page (4%) – add all team members names and dot numbers as indicated
o List names in alphabetical order by last name
o No more than 4 members to a group; your assigned group is available on Carmen
o If a member does not contribute, do not include their name on the cover sheet
o If a team member is unresponsive or inaccessible through Monday, April 8th, let your instructor know and that person will be dropped from your group.
o Individual submissions will be accepted but will incur a 20% deduction from the grade earned, unless otherwise discussed with your professor.
. There are 5 problems: a decision-making problem, a decision tree, an optimization and sensitivity analysis, a network model and a simulation.
. Submit the worksheet via Carmen.
o 1 submission per group using the provided template
o Your submission includes 14 total worksheets
. Cover sheet – 1 sheet
. Decision making - 1 sheet
. Decision tree – 2 sheets
. “Problem 2 Tree” requires no work but must be included
. Optimization/Sensitivity – 4 sheets
. “Problem 3 Table” requires no work but must be included
. Transshipment – 1 sheet
. Simulation – 2 sheets
o Up to 20% will be deducted for “non-professional” reports – neatness and formatting count.
o Incomplete assignments will receive a grade of zero.
o Should the grader feel a poor effort was made the entire assignment will receive a zero.
o NOTE – With the extension from 4/15 to 4/22, late assignments will NOT BE ACCEPTED!
. Solver MUST be complete (i.e. executable) for problem 3 and 4 in your final submission or you will receive a ZERO for each problem that is missing the solver details!
If group members used Excel Online to share the excel workbook with group members or if a problem was copied from another workbook, the solver program may not transfer with the copied/shared files. Each group is responsible for checking its final submission to ensure that solver is properly filled out for each problem. Should solver be missing or not run for a problem the group will receive a zero for the problem. It is advised to complete the group exercise in Teams rather than trying to combine various files.
Some advice for completing the Group Exercise
. The Group Exercise is designed to be COMPREHENSIVE – to include almost everything covered in the course.
. The Group Exercise is designed to IMPROVE YOUR ABILITY TO USE THE COURSE MATERIAL, to solve problems, to apply simple logic and algebraic principles in the solution of business issues.
. The Group Exercise provides all the information you require to solve the problem, but not always in a straightforward array of data – you may have to THINK THROUGH THE PROBLEM.
. The Group Exercise includes somewhat complex problems which are designed to be completed through DISCUSSION AND COLLABORATION.
. The Group Exercise is designed to PREPARE YOU FOR THE FINAL EXAM.
. The Group Exercise is not designed as a DIVIDE AND CONQUER assignment – assigning individual group members to an individual problem, then assembling these individual efforts into a finished project will not be in any group member’s best interest.
Work together, work early. You will be given chances to work with your group during lecture and recitation, where instructors and TAs are available to help you. During these sessions, and during office hours, we will easily recognize those who have not put any work or thought into the assignment and we will defer helping you until we feel you have put work and thought into the project.
And, in case you missed our earlier subtle hints:
Problem 1 – Decision Making – Columbus Housing (15%)
Using the information provided, create a payoff table for part 1 in the part 1 section of the ‘Problem 1’ sheet in the provided Excel workbook. After that, complete parts 2 through 4 and place your answers in their appropriate sections of the problem 1 sheet.
Part 1 - Using the following problem statement and information, create a payoff table
Cities across the United States have been facing a housing shortage. Central Ohio governments are preparing for a surge in housing demand associated with the new semiconductor factory Intel is constructing on the east side. The Mayor of Columbus, Dewy Cheatum, has tasked the Columbus Metropolitan Housing Authority (CMHA) to develop additional public housing using federal funding from the Biden-Harris administration's Housing Supply Action Plan.
The CMHA has recently acquired a large tract of land in the Brewery District near the Scioto Audubon Metro Park and has the option of building several different types of housing on the site, from single family to high-rises. Zoning regulations in the district only allow the CMHA to build one type of housing on the site (housing types cannot be mixed). However, the type of housing must consider the possible future states of the housing market in Columbus and be designed to maximize CMHAs profit so that they can continue to build more public housing projects for the people of Columbus.
Ava, one of the directors at CMHA, has tasked your team of trusted interns to help analyze the various market states and determine which housing type should be built on the land.
Your team receives information about each housing type from Guy, the Head of Development at CMHA. The information includes the total cost to build, the number of units built, the average rent per unit, and the average upkeep per unit. The housing authority only receives rent when an apartment is occupied, but CMHA will pay upkeep on a unit regardless of it being occupied or not. Additionally, federal and state funding will cover 80% of the Total Cost to Build, with CMHA covering the remaining 20%.
Elan, the head of Market Analysis provides your team with four potential market outcomes for the next five years, specifically the occupancy for units (i.e. number of occupied units based on each market condition).
Director Ava tasks your team to determine which housing type will be most profitable for CMHA over the coming 5 years by putting together a payoff table using the information provided by Guy and Elan.
Hint: Using the empty columns/rows surrounding areas of the template to create working tables may help group members understand how your solutions were developed, This is NOT required – only the final product will be graded.
Part 2 - Use the following Payoff Table (not the one that you created in part 1) to determine the proper decision using Maximax, Maximin, and EMV (using equally likely and table probabilities). Use the following table to create a regret table.
Part 3 - Use the following regret table (not the one that you created in part 2) to determine the proper decision using Minimax Regret and EOL (using equally likely and table probabilities).
Part 4 - Using the following modified Payoff Table create a graph of projected costs versus the probability that
the economy is in a expansion (use proper formatting, labeling, and titles – see MBE 3). Find the equation for
EVwPI and the value of EVPI at p(Expansion) = 55%, find the indifference points between Townhomes & Low-rise Apartments and Low-rise & Mid-rise Apartments. Identify which housing type options can be eliminated from the decision-making process.
Problem 2 – Decision Tree – Late Night Snack (15%)
You and your five friends just got 26th place in Trivia at Leo’s on the Alley . To combat the incredible post loss
embarrassment the five of you decide to eat something! One of your friends, an Analytics major, has derived the
probabilities and payoffs for each option, with the payoffs calculated using “ Units of fun” or Utils for short. This friend has also created a decision tree to use when determining the best course of action for you and your friends.
Below is a list of the six food choices. Of the six choices, two of them require you to door dash (Swensons and Sonic), three of them require you to pick-up the food (McDonalds, Taco Bell, and Buckeye Pizza), and 1 of them can be made from the comfort of your lovely college home (Ramen Noodles). You and your five friends decided to rank the six food choices, 1 being the least preferred and 6 being the most preferred. The rankings are shown below:
The calculation of your initial utils will be based on the total number of votes for each food choice and whether or not your door dasher eats your food, you have to wait longer than 30 minutes for your food, or you drop your food while on a lime scooter.
. If the Door Dasher eats your food and you wait longer than 30 minutes for your food, you will get 40% of the total votes for that choice.
. If the Door Dasher eats your food and you don’t wait 30 minutes you will get 15% of the total votes for that choice.
o If the Door Dasher does not eat your food and all is well, you will receive the full utils for that option.
. If you drop your food while on the lime scooter AND wait longer than 30 minutes for your food, you will get 10% of the total votes for that choice.
. If you drop your food while on the lime scooter, you will receive 25% of the total votes for that choice.
. If you burn your Ramen Noodles, you will receive 7% of the total votes for that choice.
The wait time at each choice can significantly impact your happiness. In addition, uncertain door dashing conditions and the chance of dropping your food or burning your noodles can be extremely frustrating. The probability of long waits, door dashing condition, dropping your food, and burning your noodles are listed below:
For pick-up choices, you will be able to decide between walking or zooming on a lime scooter. If you decide to walk, this will reduce your number of utils. Walking and waiting longer than 30 minutes for your food will reduce your utils by 25%. Walking and waiting less than 30 minutes for your food will reduce your utils by 7%.
The figure on the ‘Problem 2 Tree’ sheet in the template is the decision tree developed by your Analytics friend. You and your friends need to determine the Expected Value (in Utils) for each node and EMV cell in the decision tree, as well as determine the proper course(s) of action. Place your answers in the Excel answer sheet “ Problem 2” .
You are NOT required to complete the physical decision tree on the sheet labeled “Problem 2 Tree.” However, we highly recommend you do!
Problem 3 – Optimization and Sensitivity Analysis – Buckeye Systems (30%)
Using the information provided, create a linear program using solver in the ‘Problem 3 - Part 1’ sheet in the provided Excel workbook. Then create a sensitivity analysis using that optimization model to answer the questions on the problem 3 – part 3 answers sheet
Part 1 – Construct an optimization model using the information and the table provided on the next page
Buckeye Systems is a systems integrator located in Columbus, Ohio. Each year they make wholesale purchasing
arrangements with manufacturers to acquire electronic components that they then assemble into computers for retail sale or custom orders. The order deadline for this year is rapidly approaching and they just received an unexpected order from THE Ohio State University for new lab computers. Even worse is that their entire acquisistions team went to Miami for
Spring Break and have decided that they will not be returning to Ohio, leaving no one at Buckeye Systems able to identify how much of each component should be ordered.
You and your team have been brought in as consultants to identify how much of each component should be ordered
before the deadline passes. The CEO has briefed your team on whats available to be purchased and the constraints
facing Buckeye Systems, he also informs you that he is trying to maximize annual profit so that he can buy a new yacht.
While the CEO briefed you on the constraints, your team took the following notes:
. The warehouse currently has 75 assembley workers, who each work 40 hours per week, 46 weeks a year
. Some of the computers built have software deals, in which the computer is sold with extra software installed, each computer sold with this additional software bundled in brings Buckeye Systems an extra $150 on top of the
computers sale price.
. At least 5,000 Desktops must be assembled, 10,000 Laptops, and 10,000 Minis
. There is a custom order from THE Ohio State University for exactly 10,000 new Lab PCs
. To spread risk and not oversaturate the market, some limits are set on high-end and ultra-high-end computers
o At least 40% of total profit must come from computers not specified as high and ultra-high
o No more than 10% of total desktops and laptops sold can be specified as high and ultra-high
. Lastly, at least 50% more low-end Mini computers need to be made than low-mid-end Mini computers
The table provided shows various statistics about each computer Buckeye Systems builds, with each ‘X’ indicating that the given component is used in the assembely of that particular computer model.
NOTE: Normally a problem like this would include an integer constraint as its not possible to build a partial computer using partial components. However, you can not generate a solver sensitivty report with an integer constraint, so do not include one in your problem!
Part 2 – Using the optimization model from part 1, run a sensetivity analysis using solver, rename the sensetivity report to ‘Problem 3 – Part 2’
Part 3 – Using the senesetivity report from part 2, answer the questions on the ‘Problem 3 – Part 3’ sheet
Problem 4 – Transshipment – Grandma’s Cheese Shed (15%)
Your team has recently accepted a consulting role at Grandma’s Cheese Shed and has been tasked with helping their
logistics department. Recently, Grandma’s Cheese Shed has added two new cheeses as part of their “Grandmas Pack’n HEAT" line of spicy foods, these two cheeses are ‘Carolina Reaper’ Cheddar and ‘ Heat is Real’ Muenster. These new
cheese varieties have been so popular, Grandma’s Cheese Shed has been struggling to keep them in stock. Your team is tasked with producing a transportation model to find the lowest possible daily cost of shipping these new spicy cheeses to the many cheese sheds scattered across Ohio.
The head of the logistics department has provided you with some information to help your team build the transportation
model. First, you are informed that while there are no capacity constraints associated with the company's two
warehouses in Columbus and Canton, the connections between the various cheese factories, warehouses, and cheese sheds do have a limited capacity. The refrigerated trucks that will be used to ship the spicy cheeses must share space with other products and therefore, each truck can only carry 1000 blocks of spicy cheese at a time. You are also told that each facility connection has one truck assigned to it that runs twice a day, with two deliveries being made. Second, you are informed about an agreement that exists between Grandma’s Cheese Shed and Trader Terry's, a large grocery store chain in the Midwest. This agreement allows Grandma to sell her products in Trader Terry's if Grandma delivers the
products to Trader Terry's Distribution center. For each block of cheese delivered to Trader Terry's Distribution Center, Grandma’s Cheese Shed will earn 50 cents. Furthermore, you are informed that 15% of total spicy cheese sales are for the ‘Heat is Real’ Muenster. Lastly, its not possible for Grandma to sell a partial block of cheese! Be sure to make this an integer program.
In the template, the following variables represent the respective cheeses:
A= ‘Carolina Reaper’ Cheddar
B= ‘Heat is Real’ Muenster
The decision variable will work like so: X ‘product type’ - ‘start node’ - ‘end node’