# Excel work

# MPS

Instructions: Complete the MPS table | |||||||||||||

Period | On-Hand | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |

Forecast | 25 | 25 | 25 | 0 | 15 | 35 | 25 | 30 | 20 | 25 | 15 | 0 | |

Customer Orders | 20 | 30 | 15 | 10 | 0 | 0 | 20 | 25 | 0 | 0 | 30 | 0 | |

Projected Available | 25 | ||||||||||||

Available to Promise | |||||||||||||

MPS | |||||||||||||

MPS Lot Size = 50 | |||||||||||||

Lead-time = 0 | |||||||||||||

Projected Available = | |||||||||||||

BI + MPS â€“ Max (Customer Orders ; Forecast) | |||||||||||||

ATP action bucket | = BI + MPS – customer orders between current MPS and next scheduled replenishment | ||||||||||||

Available to Promise = MPS – customer orders between current MPS and next scheduled replenishment |

# ROP

Product X Annual Demand = | 1000 units | Instructions: Find the Reorder Point for product X & show your calculations. |

Work Days per Year = | 250 | |

Lead-time = | 15 days | |

Service Level = | 95% | |

Std Deviation of Demand during Lead-time = | 25 units | ROP = |

# MRP Final

Instructions: Complete all empty cells in the MRP tables below for all components shown in the product tree for Product Z (highlighted in green at right). Assume that lead-time is strictly followed, and you are not able to get or build material inside of lead-time. | |||||||||

MRP Final | |||||||||

Planning Data: | Lot Size = | Lot for Lot | L T = | 1 | Sfty Stk = | 0 | |||

Product Z | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | Z |

Gross Requirements | 300 | 325 | 400 | 350 | 300 | 350 | 400 | 400 | |

Scheduled Receipts | A (x1) | B (x2) | |||||||

Projected Available | 300 | ||||||||

Net Requirements | D (x3) | C (x0.5) | |||||||

Planned Order Receipts | |||||||||

Planned Order Releases | E (x1) | ||||||||

Planning Data: | Lot Size = | 150 | L T = | 2 | Sfty Stk = | 0 | |||

Assembly B | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | |

Gross Requirements | |||||||||

Scheduled Receipts | 400 | 600 | |||||||

Projected Available | 300 | ||||||||

Net Requirements | |||||||||

Planned Order Receipts | |||||||||

Planned Order Releases | |||||||||

Planning Data: | Lot Size = | 300 | L T = | 2 | Sfty Stk = | 100 | |||

Component A | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | |

Gross Requirements | |||||||||

Scheduled Receipts | 300 | ||||||||

Projected Available | 500 | ||||||||

Net Requirements | |||||||||

Planned Order Receipts | |||||||||

Planned Order Releases | |||||||||

Planning Data: | Lot Size = | 125 | L T = | 3 | Sfty Stk = | 250 | |||

Component C | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | |

Gross Requirements | |||||||||

Scheduled Receipts | 375 | 125 | 0 | ||||||

Projected Available | 375 | ||||||||

Net Requirements | |||||||||

Planned Order Receipts | |||||||||

Planned Order Releases | |||||||||

Planning Data: | Lot Size = | 250 | L T = | 1 | Sfty Stk = | 150 | |||

Component D | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | |

Gross Requirements | |||||||||

Scheduled Receipts | 500 | 0 | 500 | ||||||

Projected Available | 650 | ||||||||

Net Requirements | |||||||||

Planned Order Receipts | |||||||||

Planned Order Releases | |||||||||

Planning Data: | Lot Size = | 100 | L T = | 1 | Sfty Stk = | 100 | |||

Component E | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | |

Gross Requirements | |||||||||

Scheduled Receipts | 1100 | ||||||||

Projected Available | 350 | ||||||||

Net Requirements | |||||||||

Planned Order Receipts | |||||||||

Planned Order Releases |

# EDD

Job Number | Job Time | Due Date | Remaining Job time at other WS | Job Flow | Job | Job | Critical Ratio | Job Number Mapping |

(Set-up & run) | (days from now) | Lateness | Tardiness | Days = | ||||

A | 3 days | 15 | 6 | Job # = | ||||

B | 7 days | 20 | 8 | |||||

C | 6 days | 30 | 5 | |||||

D | 4 days | 20 | 3 | |||||

E | 2 days | 22 | 7 | |||||

F | 5 days | 20 | 5 | |||||

Average | ||||||||

