Posted: April 30th, 2024

LGMT 525 Inventory Control Models Assignment 1

P a g e 1 | 23
LGMT 525 Management Science for Operations
Inventory Control Models
Assignment 1
Complete using Microsoft Office Excel Office 365
P a g e 2 | 23
Assignment Introduction & Overview
The primary focus of this assignment is the introduction of the following core concepts:
Economic Order Quantity
Production Order Quantity
Quantity Discount
Safety Stock
The assignment consists of a set of tasks to learn and demonstrate understanding of how to implement and utilize the
concepts.
Concept and Task Organization
The assignment is designed to introduce you to a new concept and progress to basic understanding and application. The
iterative structure of the assignment facilitates the progression. In addition, the assignment includes the creation and
presentation of analysis products suitable for C-suite presentations. The best analysis in the world is for not if the analyst
cannot communicate an analysis and recommendation in a clear, concise, and professional manner.
You are expected to review each task’s elements to understand the requirements. If you are uncertain of the
requirements contact your instructor for clarification.
The tasks are presented in a detailed format. The Detailed form is akin to a cookbook with steps demonstrating how to
conduct the tasks using Excel. The task elements constitute a complete description of the requirements. Instructions for
how to do something will only be presented once. Subsequent requests to perform a similar task includes an implicit
statement to utilize all previous instructions and follow the requirements specified when originally taught.
Please note, the example data, charts, and products within the instructions may not exactly match your results in
numeric value or column headers.
Assignment Files & How to Submit Work
To complete the assignment, download a copy of the file named “M06_LGMT525_Assignment.xlsx” and store the file on
your computer. The file link is located on the assignment page with the Module section in Canvas. Upload your
completed file via Canvas. The naming convention for submitted files is as follows,
LastNameFirstInitial_OriginalFileName. For example: “GibsonJ_M06_LGMT525_Assignment.xlsx”
Grading
Adherence to the course requirements and instructions is important to ensure you earn the highest grade. Each task
element within the Summary Form is a graded specification; not following the exact specification of a task will result in a
point(s) deduction. More generally, not following the instructions contained in the course or assignment instructions will
result in a point(s) deduction. When specified, the use of Excel formulas (standard or custom) is required.
P a g e 3 | 23
Economic Order Quantity: Introduction
This portion of the assignment will utilize the following worksheets within the module assignment workbook.
Task 1_EOQ Intro
Task 1
The following are instructions for conducting a economic order quantity analysis in Excel
This section will introduce how to construct an economic order quantity (EOQ) model. The EOQ is the amount of
inventory ordered that will minimize the total inventory cost. It is also called the optimal order quantity, or Q*
.
1. Open the assignment Excel workbook to the worksheet “Task 1_EOQ Intro”
2. Read the “Instructions.” The instructions provide you an overview of the components required in the task.
3. Review the Problem Statement and Table provided in the “Variables” section.
4. Complete the following steps Model formulation steps in the “Model” section.
The information provided in the problem statement and table(s) is used to define the decision variables. We will use this
information to create formulas to calculate relevant costs, levels, and factors.
Model Formulation Steps
5. The Data Table
a. Define the decision variables
i. These are the cells in which we will add decision variable information specific to individual scenarios.
Note: the orange cells define the location of user input.
ii. Annual Demand Rate (D) – is the annual demand in units for the inventory item.
iii. Setup or Ordering Cost (S) – the cost of placing an order with a supplier.
iv. Holding or Carrying cost (H) – is the cost of holding an item in inventory. The cost is expressed as per unit,
per year. Also known as carrying cost.
v. Price per unit (P) – the price per unit paid for the inventory item. This is the price the organization holding
the inventory paid, not to be confused with retail price.
Data Table Format
6. The Results Table
a. Define the functions for each objective.
i. Note: the cells are gray because we are populating the with functions to automatically calculate values.
ii. Use Excel functions to construct formulas for each of the following equations. The formulas shall link (cell
reference) to the decision variables in the Data Table.
iii. EOQ or Optimum Order Quantity, (Q*)- the amount of inventory ordered that will minimize the total
inventory cost.
𝑄
∗ = √
2𝐷𝑆
𝐻
iv. Maximum inventory – is equal to the EOQ.
𝐼𝑀𝐴𝑋 = 𝑄
∗
v. Average inventory – the average inventory on hand.
P a g e 4 | 23
𝐼𝐴𝑉𝐺 =
𝑄
∗
2
vi. Number of orders – determined by dividing the demand rate by the optimum order quantity.
𝑁𝑢𝑚𝑏𝑒𝑟 𝑜𝑓 𝑜𝑟𝑑𝑒𝑟𝑠 =
𝐷
𝑄∗
vii. Annual holding cost – the cost of holding inventory over time.
𝐴𝐻𝐶 =
𝐻𝑄
∗
2
viii. Annual order cost – the annual cost of placing orders with the supplier. Included, is the cost to set up the
manufacturing or production process for the production run model.
𝐴𝑂𝐶 =
𝐷𝑆
𝑄∗
ix. Annual unit cost – the cost of the items only.
𝐴𝑈𝐶 = 𝑃 ∗ 𝐷
x. Total annual cost – the total cost for the items and associated holding and ordering costs.
𝑇𝐶 = 𝐴𝐻𝐶 + 𝐴𝑂𝐶 + 𝐴𝑈𝐶
Results Table with Formulas
Graphing the Results
In keeping with the saying “a picture is worth a thousand words,” the following steps aid in graphing the results for an EOQ cost
sensitivity chart.
7. EOQ Cost Sensitivity Table
a. The table uses Excel formulas to provide guidance for bounding and incrementing the chart. The following
formulas are “guidance” and can be adjusted when needed.
i. Start graph at: =EOQ/4
ii. End graph at: = EOQ*2
iii. Increment by: = EOQ/10
EOQ Cost Sensitivity Table
8. Cost Curve Data
a. The table uses Excel formulas to provide data for the setup/order cost, holding cost, and total cost curves.
b. First row
i. Order quantity, Q: = start graph at cell
ii. Setup/Order cost: = D*S/Q (from order quantity, Q cell)
1. Set “D” and “S” from the Data Table to an absolute cell reference.
P a g e 5 | 23
iii. Holding cost: = H*Q/2
1. Set “H” from the Data Table to an absolute cell reference.
iv. Total cost: = Setup/Order cost + Holding cost (from same row in cost curve table)
c. Second row through last row of the cost curve data table
i. Order quantity, Q: = Q (1st row) + increment by cell
1. Set “increment by” cell to an absolute cell reference.
ii. Setup/Order cost: = D*S/Q (from order quantity, Q cell in same row)
1. Set “D” and “S” from the Data Table to an absolute cell reference.
iii. Holding cost: = H*Q/2
1. Set “H” from the Data Table to an absolute cell reference.
2. Q, equals the order quantity cell in the same row.
iv. Total cost: = Setup/Order cost + Holding cost (both from the same row in cost curve table)
Cost Curve Data Table
9. EOQ Pt Vertical Line
a. The table uses Excel formulas to provide data to create a vertical intercept at the crossing of the Setup/Order cost
and Holding cost curves. This point represents the EOQ.
b. First row
i. X-axis: = Q* (link to Results Table)
ii. Y-axis: = 0
c. Second row
i. X-axis: = Q* (link to Results Table)
ii. Y-axis: = Annual Holding Cost + Annual Order Cost (link to Results Table)
EOQ Pt Vertical Line Table
10. Create the graph
a. Reference previously provided guidance for creating graphs.
b. Select the cost curve data table to include the column headers.
c. Insert >> Scatter >> Scatter with smooth lines.
d. Select Data >> Add
i. Series name: select the EOQ cell in the EOQ Pt Vertical Line.
ii. Series X values: select the two cells below X-axis
iii. Series Y values: select the two cells below Y-axis
e. Retain the default line colors.
f. The vertical EOQ line should be dashed
g. Y-axis label: Total Cost ($)
h. X-axis label: Order Quantity (Q)
i. Figure # = 1
P a g e 6 | 23
j. Figure Title: Economic Order Quantity by “your last name”
EOQ Cost Curve Graph
Result Statements
The following are examples of how to write result statements for an EOQ analysis. Additional statements are possible given the
results of the analysis; the following are the important basics. The goal of each statement is a concise and information rich sentence.
We are reporting facts, not writing Milton’s Paradise Lost.
11. Examples of Results Statements
a. Describe the optimal ordering strategy.
i. Example: To achieve optimal ordering for the 1000 unit demand, the company should place 5 orders of
200 units for a total annual cost of $3100.
b. Describe the resulting inventory.
i. Example: Utilizing EOQ, the maximum inventory is 200 with an average of 100 for a total annual holding
cost of $50
c. Describe the costs.
i. Example: To meet demand, the total annual cost is $3100; $3000 for production, $50 in order costs, and
$50 for inventory holding costs.
12. Answer the question(s) in this section.
a. Answers should be typed into the respective green highlighted boxes. The wrap text and increasing the row height
can be used to ensure the entire answer is displayed.
13. Task 1 is complete.
P a g e 7 | 23
Economic Order Quantity: Fuel Valves
This portion of the assignment will utilize the following worksheets within the module assignment workbook.
Task 2_EOQ Fuel valves
Task 2
The following are instructions for conducting a economic order quantity analysis in Excel
This section is practice for applying the economic order quantity (EOQ) model.
1. Open the assignment Excel workbook to the worksheet “Task 2_EOQ Fuel valves”
2. Read the “Instructions.” The instructions provide you an overview of the components required in the task.
3. Review the Problem Statement and Table provided in the “Variables” section.
4. Complete the following steps Model formulation steps in the “Model” section.
Model Formulation Steps
5. The Data Table
a. Define the decision variables to include the symbols and inputs.
6. The Results Table
a. Define the functions for each objective to include the equation and formulas.
Graphing the Results
Create an EOQ graph and define the supporting elements.
7. EOQ Cost Sensitivity Table
8. Cost Curve Data
9. EOQ Pt Vertical Line
10. Create the graph
a. Figure # = 2
b. Figure Title: Economic Order Quantity by “your last name”
Result Statements
11. Answer the question(s) in this section.
a. Answers should be typed into the respective green highlighted boxes. The wrap text and increasing the row height
can be used to ensure the entire answer is displayed.
12. Task 2 is complete.
P a g e 8 | 23
Production Order Quantity: Introduction
This portion of the assignment will utilize the following worksheets within the module assignment workbook.
Task 3_POQ Intro
Task 3
The following are instructions for conducting a production order quantity analysis in Excel
This section will introduce how to construct a production order quantity (POQ) model. The production run model is an
inventory model in which inventory is produced or manufactured over time instead of being ordered or purchased. A
key difference between the EOQ and POQ is the elimination of the instantaneous receipt assumption.
1. Open the assignment Excel workbook to the worksheet “Task 3_POQ Intro”
2. Read the “Instructions.” The instructions provide you an overview of the components required in the task.
3. Review the Problem Statement and Table provided in the “Variables” section.
4. Complete the following steps Model formulation steps in the “Model” section.
The information provided in the problem statement and table(s) is used to define the decision variables. We will use this
information to create formulas to calculate relevant costs, levels, and factors.
Model Formulation Steps
5. The Data Table
a. Define the decision variables
i. These are the cells in which we will add decision variable information specific to individual scenarios.
Note: the orange cells define the location of user input.
ii. Annual Demand Rate (D) – is the annual demand in units for the inventory item.
iii. Setup or Ordering Cost (S) – the cost of placing an order with a supplier.
iv. Holding or Carrying cost (H) – is the cost of holding an item in inventory. The cost is expressed as per unit,
per year. Also known as carrying cost.
v. Price per unit (P) – the price per unit paid for the inventory item. This is the price the organization holding
the inventory paid, not to be confused with retail price.
vi. Daily production rate (p) – the number of units produced per work day.
vii. Daily demand rate for calculations (d) – uses an automatic function to support dependent calculations.
1. Excel formula for cell: =IF(D35>0,D35,IF(D36>0,D36,””))
a. D35 = cell for Daily demand rate (known)
b. D36 = cell for Daily demand rate (calculated)
2. Daily demand can be determined in two ways. First, it’s known (my kind of math); if known,
insert the value in the Daily demand rate (known) cell. Second, it’s calculated based on the
Demand rate and the production days per year (Wd).
3. Daily demand rate (calculated) rounds the result of Demand rate divided by production days per
year.
a. Excel formula of cell: =ROUND(Demand rate/Wd,0)
viii. Production days per year (Wd) – the number of actual production days per year
P a g e 9 | 23
Data Table Format
6. The Results Table
a. Define the functions for each objective.
i. Note: the cells are gray because we are populating the with functions to automatically calculate values.
ii. Use Excel functions to construct formulas for each of the following equations. The formulas shall link (cell
reference) to the decision variables in the Data Table.
iii. POQ or Optimum Production Quantity, (Q*)- the amount of production run quantity to minimize the total
cost.
𝑄
∗ = √
2𝐷𝑆
𝐻
∗ √
𝑝
(𝑝 − 𝑑)
iv. Maximum inventory – the maximum inventory level in the production model is less than Q.
𝐼𝑀𝐴𝑋 = 𝑄
∗
(𝑝 − 𝑑)
𝑝
v. Average inventory – the average inventory on hand. The average inventory is half the maximum level.
𝐼𝐴𝑉𝐺 =
𝑄
∗
2
vi. Number of setups – determined by dividing the demand rate by the optimum order quantity.
𝑁𝑢𝑚𝑏𝑒𝑟 𝑜𝑓 𝑠𝑒𝑡𝑢𝑝𝑠 =
𝐷
𝑄∗
vii. Holding cost – the cost of holding inventory over time.
𝐻𝐶 = 𝐻 ∗ 𝐼𝐴𝑉𝐺
viii. Setup cost – the cost to set up the manufacturing or production process for the production run model.
𝑆𝐶 = 𝑆 ∗ 𝑁𝑢𝑚𝑏𝑒𝑟 𝑜𝑓 𝑠𝑒𝑡𝑢𝑝𝑠
ix. Unit cost – the cost of the items only.
𝑈𝐶 = 𝑃 ∗ 𝐷
x. Total cost – the total cost for producing the items and associated holding and setup costs.
𝑇𝐶 = 𝐻𝐶 + 𝑆𝐶 + 𝑈𝐶
Results Table with Formulas
P a g e 10 | 23
Graphing the Results
In keeping with the saying “a picture is worth a thousand words,” the following steps aid in graphing the results for an POQ cost
sensitivity chart.
7. POQ Cost Sensitivity Table
a. The table uses Excel formulas to provide guidance for bounding and incrementing the chart. The following
formulas are “guidance” and can be adjusted when needed.
i. Start graph at: =POQ/4
ii. End graph at: = POQ*2
iii. Increment by: = POQ/10
POQ Cost Sensitivity Table
8. Cost Curve Data
a. The table uses Excel formulas to provide data for the setup/order cost, holding cost, and total cost curves.
b. First row
i. Order quantity, Q: = start graph at cell
ii. Setup/Order cost: = D*S/Q (from order quantity, Q cell)
1. Set “D” and “S” from the Data Table to an absolute cell reference.
iii. Holding cost: = H*Q*(p-d)/p/2
1. Set “H”, “p” and “d” from the Data Table to an absolute cell reference.
2. Q, equals the order quantity cell in the same row.
iv. Total cost: = Setup/Order cost + Holding cost (from same row in cost curve table)
c. Second row through last row of the cost curve data table
i. Order quantity, Q: = Q (1st row) + increment by cell
1. Set “increment by” cell to an absolute cell reference.
ii. The remaining calculations are the same.
Cost Curve Data Table
9. POQ Pt Vertical Line
a. The table uses Excel formulas to provide data to create a vertical intercept at the crossing of the Setup/Order cost
and Holding cost curves. This point represents the POQ.
b. First row
i. X-axis: = Q* (link to Results Table)
ii. Y-axis: = 0
c. Second row
i. X-axis: = Q* (link to Results Table)
ii. Y-axis: = Holding Cost (HC) + Setup Cost (SC) (link to Results Table)
P a g e 11 | 23
POQ Pt Vertical Line Table
10. Create the graph
a. Reference previously provided guidance for creating graphs.
b. Y-axis label: Total Cost (holding + Setup) ($)
c. X-axis label: Production Order Quantity (Q)
d. Figure # = 3
e. Figure Title: Production Order Quantity costs by “your last name”
Result Statements
The following are examples of how to write result statements for an POQ analysis. Additional statements are possible given the
results of the analysis; the following are the important basics.
11. Examples of Results Statements
a. Describe the optimal production strategy.
i. Example: To achieve optimal production for the 10,000 unit demand, the company should plan 2.5 batch
productions of 4,000 units for a total cost of $50,500.
b. Describe the resulting inventory.
i. Example: Utilizing POQ, the maximum inventory is 1,000 with an average of $500 for a holding cost of
$250.
c. Describe the costs.
i. Example: To meet demand, the total cost is $50,500; $50,000 for production, $250 in holding costs, and
$250 for setup costs.
12. Answer the question(s) in this section.
a. Answers should be typed into the respective green highlighted boxes. The wrap text and increasing the row height
can be used to ensure the entire answer is displayed.
13. Task 3 is complete.
P a g e 12 | 23
Production Order Quantity: Scissors
This portion of the assignment will utilize the following worksheets within the module assignment workbook.
Task 4_POQ Scissors
Task 4
The following are instructions for conducting a production order quantity analysis in Excel
This section is practice for constructing a production order quantity (POQ) model.
1. Open the assignment Excel workbook to the worksheet “Task 4_POQ Scissors”
2. Read the “Instructions.” The instructions provide you an overview of the components required in the task.
3. Review the Problem Statement and Table provided in the “Variables” section.
4. Complete the following steps Model formulation steps in the “Model” section.
The information provided in the problem statement and table(s) is used to define the decision variables. We will use this
information to create formulas to calculate relevant costs, levels, and factors.
Model Formulation Steps
5. The Data Table
a. Define the decision variables to include the symbols and inputs.
6. The Results Table
a. Define the functions for each objective to include the equation and formulas.
Graphing the Results
Create an POQ graph and define the supporting elements.
7. POQ Cost Sensitivity Table
8. Cost Curve Data
9. POQ Pt Vertical Line
10. Create the graph
a. Reference previously provided guidance for creating graphs.
b. Figure # = 4
c. Figure Title: Production Order Quantity costs by “your last name”
Result Statements
The following are examples of how to write result statements for an POQ analysis. Additional statements are possible given the
results of the analysis; the following are the important basics.
11. Answer the question(s) in this section.
a. Answers should be typed into the respective green highlighted boxes. The wrap text and increasing the row height
can be used to ensure the entire answer is displayed.
12. Task 4 is complete.
P a g e 13 | 23
Quantity Discount: Introduction
This portion of the assignment will utilize the following worksheets within the module assignment workbook.
Task 5_qtyDiscount Intro
Task 5
The following are instructions for conducting a quantity discount analysis in Excel
This section will introduce how to construct a quantity discount model. The quantity discount model is an inventory
model in which cost per unit changes when large orders are placed. The overall objective of the quantity discount model
is to minimize total inventory costs, which now include actual material costs.
1. Open the assignment Excel workbook to the worksheet “Task 5_qtyDiscount Intro”
2. Read the “Instructions.” The instructions provide you an overview of the components required in the task.
3. Review the Problem Statement and Table provided in the “Variables” section.
4. Complete the following steps Model formulation steps in the “Model” section.
The information provided in the problem statement and table(s) is used to define the decision variables. We will use this
information to create formulas to calculate relevant costs, levels, and factors.
Model Formulation Steps
5. The Data Table
a. Define the decision variables
i. These are the cells in which we will add decision variable information specific to individual scenarios.
Note: the orange cells define the location of user input.
ii. Annual Demand Rate (D) – is the annual demand in units for the inventory item.
iii. Ordering or Setup Cost (Co) – the cost of placing an order with a supplier.
iv. Holding cost (I) – is the cost of holding an item in inventory. The cost is expressed as a percentage of the
unit price per year.
1. The holding or carrying cost per unit per year (Ch) = holding cost (%), I times unit price, P.
𝐶ℎ = 𝐼 ∗ 𝑃
v. Min quantity breakpoints – the range values specifying the minimum threshold quantity required to reach
the next price range.
vi. Unit price (P) – the price per unit paid for the inventory item within each breakpoint range.
Data Table Format
6. The Results Table
a. Define the functions for each objective.
i. Note: the cells are gray because we are populating the with functions to automatically calculate values.
ii. Use Excel functions to construct formulas for each of the following equations. The formulas shall link (cell
reference) to the decision variables in the Data Table.
P a g e 14 | 23
iii. EOQ or Optimum Order Quantity, (Q*)- the amount of inventory ordered that will minimize the total
inventory cost.
1. Use absolute cell referencing for “D”, “Co”, and “I”.
𝑄
∗ = √
2 ∗ 𝐷 ∗ 𝐶𝑜
𝐼 ∗ 𝑃
iv. Order quantity – the number of units ordered.
1. The following Excel function compares the Q* value to min quantity breakpoint for the existing
range and the next higher range, if there is a higher range. If Q* exceeds the min quantity
breakpoint, the OC value equals Q*. If Q* does not exceed the min quantity breakpoint, the OC
equals the min quantity breakpoint for the current range.
2. The following Excel function operationalizes the above.
a. Please read the following carefully while referencing the figure. It’s normal to review
this multiple times to follow the Excel function logic.
b. For the Range 1 & Range 2 OC: =IF(Q*>=”min qty breakpoint for next higher
range”,”NA”,IF(Q*>=”min qty breakpoint for current range”,Q*,min qty breakpoint for
current range)
i. If Q* >= the min qty breakpoint for the next higher range, Excel returns a “NA”
to the cell.
ii. If the above does not happen:
1. If Q* >= the min qty breakpoint for the current range, Excel returns
the value of Q* to the cell.
2. If Q* is not >= the min qty breakpoint for the current range, Excel
returns the min qty breakpoint for the current range value to the cell.
Order Quantity Excel Function for Ranges 1 & 2
c. For the Range 3 OC: =IF(Q*>=”min qty breakpoint for current range”,Q*,min qty
breakpoint for current range)
i. The Range 3 OC Excel function is different because there is no minimum
quantity breakpoint specified above the range.
1. If Q* >= the min qty breakpoint for the current range, Excel returns
the value of Q* to the cell.
P a g e 15 | 23
2. If Q* is not >= the min qty breakpoint for the current range, Excel
returns the min qty breakpoint for the current range value to the cell.
Order Quantity Excel Function for Range 3
v. Holding cost – the cost of holding inventory over time.
𝐻𝐶 = 𝑂𝐶 ∗ 𝐼 ∗ 𝑃
vi. Setup cost – the cost to set up the manufacturing or production process for the production run model.
𝑆𝐶 = 𝐷
𝐶𝑂
𝑂𝐶
vii. Unit cost – the cost of the items only.
𝑈𝐶 = 𝐷 ∗ 𝑃
viii. Total cost – the total cost for producing the items and associated holding and setup costs.
𝑇𝐶 = 𝐻𝐶 + 𝑆𝐶 + 𝑈𝐶
Results Table with Formulas
Graphing the Results
The following steps aid in graphing the results for a quantity discount analysis.
7. Cost Curve Data
a. Within the Chart Range and Increment Values
i. The “increment based on” value represents the minimum quantity breakpoints. In this example, the range
breaks occur in 1000 unit increments; therefore, we use “1000.”
b. A sufficient number of rows are required such that the Q value in the “order quantity” column exceeds the largest
breakpoint plus a four additional. We will create the table row by row and stop when the appropriate number is
reached.
P a g e 16 | 23
c. The first column is a series from 1 to XX; XX being the number of rows required to exceed the largest breakpoint
plus four additional rows. Repeat the following Excel formulas for all rows.
d. Order Quantity, Q:
i. Description: = Increment based on / 4 + row number (Increment based on/12)
ii. Excel formula: = =$C$60/4+B66*$C$60/12
e. Unit Cost:
i. Description: uses a lookup table to find the unit price based on the min quantity breakpoint values.
ii. Excel formula: =HLOOKUP(C66,$D$40:$F$41,2)
1. The min quantity breakpoints and unit price cells for range 1 – 3: $D$40:$F$41
f. Setup Cost:
i. Description: demand rate (D) times ordering or setup cost (Co) divided by order quantity (Q)
ii. Excel formula: =$D$35*$D$36/C66
g. Holding Cost:
i. Description: order quantity (Q) times holding cost percentage (I) times unit cost divided by 2
ii. Excel formula: =C66*$D$37*D66/2
h. Total Unit Cost:
i. Description: unit cost times demand rate (D)
ii. Excel formula: =D66*$D$35
i. Total Costs
i. Description: the sum of setup, holding, and total unit costs.
ii. Excel formula: =SUM(E66:G66)
Cost Curve and Chart Range Excel Formulas
8. Chart Range and Increment Values
a. The “increment based on” value represents the minimum quantity breakpoints. In this example, the range breaks
occur in 1000 unit increments; therefore, we use “1000.”
b. The X-axis and Y-axis information provides values for use in formatting the
c. First column: X-Axis Range
i. Row 1: insert a zero, 0.
ii. Row 2: =ROUNDUP(Q value in last row of cost curve data,-2)
iii. Row 3: =(Row 1 – Row 2)/10
d. Second column: Y-Axis Range
i. Row 1: =ROUNDDOWN(MIN(all cells in the total costs column),-3)
ii. Row 2: =ROUNDUP(MAX(all cells in the total costs column),-3)
iii. Row 3: =(Row 1 – Row 2)/10
P a g e 17 | 23
9. Create the graph
a. Reference previously provided guidance for creating graphs.
b. Plot Total Cost and Order Quantity
c. Y-axis label: Total Cost ($)
d. X-axis label: Order Quantity (Q)
e. Figure # = 5
f. Figure Title: Order Quantity Discount: Total Cost vs Order Quantity by “Student Last Name”
Order Quantity Discount Graph
Result Statements
10. Answer the question(s) in this section.
a. Answers should be typed into the respective green highlighted boxes. The wrap text and increasing the row height
can be used to ensure the entire answer is displayed.
11. Task 5 is complete.
P a g e 18 | 23
Quantity Discount: Cannon Plugs
This portion of the assignment will utilize the following worksheets within the module assignment workbook.
Task 6_qtyDiscount Plugs
Task 6
The following are instructions for conducting a quantity discount analysis in Excel
This section is practice for constructing a quantity discount model.
1. Open the assignment Excel workbook to the worksheet “Task 6_qtyDiscount Plugs”
2. Read the “Instructions.” The instructions provide you an overview of the components required in the task.
3. Review the Problem Statement and Table provided in the “Variables” section.
4. Complete the following steps Model formulation steps in the “Model” section.
Model Formulation Steps
5. The Data Table
6. The Results Table
Graphing the Results
7. Cost Curve Data
8. Chart Range and Increment Values
9. Create the graph
Result Statements
10. Answer the question(s) in this section.
11. Task 6 is complete.
P a g e 19 | 23
Safety Stock: Introduction
This portion of the assignment will utilize the following worksheets within the module assignment workbook.
Task 7_SafetyStock Intro
Task 7
The following are instructions for conducting a safety stock analysis in Excel
This section will introduce how to construct a safety stock analysis model. When the EOQ assumptions are met, it is
possible to schedule orders to arrive so that stockouts are avoided. However, if demand or lead time is uncertain, the
exact demand during the lead time (which is the ROP in the EOQ situation) will not be known with certainty. Therefore,
to prevent stockouts, it is necessary to carry additional inventory called safety stock.
1. Open the assignment Excel workbook to the worksheet “Task 7_SafetyStock Intro”
2. Read the “Instructions.” The instructions provide you an overview of the components required in the task.
3. Review the Problem Statement for each model provided in the “Variables” section.
4. Complete the following steps Model formulation steps in the “Model” section.
The information provided in the problem statement and table(s) is used to define the decision variables. We will use this
information to create formulas to calculate relevant costs, levels, and factors.
Model Formulation Steps
The information provided in the problem statements is used to define the decision variables. We will use this information to create
formulas to calculate key values for each of the following safety stock models.
Safety Stock Model: when demand during lead time and its standard deviation are known
Data and Results Table
5. The Data Table
a. Define the decision variables
i. These are the cells in which we will add decision variable information specific to individual scenarios.
Note: the orange cells define the location of user input.
ii. Average demand during lead time (µ)
iii. Standard deviation of lead time, (𝜎𝐿𝑇)
iv. Service Level, (SL) – the chance, expressed as a percent, that there will not be a stockout.
1. SL = 1 – probability of a stockout
6. The Results Table
a. Define the functions for each objective.
i. Note: the cells are gray because we are populating the with functions to automatically calculate values.
ii. Z-value (Z) – how many standard deviations away from the mean is the ‘X’ value.
1. Excel function: =NORM.S.INV(Service Level Cell)
iii. Safety Stock (SS) – extra inventory that is used to help avoid stockouts.
P a g e 20 | 23
1. Excel formula: = Z * 𝜎𝐿𝑇
iv. Reorder Point (ROP) – the number of units on hand when an order for more inventory is placed.
1. Excel formula: = SS + µ
Safety Stock Model: when either daily demand, lead time or both are known
Data and Results Table
7. The Data Table
a. Define the decision variables
i. Average daily demand (𝑑𝐴𝑉𝐺)
ii. Standard deviation of daily demand, (𝜎𝑑)
iii. Average lead time, in days (LT) – the time it takes to receive an order after it is placed.
iv. Standard deviation of lead time, (𝜎𝐿𝑇)
v. Service Level, (SL)
8. The Results Table
a. Define the functions for each objective.
i. Z-value (Z)
ii. Average demand during lead time (𝐿𝑇𝐴𝑉𝐺)
1. Excel formula: = 𝑑𝐴𝑉𝐺 ∗ 𝐿𝑇
iii. Standard deviation of demand during lead time (𝜎𝑑𝐿𝑇)
1. Excel formula: = √𝐿𝑇 ∗ 𝜎𝑑
2 + 𝑑𝐴𝑉𝐺 ∗ 𝜎𝐿𝑇
2
iv. Safety Stock (SS) – extra inventory that is used to help avoid stockouts.
1. Excel formula: = Z * 𝜎𝑑𝐿𝑇
v. Reorder Point (ROP) – the number of units on hand when an order for more inventory is placed.
1. Excel formula: = SS + 𝐿𝑇𝐴𝑉𝐺
P a g e 21 | 23
Safety Stock Model: when daily demand and its standard deviation are known
Data and Results Table
9. The Data Table
a. Define the decision variables
i. Average daily demand (𝑑𝐴𝑉𝐺)
ii. Standard deviation of daily demand, (𝜎𝑑)
iii. Lead time, in days (LT) – the time it takes to receive an order after it is placed.
iv. Service Level, (SL)
10. The Results Table
a. Define the functions for each objective.
i. Z-value (Z)
ii. Average demand during lead time (𝐿𝑇𝐴𝑉𝐺)
1. Excel formula: = 𝑑𝐴𝑉𝐺 ∗ 𝐿𝑇
iii. Standard deviation of demand during lead time (𝜎𝑑𝐿𝑇)
1. Excel formula: = 𝜎𝑑√𝐿𝑇
iv. Safety Stock (SS) – extra inventory that is used to help avoid stockouts.
1. Excel formula: = Z * 𝜎𝑑𝐿𝑇
v. Reorder Point (ROP) – the number of units on hand when an order for more inventory is placed.
1. Excel formula: = SS + 𝐿𝑇𝐴𝑉𝐺
Result Statements
11. Answer the question(s) in this section.
a. Answers should be typed into the respective green highlighted boxes. The wrap text and increasing the row height
can be used to ensure the entire answer is displayed.
12. Task 7 is complete.
P a g e 22 | 23
Safety Stock: Introduction
This portion of the assignment will utilize the following worksheets within the module assignment workbook.
Task 8_SafetyStock Intro
Task 8
The following are instructions for conducting a safety stock analysis in Excel
This section is practice for constructing a safety stock analysis model.
1. Open the assignment Excel workbook to the worksheet “Task 8_SafetyStock Prac”
2. Read the “Instructions.” The instructions provide you an overview of the components required in the task.
3. Review the Problem Statement for each model provided in the “Variables” section.
4. Complete the following steps Model formulation steps in the “Model” section.
The information provided in the problem statement and table(s) is used to define the decision variables. We will use this
information to create formulas to calculate relevant costs, levels, and factors.
Model Formulation Steps
The information provided in the problem statements is used to define the decision variables. We will use this information to create
formulas to calculate key values for each of the following safety stock models.
Safety Stock Model: when demand during lead time and its standard deviation are known
5. The Data Table
6. The Results Table
Safety Stock Model: when either daily demand, lead time or both are known
7. The Data Table
8. The Results Table
Safety Stock Model: when daily demand and its standard deviation are known
9. The Data Table
10. The Results Table
Result Statements
11. Answer the question(s) in this section.
a. Answers should be typed into the respective green highlighted boxes. The wrap text and increasing the row height
can be used to ensure the entire answer is displayed.
12. Task 8 is complete.
P a g e 23 | 23
The End of the Assignment
“Did you hear oxygen went on a date with potassium? It went OK.”

Check Price Discount

Study Notes & Homework Samples: Clinical Practice Guidelines »What are the effects of surgeries on organs and artificial tissues?