Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
HW 2 – Business analytics
To solve the problem below you will need to do the following:
a. Use the Generalized Analytics Procedure (GAP) to set up your problem as follows:
i. Define your model in words
1. Identify the objective function in words
2. Identify the random variables in words (none in this HW)
3. Identify the decision variables in words
4. Identify the constraints in words
ii. Formulate your model mathematically
1. Define the random variables (none in this HW)
2. Define the decision variables
3. Define the objective function in terms of decision variables
4. Define the constraints in terms of the decision variables. Please include
any non-negativity constraints in your formulation
b. Set up the problem in Excel and use Solver to find the optimal values of the decision
variables. Ask Solver to create an Answer Report.
c. Answer the questions stated in the problem (in words).
Problem 1: Hyperloop
Your newest venture idea is a Hyperloop service between Washington DC and Baltimore. The Hyperloop
trains will offer customers a choice between coach and first-class tickets.
For the venture to comply with federal regulations, it must sell a minimum of 10 first-class tickets and a
minimum of 10 coach tickets per trip. Currently the profit margin is $5 for each coach ticket and $8 for
each first-class ticket. Due to safety reasons, the train total capacity is 50 travelers (excluding the crew).
While first-class tickets are more profitable, first class seats take up more space relative to coach seats.
The overall length of the seating area of the train is 2400 inches. The seat pitch for 1st class is 60 inches.
The federally mandated seat pitch for coach class is 30 inches.
Another consideration for deciding on the allocation of the seats is the weight capacity of the train. The
allowed total passenger payload is 10000 lbs. It is also known that first class customers are, on average,
heavier than coach customers. The typical weight of a first-class customer is 200lbs, while the typical
weight of a coach customer is 150lbs.
a) How many of each ticket should be sold in order to maximize profits? Use the GAP to formulate
the problem and then solve it using Excel Solver. Include your GAP in your submission. Also include
the answer report generated by Excel Solver with the date and time stamp.
10 First-class tickets and 20 coach tickets.
Profit per day=30*8+20*5=340$
Define in words
O:Maximize the total profit by selling the first-class tickets and coach tickets.
D:Number of first-class tickets and coach tickets sold.
C: At least 10 first-class and coach tickets must be sold per trip.
Total capacity of travelers is 50.
The overall length of the seating area of the train is 2400 inches while first-class is 60 inches
per ticket, coach ticket is 30 inches per ticket.
The payload of per first-class passenger on average is 200lbs, per coach passenger is 150 lbs,
total payload must be within 10000lbs.
Formulate my model mathematically.
D:
x: Number of first-class tickets sold.
y: Number of coach tickets sold.
O: Maximize profit=x*8+y*5
C: x>=10, y>=10
x+y<=50
60*x+30*y<=2400
200*x+150*y<=10000
b) Solve the problem using R and compare your solution to a). Attach a screenshot of your R code
and a screenshot of R output (solution).
c) How much would Hyperloop earn over a 10-year horizon with 365-day service and 100 trains per
day, assuming full utilization and assuming that you implement your solution in part a)?
340 * 10 * 365 * 100=$124,100,000
d) Due to an unprecedented outbreak of a novel infectious disease, the Hyperloop must either
redesign all train cars to follow the CDC guidelines for social distancing or shut down all
operations. The full redesign would cost $50 Million and reduce the available seating area, and
thus the maximum seating area of each train by 50% (The maximum number of passengers is not
restricted). Given that you are unwilling to increase prices, how many of each ticket should be
sold in order to maximize profits? Under the assumptions in (c) is the venture still profitable?
Under this condition, 10 First-class tickets and 20 Coach tickets should be sold, 180$ can be earned
per day.
Total profit under condition(c)= 180 * 10 * 365 * 100=65700000>50million, still profitable.
e) Ignore part d). Now suppose that you could spend capital to upgrade the train in one of two ways:
• Upgrade 1: Spend $4 million dollars to increase the maximum number of travelers from
50 to 55.
• Upgrade 2: Spend $8 million dollars to increase the seating area of the train from 2400
inches to 2600 inches.
You have three options. 1) Invest in Upgrade 1, 2) Invest in Upgrade 2, 3) Invest in Neither. Which
choice maximizes profit over the 10-year horizon? Justify you answer with the sensitivity report.
This report indicates that, every new traveler will increase 2$ of revenue, every extra inch will
increase 0.1 of revenue.
Option 1--5 extra travelers
5*2*10*365*100=3,650,000$
3650000-4000000=-350000$
Option 2--200 extra inches
200*0.1*10*365*100=7,300,000$
7300000-8000000= -700000$
Base on the result we can find that both options will lead to deduction, therefore, we
choose option 3, not investing in these two options.