Instructions: | ||||||||

1) | Sort Job Numbers in table above in the correct order based on the Priority Rule = Earliest Delivery Date hblowell: hblowell: Remember to keep all the data together for each Job when you sort. |
|||||||

2) | Complete Job Number Mapping in columns J-AJ by showing correct order of jobs and duration of each job | |||||||

3) | Complete cells highlighted in green in the table (E-H) above | |||||||

For the following questions, calculate the answer and enter that in column C (Show your calculations): | ||||||||

3) | Make Span = | |||||||

4) | Job Flow Time = | |||||||

5) | Average # Jobs in System = |

# CR

Job Number | Job Time | Due Date | Remaining Job time at other WS | Job Flow | Job | Job | Critical Ratio | Job Number Mapping |

(Set-up & run) | (days from now) | Lateness | Tardiness | Days = | ||||

A | 3 days | 15 | 6 | Job # = | ||||

B | 7 days | 20 | 8 | |||||

C | 6 days | 30 | 5 | |||||

D | 4 days | 20 | 3 | |||||

E | 2 days | 22 | 7 | |||||

F | 5 days | 20 | 5 | |||||

Average | ||||||||

Instructions: | ||||||||

1) | Sort Job Numbers in table above in the correct order based on the Priority Rule = Earliest Delivery Date hblowell: hblowell: Remember to keep all the data together for each Job when you sort. |
|||||||

2) | Complete Job Number Mapping in columns J-AJ by showing correct order of jobs and duration of each job | |||||||

3) | Complete cells highlighted in green in the table (E-H) above | |||||||

For the following questions, enter the answer in column C: | ||||||||

3) | Make Span = | |||||||

4) | Job Flow Time = | |||||||

5) | Average # Jobs in System = | |||||||

6) | Explain what type of company or what scenario would want to use EDD instead of CR as the priority rule and why. |

# level schedule data

Products | Forecasts (Year) | ||||

Z | 1000 | ||||

Y | 3000 | ||||

X | 7000 | ||||

W | 12000 | ||||

Z | Y | X | W | ||

Year Forecast | 1000 | 3000 | 7000 | 12000 | |

Daily Batch | divide by ? | ||||

Hourly Batch | divide by ? | ||||

Minimum Batch MPS | Relationship of smallest to others | ||||

Instructions: | |||||

1) Replace the “?” above in cells D9 and D10 with the correct number. | |||||

2) Complete table for products Z-W (E9:W11). | |||||

3) Answer the following questions. Put answer in the green cells in column E. | |||||

What is the Minimum Batch MPS? | |||||

How often need to repeat the Minimum Batch MPS per day? |

â€¢ Company produces four products with a mixed model assembly line. â€¢ Operates 12 hours per day for 250 days/yr. â€¢ Determine the mixed model MPS for a daily and hourly batch . â€¢ Determine mix schedule for a day.

# DNA

Answer each of the following questions by placing an “X” next to the best response, based on your reading of “Decoding the DNA of the Toyota Production System”. | |

0. | example: How many dogs does Prof. Lowell have? |

A) 1 | |

X | B) 2 |

C) 3 | |

D) Zero, she has cats. | |

1. | Products and services follow simple, specified paths. This describes Rule #_____. |

A) 1 | |

B) 2 | |

C) 3 | |

D) 4 | |

2. | Rule 4 says improvements are made using trial and error, under guidance of a teacher, and at the lowest possible organization level. |

A) True | |

B) False | |

3. | _______________________ was an original architect of the Toyota Production System. |

A) Fujio Cho | |

B) Hajime Ohba | |

C) Taiichi Ohno | |

D) Steven Spear | |

4. | Name three circumstances that may require a company to hold inventory. |

A) unpredictable downtime, unknown yields and long set-up times | |

B) volatility of product mix, known customer demands, and 100% yield | |

C) small batch sizes, immediate delivery, and defect free | |

D) none of the above | |

5. | According to the authors of “Decoding the DNA of the Toyota Production System”, why has it been difficult for other companies to duplicate Toyota’s success? |

A) Other companies don’t realize activities, connections and flows are rigid with no flexibility | |

B) Toyota is very secretive about what goes into the Toyota Production System | |

C) Other companies confuse the tools and practices of the Toyota Production System as being the system itself | |

D) All of the above | |

6. | The Toyota Production System creates a community of ________________. |

A) Students | |

B) Teachers | |

C) Learners | |

D) Scientists | |

