Excel Homework Example (Management Science)
|← Example of Annotated Bibliography for Management Essay||The Strategic Planning Process →|
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.
- 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
d. Compute the breakeven point.
Breakeven occurs when cost equals to Revenue
600x = 240x+9600
360x = 9600
x = 26.7
Breakeven point occurs when the number of enrolments reach 27 students.
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.
- 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?
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
15x = 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)
The anticipated profit will be $6,000,000
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.
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?
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.
The binding constraints are those for Cutting and Dyeing, and Inspection and Packaging
Solve the following linear programming problem using the graphical solution procedure:
Max 5A + 5B
1A ≤ 100
1B ≤ 80
2A + 4B ≤ 400
A, B ≥0
The optimal point is point C(100, 80), with a max value of 750
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.125A+0.25B≤100 Packaging and Shipping
b. Find the optimal solution using the graphical solution procedure. How many gloves of each model should Kelson manufacture?
Optimal solution is within the shaded region.
c. What is the total profit contribution Kelson can earn with the given production quantities?
At Point A(501.49, 149.25) total profit is $3701.50
Consider the following all-integer linear program:
Max 1x1 + 1x2
4x1 + 6x2 ≤22
1x1 +5x2 ≤15
2x1 + 1x2 ≤ 9
x1, x2 ≥ 0 and integer
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).