Please follow the instructions for credit, thank you!!!
#1 Skycell, a major European cell phone manufacturer, is making production plans for the coming year. Skycell has worked with its customers (the service providers) to come up with the forecasts of monthly requirements (in thousands of phones) as shown in the table.
Manufacturing is primarily an assembly operation, and capacity is governed by the number of people on the production line. The plant operates for 20 days a month, eight hours each day. One person can assemble a phone every 10 minutes. Workers are paid 20 euros per hour and a 50 percent premium for overtime. The plant currently employs 1,250 workers. Component costs for each cellphone total of 20 euros. Given the rapid decline in component and finished-product prices, carrying inventory from one month to the next incurs a cost of 3 euros per phone per month. Skycell currently has a no-layoff policy in place. Overtime is limited to a maximum of 20 hours per month per employee. Assume that Skycell has a starting inventory of 50,000 units and wants to end the year with the same level of inventory.
Solve part 2 in excel. (3 pts)
6. Solve part 3 in excel. (3 pts
Month |
Demand |
Jan. |
1000 |
Feb. |
1100 |
March |
1000 |
April |
1200 |
May |
1500 |
June |
1600 |
July |
1600 |
Aug. |
900 |
Sep. |
1100 |
Oct. |
800 |
Nov. |
1400 |
Dec. |
1700 |
I have use excel solver to solve the above problem. Please note that I have not used cost of component ($20/unit) because it is a fix cost. ($20*unit produced)
Please find screenshots below
Excel formulation
Solver Parameters
Ans (a)
Ans (b)
Change hours in cell K12 from 20 to 40, this will get 300 ('000 unit) capacity. Set cells P21:P32 to 300 and solve.
Ans (6)
Change cell C14 to $6, and O21:O32 to 1000 ('000 units)
As you can see cost increases than previous solution
Due to time limit I am not able to answer remaining quetion but all can be answered using this model.
Get Answers For Free
Most questions answered within 1 hours.