[SOLVED] BUSOBA 2321 Group Exercise 2024 Autumn Semester Python

BUSOBA 2321 – Group Exercise

2024 Autumn Semester

Due Monday, December 2nd at 11:59pm

· File Name (1%) – Save your solution as “BusOBA 2321 – Group XXX” – inserting your 3-digit group number for the “XXX”  

· 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, November 18^th, 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. 

· Your submission includes 11 total worksheets: 

o Cover sheet – 1 sheet 

o Decision making – 1 sheet

o Decision tree – 2 sheets 

§ “Problem 2 Tree” requires no work but must be included. 

o Optimization/Sensitivity – 4 sheets 

§ “Problem 3 Table” requires no work but must be included. 

o Transshipment – 1 sheet 

o Simulation – 2 sheets 

· Up to 20% will be deducted for “non-professional” reports – neatness and formatting count. 

· Incomplete assignments will receive a grade of zero. 

· Should the grader feel a poor effort was made the entire assignment will receive a zero. 

· If the assignment is submitted late all members will receive a reduction of 33% per day. 

· 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 

o The Group Exercise is designed to be COMPREHENSIVE – to include almost everything covered in the course. 

o 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. 

o 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. 

o The Group Exercise includes somewhat complex problems which are designed to be completed through DISCUSSION AND COLLABORATION. 

o The Group Exercise is designed to PREPARE YOU FOR THE FINAL EXAM. 

o 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: 

Question 1 Decision Analysis

An automotive company, KSMG, plans to launch a new car model, Toyota Ralph4 in five trims—each designed to cater to different customer segments. Due to global supply chain disruptions and raw material shortages, Havi and Tessa  must decide which trim to focus production on for the next five years to maximize profitability and manage risks.

Each trim varies in production cost, customer demand, and supply chain dependence. Additionally, the company’s production capacity is limited to only one trim level for the next  years, as retooling for multiple trims would exceed budget and disrupt timelines.

The company is also affected by raw material costs ( lithium prices) and government incentives for electric vehicles (EVs). Your team must consider market conditions, production constraints, and demand fluctuations to recommend which trim to manufacture.

Market Conditions and Constraints:

Market analysts have provided four potential market scenarios, each influenced by factors such as consumer behavior, material costs, and government incentives.

· Material Costs:

o Lithium prices rise with increasing EV adoption and resource scarcity.

o IF the Lithium prices are moderate, it will cost $500/kg

o IF the Lithium prices are high, it will cost $1200/kg

o IF the Lithium prices are very high, it will cost $1600/kg

· Government Policy:

o EVs receive a $10,000 subsidy per unit, making them more competitive in certain markets.

Market Scenarios:

Scenario/conditions	Economic Downturn	Steady Recovery	Green Revolution	Raw Material Crisis
Lithium Prices	Moderate	Moderate	High	Very High
Government Subsidy	No	No	Yes	No
Demand Shift	Strong preference for Base and Mid-tier	Balanced demand across all trims	High demand for EVs and Premium trims	Limited capacity for EVs, focus on Base

Trim Demand per Market Scenario:

Scenario/car trim	Economic Downturn	Steady Recovery	Green Revolution	Raw Material Crisis
Base Model	8000	6000	3000	9500
Mid-Tier	5000	5000	3500	6000
Premium	2000	3000	4500	1000
Luxury	500	1500	2000	200
Electric (EV)	300	1500	3000	100

 

Trim/Cost	Production Cost per Unit	MSRP (Selling Price)	Annual Production Capacity	Lithium Usage per Car in kg
Base Model	$20,000	$25,000	9000	0
Mid-Tier	$28,000	$35,000	5000	1
Premium	$40,000	$50,000	4000	1.5
Luxury	$55,000	$70,000	1600	2
Electric (EV)	$60,000	$80,000	3000	3

Trim Cost/profit

Problem 2 – Decision Tree – KSMG Automotives Executive Meeting (15%) 

After such a strong performance in the case competition, the executive team of Klinker Automotive Group receives a letter in the mail inviting them to the National Automotive Conference in Washington, DC.  With winter break travel planning on the rise, the team needs to act fast to get their flights booked before prices soar through the roof.  The team wants to know what flight to take and how much the trip will cost based on the decision.  

Ryan has derived the probabilities and payoffs for each option, with the payoffs being calculated using “Units of fun” or Utils for short.  He has also created a decision tree to use when determining the best course of action for the executive team.  As a last resort, the team can also pack up the trusty company van “Havi McQueen” early in the morning and drive the 6 hours to DC.

