Excel work

Planning.final_MPS.ROP.MRP.JITlevel.shopfloor.DNA.lotsize-D2L.xlsx

Home >Business & Finance homework help >Management homework help >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