Production Planning at Parbold Products

The assignment is to create a production plan for Parbold Products, a company that manufactures three kitchen appliances, using quantitative modelling techniques. The goal is to maximize sales revenue and ensure at least 75% of expected demand is met. The problem involves forecasting sales, formulating the problem as a Linear Program, and solving it using Excel Solver.

Production Planning at Parbold Products
Problem Overview
Parbold Products make a range of 3 kitchen appliances: a food processor, a toastie machine and a slow
cooker. Sales have been good and the board of directors are looking at how to plan for the coming
financial quarter. They wish to use quantitative modelling techniques to produce a production plan and
resolve any production planning issues.
Data for past sales can be found in the associated Excel file. Products are rarely out of stock and so
demand is assumed to be equivalent to sales.
At the end of this quarter the company is expected to have about £500,000 to spend on production
during the next quarter. The Head of Production has stated that this will be used to cover the total
production cost (i.e. a production budget) of the items produced for 2017 Q1. The Head of Sales has
stated that the company should borrow some money to increase this production budget as he believes
it will not be sufficient to produce enough supply to meet demand.
The production cost of each item is as follows:
It is standard practice to produce a plan to try and meet the expected demand. Should demand exceed
this supply then extra production is implemented using overtime. This overtime is outside the scope of
this analysis. The company have a policy of planning
to meet at least 75% of expected demand in order maintain brand reputation. It is not clear to the Head
of Analytics whether this should be interpreted as 75% of expected demand for each product or 75% of
overall demand.
The Chairman of the Board is interested in maximising sales revenue. This is to be the objective of this
study. The sales price of each product is as follows:

The Chairman has asked the Head of Analytics, your boss, to model the problem and he has delegated
the work to you. This will involve forecasting sales for the coming quarter, formulating the problem as a
Linear Program and solving it using Excel Solver. He wants a report describing a plan and dealing with
the above issues. He is also keen that the figures quoted be justified and that sensitivity analysis
techniques be used as appropriate.
You should produce a report to advise the company. You will be assessed on both the reporting of the
analysis and the suitability of the models used to produce the analysis. This report should consist of five
sections:
Executive Summary [10 marks]
This should provide a concise suggested plan of action for the Chairman of the Board highlighting key
figures used. He will not be interested in the details of any models. He will be interested the key
limitations of the model. The Executive Summary should be no longer than one page in length. Note: this
is not a summary of the work you have done in this coursework; nor is it an academic abstract.
Forecasting Model Description [20 marks]
This section of the report is for the Head of Analytics. He is interested in how the model works. A
suitable description of the model is one that would allow the Director of Operations to implement the
model himself. This should not include details of Excel formulae as he may wish to implement the model
in some other way. You may use mathematical formulae where appropriate.
Note: many forecasting techniques are available but please limit yourself to what has been covered in
class. Some methods, regression in particular, make assumptions about the data that you are not in a
position to justify as we have not covered them in this course.
Linear Programming Model Description [10 marks]
This section of the report is for the Head of Analytics. He is interested in how the model works. A
suitable description of the model is one that would allow the Director of Operations to implement the
model himself. This should not include details of Excel formulae as he may wish to implement the model
in some other way. You may use mathematical formulae where appropriate.
Results [40 marks]

The section should give a full analysis of the problem including the results used to provide the
recommendations in the Executive Summary.
Assumptions, Limitations and Future Work [20 marks]
This section of the report is, again, for the Head of Analytics. He will use the information presented to
determine how the model should be used by the company. He may also commission further
development of the model if the future work is deemed of value.
Note: the issues raised should relate to the model development and possible future model development
or use.
Appendix A [unmarked]
Any Excel generated reports should be included in this section. Please assume that any material included
in an appendix will not be read.