Below is a list of the six best flights that Ryan was able to find to get the team to Washington DC. Of the six choices, three of them require you to take a connecting flight  (Delta, American, and Spirit), two options allow you to take a non-stop flight (Southwest and United), and one will allow you to skip the hassle altogether. (Driving). Each member of the executive team has ranked all of the available options based on routing, price, and time with 1 being the least preferred and 6 being the most preferred. The rankings are shown below in Table 2.

Table 1: Flight Results

Table 2: Util Rankings

The calculation of your initial utils will be based on the total number of votes for each flight, whether or not there are thunderstorms, if your flight gets delayed, or if the airline loses your bags.

While both non-stop flights leave early in the morning and get the team to DC well before their meeting, on their way to the airport, Jeslyn sees the skies are much darker than usual.  Ryan checks the weather and sees that there could be some strong thunderstorms rolling through Columbus early in the morning, so the team must plan accordingly.  For non-stop flights:

● If there are thunderstorms and your flight gets delayed for more than 2 hours, you will get 20% of the total votes for that choice.

● If something else delays your flight for more than 2 hours then you will get 45% of the total votes.

● If there are thunderstorms, but your flight arrives on time you will get 60% of the total votes for that choice.

○ If the weather is great and your flight leaves on time, you will receive the full utils for that option.

While all of the connecting flights will avoid the early-morning storms, the team must check their bags in order to fit on the flight.  However, the team can carry-on their bags for a fee of $60.00 per bag.  This unexpected fee takes a hit on the team’s excitement and reduces the total utils for that airline by an additional 18%.  For connecting flights:   

● If the airline loses your bag AND your connecting flight is delayed, you will get 11% of the total votes for that choice.

● If the airline loses your bag, you will receive 35% of the total votes for that choice.

● If your connecting flight is delayed your total utils will be reduced by 40%

● If you get lost while driving, you will receive 8% of the total votes for that choice.

The probability of delays, weather, losing your bags, and getting lost  are listed below:

Problem 3 – Optimization and Sensitivity Analysis – KSMG Automotive (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

KSMG automotive is a new car dealership located in Columbus, Ohio. They want to purchase an assortment of cars to sell for the upcoming year. They will purchase a mix of SUVs, pickups, sedans, crossovers, and motorcycles. The dealership can pick the number of each make and model.

You and your team are responsible for purchases the best arrangement of cars to meet the demands of the Columbus market and consumer preferences while maximizing the dealership’s monthly profit. The CEO has briefed your team on what features are available to be purchased and the constraints facing KSMG automotive.

While the CEO briefed you on the constraints, your team took the following notes:

· The vendor currently has 75 factory workers, who each work 40 hours per week, 4 weeks a month to add on the additional features.

· Some of the vehicles have four-wheel drive which is sold to customers for an extra $3000 on top of the car’s sales price.

· The base cost for the vehicles varies depending on the type of vehicle. Costs are listed below:

o SUV: $18,000

o Sedan: $9,000

o Pickup: $22,000

o Crossover: $15,000

o Motorcycle: $3,000

· At least 30 SUVs must be purchased, 15 Pickups, 25 Sedans, 10 Crossovers and at least 5 but no more than 20 Motorcycles.

· There must be exactly 130 Vehicles purchased in total for the dealership this upcoming year.

· As market demands are changing in preference towards electric and hybrid cars at least 40%, but no more than 65% of cars must be electric or hybrid.

· 55 customers requested a high performing car for better off-road performance and require that the vehicles have 4WD and at least 300hp

· With gas prices on the rise:

o Avid road-trippers want at least 42% of all SUV’s and crossovers to have at least 35MPG highway

o People who commute to work in the city want at least 27% of all Sedans and Pickups to have 30MPG city.

· Due to budget constraints, the manufacturing company is given $93,000 per week to install premium interior features which must be evenly split between features. (leather seats, heated/cooled seats, pre-set seats, Weather mats, carplay, and premium sound).

· KSMG wants to purchase an equal amount of 100hp and 200hp motorcycles.

· At least 70% of SUVs and crossovers must have supercruise for long trips.

The table provided shows various statistics about each vehicle KSMG is interested in purchasing to sell the next year, with each ‘X’ indicating the features that is included in the vehicle.

NOTE: Normally a problem like this would include an integer constraint as its not possible to purchase a partial car.  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 sensitivity analysis using solver, rename the sensitivity report to ‘Problem 3 – Part 2’

Part 3 – Using the sensitivity report from part 2, answer the questions on the ‘Problem 3 – Part 3’ sheet