Microsoft Office Tutorials and References
In Depth Information
ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT
At your first meeting to create the S&OP, you quickly discover that key members of management have
been at odds over the best production, hiring, and inventory strategies to employ. The argument revolves
around three key managers:
￿
George Detweiler, the Operations manager, feels that the best strategy is to make exactly what
the sales forecasts dictate every month, and to hire and lay off employees accordingly. In sales
and operations planning, this is called
chasing
the sales forecast or a Chase plan.
￿
Betty Miller, the Human Resources (HR) manager, disagrees with George. She thinks that the best
strategy is to find the correct workforce size to keep employment levels stable, and to let finished
goods inventories expand and contract as a buffer against high or low demand. She believes the
savings that result from not continually hiring and laying off employees will more than offset the
higher costs of carrying inventory. She also contends that a steady, trained workforce will improve
product quality and morale in the workplace. (She will also have less work to do if the company
hires and trains fewer new employees.) This type of S&OP is called a Level plan.
￿
Pedro Sanchez, the Logistics manager, disagrees with George and Betty. He contends that the
best way to plan operations is to keep the lowest possible amount of finished goods inventory in
the warehouse needed to service the customers. He feels that the S&OP should target a 5- to
6-day inventory at the end of every month to minimize inventory carrying costs. This type of
S&OP is called a Logistics Target plan.
￿
Kathy Green, the president, is not sure which strategy to pursue. She is leaning toward Betty
Miller
s proposal because she believes that minimizing layoffs will build a loyal, well-trained
workforce. At the same time, Kathy recognizes the mobility of the textile labor market in the region
and does not want to lose a competitive edge from incurring higher inventory carrying costs.
You want to provide quantitative data that shows the management team the most cost-effective solution.
You are not sure that any of the managers has the correct solution, so you have decided to build a decision
support model to determine the costs, workforce, and inventory levels for each of the managers
proposals.
You also want to determine the least expensive optimal solution.
The Accounting Department has given you information for the historical performance of Green Jeans, as
well as data that will help you build your model. The information includes:
￿
Historical sales and operations results for the previous three months
￿
Employee productivity measured in pairs of jeans produced per employee per day
￿
Hiring and layoff costs per hire or layoff
￿
Inventory carrying costs as a percentage of inventory value
￿
The budgeted unit cost and current selling price of a pair of jeans
The Marketing and Sales Department has provided its sales forecast for the first six months of 2012.
Human Resources has provided the current number of employees. The Logistics manager said that to keep
customer service at an acceptable level, the end-of-month finished goods inventory must equal at least five
days of supply according to the sales forecast.
ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT
In this assignment, you will create a spreadsheet that models Green Jeans
S&OP, with an emphasis on
employment levels for the six months in question. In Assignment 1A, you will create a Solver spreadsheet to
model George Detweiler
s Chase plan. In Assignment 1B, you will copy the first spreadsheet to new sheets
and rerun Solver to model the HR manager
s plan, and your own optimal cost
plan. In Assignments 2 and 3, you will use the spreadsheet models to summarize the costs of each plan, and
you will give a presentation of your analysis and recommendations.
The Sales and Operations spreadsheet model will contain the following sections:
s plan, the Logistics manager
￿
Constants
￿
Sales and Operations Plan, which is a combination of the Changing Cells and some calculations
￿
Hires and Layoffs, which is a calculation of the number of workers hired and laid off
￿
Costs, which is a calculation and summation of the hiring, layoff, and inventory costs; the Total
Cost is the Optimization Cell
 
Search JabSto ::




Custom Search