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 |