Assignment 1: (35%)
You are provided with three problems that are related to forecasting, sales and operations planning and inventory analysis. All the problems require appropriate spreadsheet and it has to be uploaded along with the report.
In the spreadsheet, you have to indicate your name and student number
The assignment should be of professional standards with a separate title page with module number, name, logo etc
While answering questions that are judgemental, you have to support with appropriate reference
You have to upload your assignment online and no need to submit hardcopy
Case Study 1: Forecasting (10 Marks)
Two years ago, Matrix Company moved from just making audio systems to also producing television sets. Matrix makes three different models: Mini, Medium, and large.
While Matrix is pleased with the growing sales for all three models (see the following tables), the numbers present James Young, the production manager, with a dilemma. James knows that the current manufacturing work cell is capable of producing only 2,700 TV sets per month, and total sales seem to be rapidly approaching that number. James’s team has told him it will take at least three months to plan for and implement an expanded work cell. You can get the data from the spreadsheet
1. Develop a quantitative forecast model for James till the end of the year. Which modelling technique did you choose, and why? What are the assumptions behind your model?
2. According to your model, when will Matrix need to have the expanded work cell up and running? What are the implications for when James should start the expansion effort?
3. Now suppose that over lunch the marketing vice president says to James:
We’re feeling a lot of heat from Chinese manufacturers who are offering very similar TVs to ours, but at significantly lower prices. The legal department is working on a patent infringement case, but if we can’t block these TVs from entering the market, I expect to see our sales flatten, and maybe even fall, over the rest of the year.
What questions should James ask? How would the answers to these questions affect the forecast?
Does it still make sense to use quantitative forecasting under these circumstances? Why?
Case Study 2: Sales and Operations Planning (15 Marks)
Thomas Cope, located in Bolton is a manufacturer of stainless steel exhaust hoods. Thomas Cope is in the middle of the demand and supply planning exercise for the coming year. Anticipated monthly demand from distributors over the 12 months can be obtained by entering the last three digits of your student number in the attached spreadsheet.
Capacity at Thomas Cope is governed by the number of machine operator 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 hood requires two hours of labour input. It costs £3 to carry a exhausthood in inventory for a month. Materials cost per exhaust hood is £40. The hoods are sold to distributors at a price of £125 each. We assume that no stockouts are allowed and the starting inventory entering January is 5000 units and the desired ending inventory in December is also 5,000 units.
Market research has indicated that a promotion dropping prices by 1% in a given month will increase sales in that month by 20% and bring forward 10% demand from each of the following two months. Thus a 1% drop in price in March increases sales in March by 3000 (=.02*15,000) and shifts 1,800 (=.1*18,000) units in demand from April and 2,500 (=.1*25,000) units from May forward to March.
a. What is the optimal (Use excel solver to find this) production cost for the year if we assume no promotion and keep the production constant? What is the annual profit from this plan? What is the cost of this plan?
b. Is it better to promote in April or July? How much increase in profit can be achieved as a result?
c. If hoods are sold for £250 instead of £125, does the decision about the timing of the promotion change? Why?
Assume that Thomas Cope can alter the headcount by firing or hiring. Hiring a new employee incurs a cost of £1000, firing an employee incurs a cost of £2000
a. What would be the total production cost for the year if we assume no promotion? What is the annual profit with this plan? What is the cost of this plan?
b. Is it better to promote in April or July? How much increase in profit can be achieved as a result?
c. If the holding cost for hood increases from £3 per month to £5 per month, does the decision of the timing of promotion change? Why?
Case Study 3: Inventory Analysis (10 Marks)
As purchasing manager, you have the following information about a printer that you sell:
Average weekly demand 
60 Printers 
(52 weeks per year) 

Standard deviation of weekly demand 
12 Printers 
Order lead time 3 weeks 
3 Weeks 
Standard deviation of order lead time 
0 (Lead times are constant) 
Item cost 
£120 per printer 
Cost to place an order $2 
£2 
Yearly holding cost per printer 
£48 
Desired service level during reordering period 
99% (z=2.33) 
1. What is the economic order quantity for the printer?
2. Calculate annual ordering costs and holding costs (ignoring safety stock) for the EOQ. What do you notice about the two?
3. Suppose you currently orders 120 printers at a time. How much more or less would you pay in holding and ordering costs per year if it ordered just 12 printers at a time? Show your work.
4. What is the reorder point for the printer? How much of the reorder point consists of safety stock?
For the below questions, use the following formula to consider the impact of safety stock (SS) on average inventory levels and annual holding costs:
Total Cost = Demand * Cost Per unit + (Annual Demand / Order Quantity)*Ordering Cost + (Order Quantity /2)*Holding Cost
a. What is the annual cost of holding inventory, including the safety stock? How much of this cost is due to the safety stock?
b. Suppose you are able to cut the lead time to a constant 1 week. What would be the new safety stock
Normal
0
false
false
false
ENGB
XNONE
XNONE
/* Style Definitions */
table.MsoNormalTable
{msostylename:”Table Normal”;
msotstylerowbandsize:0;
msotstylecolbandsize:0;
msostylenoshow:yes;
msostylepriority:99;
msostyleparent:””;
msopaddingalt:0cm 5.4pt 0cm 5.4pt;
msoparamargintop:0cm;
msoparamarginright:0cm;
msoparamarginbottom:8.0pt;
msoparamarginleft:0cm;
lineheight:107%;
msopagination:widoworphan;
fontsize:11.0pt;
fontfamily:”Calibri”,sansserif;
msoasciifontfamily:Calibri;
msoasciithemefont:minorlatin;
msohansifontfamily:Calibri;
msohansithemefont:minorlatin;
msofareastlanguage:ENUS;}
c. How much would this reduce annual holding costs?