← Management of Covid-19

Excel Homework Example (Management Science)



Question 1

Micromedia offers computer training seminars on a variety of topics. In the seminars each student works at a personal computer, practicing the particular activity that the instructor is presenting. Micromedia is currently planning a two-day seminar on the use of Micro- soft Excel in statistical analysis. The projected fee for the seminar is $600 per student. The cost for the conference room, instructor compensation, lab assistants, and promotion is $9600. Micromedia rents computers for its seminars at a cost of $120 per computer per day.

Solution

Given:

  • Cost per computer per day = $120, cost per computer for 2 days = $240
  • Fixed cost = $9600
  • Projected fee per student = $600

a. Develop a model for the total cost to put on the seminar. Let x represent the number of students who enroll in the seminar.

Cost model for the seminar

Let x represent the number of enrolments

Total Cost = 240x+9600

b. Develop a model for the total profit if x students enroll in the seminar.

Profit model for the seminar

Total profit = Revenue-Cost
Total profit = 600x-(240x+9600)
Total profit = 360x-9600

c. Micromedia has forecasted an enrollment of 30 students for the seminar. How much profit will be earned if their forecast is accurate?

Profit made for 30 students

Total profit = 360x-9600
= 360×30-9600
= $1200

d. Compute the breakeven point.

Breakeven point

Breakeven occurs when cost equals to Revenue

600x = 240x+9600
360= 9600
x = 26.7
= 27

Breakeven point occurs when the number of enrolments reach 27 students.

Question 2

Preliminary plans are under way for the construction of a new stadium for a major league baseball team. City officials have questioned the number and profitability of the luxury corporate boxes planned for the upper deck of the stadium. Corporations and selected individuals may buy the boxes for $300,000 each. The fixed construction cost for the upper- deck area is estimated to be $4,500,000, with a variable cost of $150,000 for each box constructed.

Solution

Given:

  • Fixed cost = $4500000
  • Price of boxes = $300000
  • Cost for constructing each box = $150000

a. What is the breakeven point for the number of luxury boxes in the new stadium?

Breakeven point

At break-even, revenue = total cost

Let the number of boxes be x,

The total cost will be given by

Total Cost = 150000x+4500000

Revenue will be given by:

Total Revenue = 300000x

At breakeven:

300000x =150000x+4500000

15= 450

x= 30 boxes

The break-even happens with a total of 30 boxes.

b. Preliminary drawings for the stadium show that space is available for the construction of up to 50 luxury boxes. Promoters indicate that buyers are available and that all 50 could be sold if constructed. What is your recommendation concerning the construction of luxury boxes? (1.25 points) What profit is anticipated?

Recommendation for the construction of 50 boxes

It would be a viable venture to construct all the 50 luxury boxes since the market seems to be available and at the same time, it would earn great profit if they are sold. 

The anticipated profit would be given by 

Profit = 300000(50-30)

= 300000×20

= $6000000

The anticipated profit will be $6,000,000

Question 3

Reiser Sports Products wants to determine the number of All-Pro (A) and College (C) footballs to produce in order to maximize profit over the next four-week planning ho- rizon. Constraints affecting the production quantities are the production capacities in three departments: cutting and dyeing; sewing; and inspection and packaging. For the four-week planning period, 340 hours of cutting and dyeing time, 420 hours of sewing time, and 200 hours of inspection and packaging time are available. All-Pro footballs provide a profit of $5 per unit, and College footballs provide a profit of $4 per unit. The linear programming model with production times expressed in minutes is as follows:

A portion of the graphical solution to the Reiser problem is shown in Figure.

Solution

a. Shade the feasible region for this problem. 

 

b. Determine the coordinates of each extreme point and the corresponding profit. Which extreme point generates the highest profit?

Coordinates

Extreme points are B(800, 1200) and C(1400, 600) and the profits are $8800 and $9400 respectively

Extreme point C(1400, 600) generates the highest profit of $9400.

c. Draw the profit line corresponding to a profit of $4000. Move the profit line as far from the origin as you can in order to determine which extreme point will provide the optimal solution. Compare your answer with the approach you used in part (b).

The optimal solution

Optimal solution will be obtained at point C(1400, 600). The results are similar to those obtained in b.

d. Which constraints are binding? Explain.

Binding constraints

The binding constraints are those for Cutting and Dyeing, and Inspection and Packaging

Question 4

Solve the following linear programming problem using the graphical solution procedure:

Max 5A + 5B
s.t.
   1A ≤ 100
   1B ≤ 80
   2A + 4B ≤ 400
   A, B ≥0

Solution

The optimal point is point C(100, 80), with a max value of 750

Question 5

Kelson Sporting Equipment, Inc., makes two different types of baseball gloves: a regular model and a catcher’s model. The firm has 900 hours of production time available in its cut- ting and sewing department, 300 hours available in its finishing department, and 100 hours available in its packaging and shipping department. The production time requirements and the profit contribution per glove are given in the following table:

 

Assuming that the company is interested in maximizing the total profit contribution, answer the following:

a. What is the linear programming model for this problem?

Linear programing model  

Let the regular model be A and the catcher’s model be B

A+1.5B≤900 Cuttin and sewing

0.5A+1/3B≤300 Finishing

0.125A+0.25B≤100 Packaging and Shipping

Max 5A+8B

b. Find the optimal solution using the graphical solution procedure. How many gloves of each model should Kelson manufacture?

Optimal solution

Optimal solution is within the shaded region.

c. What is the total profit contribution Kelson can earn with the given production quantities?

Total profit

At Point A(501.49, 149.25) total profit is $3701.50

Question 6

Consider the following all-integer linear program:

Max 1x1 + 1x2
s.t.
   4x1 + 6x2 ≤22
   1x1 +5x2 ≤15
   2x1 + 1x2 ≤ 9
   x1, x2 ≥ 0 and integer

Solution

a. Graph the constraints for this problem. Use dots to indicate all feasible integer solutions

Graph the constraints

 

b. Find the optimal integer solution.

Optimal integer solution

The optimal integer solution is at point (2,2).