7. | A set of instructions with details on how to complete a task and how long it should take is an example of ________________________. |

A) Rule 1 | |

B) Rule 2 | |

C) Rule 3 | |

D) Rule 4 | |

8. | Kanban cards and andon cords establish links between customers and suppliers. This is a concept of ______________________. |

A) Rule 1 | |

B) Rule 2 | |

C) Rule 3 | |

D) Rule 4 | |

9. | How does Toyota teach new employees about the rules of the Toyota Production System? |

A) Rigorous training classes | |

B) Sends them to the OMCD center | |

C) Gives new employees a manual | |

D) Asks questions (socratic method) |

# EOQ

EOQ | Lead-time = 1 week | |||||||||

1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | |

Gross requirements | 80 | 110 | 80 | 20 | 140 | 60 | 80 | 40 | 0 | 40 |

Scheduled receipts | ||||||||||

Projected on hand | 95 | |||||||||

Net requirements | ||||||||||

Planned order receipts | ||||||||||

Planned order releases | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

total cost = | ||||||||||

Holding cost = $1.50/week; Setup cost = $75; | ||||||||||

Instructions: | ||||||||||

1) Calculate EOQ based on the following inputs, using formula shown at right: | ||||||||||

D = 65 units per week | ||||||||||

S = set-up cost | ||||||||||

H = holding cost | ||||||||||

2) Fill in the table above (Net Requirements, Projected on-hand, Planned Order Receipts) using your calculated EOQ as the Lot Size | ||||||||||

–> Note: Projected On-Hand = amount of material left in inventory at the end of the period. | ||||||||||

3) Determine Total Cost for this lot size strategy and enter that in the Total Cost cell in L9. |

# LTC

Least Total Cost | lead-time = 1 week | |||||||||

1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | |

Gross requirements | 80 | 110 | 80 | 20 | 140 | 60 | 80 | 40 | 0 | 40 |

Scheduled receipts | ||||||||||

Projected on hand | 95 | |||||||||

Net requirements | ||||||||||

Planned order receipts | ||||||||||

Planned order releases | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

total cost = | ||||||||||

Holding cost = $1.50/week; Setup cost = $75; | ||||||||||

Instructions: | ||||||||||

1) Determine Net Requirements, then determine Planned Order Receipts based on Least Total Cost lot size strategy. | ||||||||||

– Use tab called “LTC-side” to determine how to group requirements | ||||||||||

2) Fill in the table above (Net Requirements, Projected on-hand, Planned Order Receipts) using the lot size groups that you determine for the Least Total Cost lot size strategy. | ||||||||||

–> Note: Projected On-Hand = amount of material left in inventory at the end of the period. | ||||||||||

3) Determine Total Cost for this lot size strategy and enter that in the Total Cost cell in L9. |

# LTC-side

Least Total Cost | ||||||

Week | Net Req hblowel I : hblowel I : calculate the Net Requirements on the LTC tab as though using L4L method. Then copy those Net Requirements to column B to figure out the grouping that give you the lowest total cost. |
Order Grouped | Holding Cost | Order Cost | Total Cost | Holding cost = $1.50/week; Setup cost = $75; |

1 | ||||||

2 | ||||||

3 | ||||||

4 | ||||||

5 | ||||||

6 | ||||||

7 | ||||||

8 | ||||||

9 | ||||||

10 |

# L4L

Lot-for-Lot | Lead-time = 1 week | |||||||||

1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | |

Gross requirements | 80 | 110 | 80 | 20 | 140 | 60 | 80 | 40 | 0 | 40 |

Scheduled receipts | ||||||||||

Projected on hand | 95 | |||||||||

Net requirements | ||||||||||

Planned order receipts | ||||||||||

Planned order releases | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

total cost = | ||||||||||

Holding cost = $1.50/week; Setup cost = $75; | ||||||||||

Instructions: | ||||||||||

1) Fill in the table above (Net Requirements, Projected on-hand, Planned Order Receipts) using “Lot-for-Lot” lot size strategy. | ||||||||||

–> Note: Projected On-Hand = amount of material left in inventory at the end of the period. | ||||||||||

2) Determine Total Cost for this lot size strategy and enter that in the Total Cost cell in L9. |

# Best Lot Size

Instructions: Put an “X” in the yellow shaded area next to your answer. |

Which Lot Size strategy has the Lowest Cost? |

A) EOQ |

B) LTC |

C) Lot-4-Lot |