27 Jul Help in Supply (excel)
i. Use One Excel File and the SOLVER. You should use the same FORMAT as we did in class (see Content page of eLearn and the work we did in class on Thursday)
ii. Answer questions 1 and 2 in a report format, using other worksheets within the same ONE file
iii. Upload your ONE file onto the DropBox under Exam 2 Chapter 9)
(30 Points) Lavare, located in the Chicago suburbs, is a major manufacturer of stainless steel sinks. Lavare is in the middle of the demand and supply planning exercise for the coming year. Anticipated monthly demand from distributors over the 12 months is shown in Table below (ODD and EVEN Groups should use ONE designated different demand forecasts.)
| Month | Month # | ODD GRPs Forecast Demand | EVEN GRPs Forecast Demand |
| January | 1 | 10,000 | 12,000 |
| February | 2 | 12,000 | 14,000 |
| March | 3 | 15,000 | 14,000 |
| April | 4 | 16,000 | 18,000 |
| May | 5 | 25,000 | 19,000 |
| June | 6 | 30,000 | 20,000 |
| July | 7 | 28,000 | 22,000 |
| August | 8 | 24,000 | 27,000 |
| September | 9 | 20,000 | 30,000 |
| October | 10 | 17,000 | 28,000 |
| November | 11 | 13,000 | 14,000 |
| December | 12 | 11,000 | 11,000 |
Capacity at Lavare is governed by the number of machine operators it hires. The firm works 20 days a month, with a regular operating shift of eight hours per day. Any time beyond that is considered overtime. Regular-time pay is $15 per hour and overtime is $22 per hour. Overtime is limited to 20 hours per month per employee. The plant currently has 250 employees. Each sink requires two hours of labor input. It costs $3 to carry a sink in inventory for a month. Materials cost per sink is $40. Sinks are sold to distributors at a price for $125 each. We assume that no stockouts are allowed and the starting inventory entering January is 5,000 units and the desired ending inventory in December is also 5,000 units.
1. (Use a separate Worksheet SAME File!!, ) Market research has indicated that a promotion dropping prices by: 5 percent (Odd groups) and 10 percent (Even Groups) in a given month will increase sales in that month by 15 percent and bring forward 20, 15, and 10 percent demand from each of the following three months.
a. What is the optimal production plan for the year if we assume no promotions? What is the annual profit from this plan? What is the cost of this plan?
b. Is it better to promote in: ODD Groups: April or June/ EVEN Groups – July or September? How much increase in profit can be achieved as a result?
c. If sinks are sold for $250 instead of $125, does the decision about the timing of the promotion change? Why?
2. (Use another separate Worksheet, SAME File!! ) Consider the data for Lavare above. We now assume that Lavare can change the size of the workforce by laying off or hiring employees. Hiring a new employee incurs a cost of $1,000; laying off an employee incurs a layoff cost of $2,000. Also, now assume that a third party has offered to produce sinks at $74 per unit. How does this change affect the optimal production plan without a promotion? How does this change affect the optimal timing of a promotion? Explain the changes.
a. What is the optimal production plan for the year if we assume no promotions? What is the annual profit with this plan? What is the cost of this plan?
b. Is it better to promote in ODD Groups: April or June/ EVEN Groups – July or September? How much increase in profit can be achieved as a result?
NOTE: Your Report: Use same Excel file but separate worksheet(s) for your reports
2
Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax.
About Writedemy
We are a professional paper writing website. If you have searched a question and bumped into our website just know you are in the right place to get help in your coursework. We offer HIGH QUALITY & PLAGIARISM FREE Papers.
How It Works
To make an Order you only need to click on “Order Now” and we will direct you to our Order Page. Fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Are there Discounts?
All new clients are eligible for 20% off in their first Order. Our payment method is safe and secure.