Microsoft Office Tutorials and References
In Depth Information
ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT
Instead of a unified Calculations section, this model setup has calculations spread throughout the last
three sections. A typical Sales and Operations plan does not include the hiring levels and cost sections shown
in this model, but instead displays them as separate worksheets or reports linked to the plan. Sales and
Operations planning modules in large, integrated software packages usually create separate reports of hiring
and cost calculations. In this case, you will keep everything on one worksheet.
Assignment 1A: Creating the Spreadsheet
A discussion of each spreadsheet section follows. It will help you set up each section of the model and learn
the logic of the formulas. If you choose to enter the data directly, follow the cell structure shown in the
figures. To save time, it is highly recommended that you use the spreadsheet skeleton. To access the base
spreadsheet skeleton, go to your data files, select Case 9, and then select Green Jeans SOP Skeleton.xlsx.
First, build the skeleton of your spreadsheet. Set up your spreadsheet title and Constants section, as shown in
Figure 9-1. An explanation of the line items follows the figure.
Spreadsheet title and Constants section
Enter the spreadsheet title in cell B1, and then merge and center the title
across cells B1 through K1. In Figure 9-1, the title font is 14-point Arial bold. The rest of the
spreadsheet uses Arial bold as well; section titles are 12 points, and the rest of the spreadsheet
uses 11-point text.
This value, 20, is the number of pairs of jeans (units) produced per
worker each day.
This value, $200, is the amount of money required for Green Jeans to hire and
train one worker.
This value, $500, is the amount of money required to lay off one worker. This cost
includes severance pay and unemployment insurance copay to the state.
Inventory Carrying Cost
This value, 2%, is the monthly cost of holding the finished goods
inventory as a percentage of its unit cost to make.
Minimum Inventory Level
This value is the minimum inventory level needed to satisfy sales
demand, expressed as a number of days. This value determines the service level for customer
orders; that is, Green Jeans must keep enough jeans in inventory to have the sizes and
quantities needed to fill orders. To maintain a service level of 98%, Green Jeans needs to stay at or
above five days of inventory for sales demand.
Beginning labor force
This value, 550, is the number of workers employed at Green Jeans at
the start of the year.
Unit Cost of Inventory
This value, $20, is the average manufacturing cost of one pair of Green
Sales Price per unit
This value, $30, is the average sales price to the wholesaler or retailer for
one pair of Green Jeans.
Sales and Operations Plan Section
This section is the heart of your decision model. All Sales and Operations plans share a similar structure: they
contain a section for historical data (in this case, the previous three months) that includes both the plan