complete the MPS plans for both end items and to create material requirements plans (MRP) for any 5 items in the BOM.

see the requirements in the word , then complete the excel sheet.

BA

Assignment 2

Clodhopper Clodbuster

Background:

Your company has just acquired a new subsidiary that makes two products, Clodhopper and Clodbuster. The Clodhopper is intended for the general consumer market and its demand is higher and more seasonal than the Clodbuster, which is intended for the commercial user market. Both products share a number of component parts and subassemblies, primarily differing in their engine and tiller subassemblies. You have been assigned to run this acquisition. The only planning information you have is historical data.

Assignment:

Your task is to complete the MPS plans for both end items and to create material requirements plans (MRP) for any 5 items in the BOM.

· First complete all of the information in the MPS plans.

· Then, using the MRP forms supplied in the spreadsheets. Fill in all appropriate data (lot size, ordering rule, etc).

· Do not make any changes to the format/layout of the spreadsheet. This is one place in industry where creativity in presenting data is not appreciated since many of the cells in the forms are normally linked, and changes can lead to significant errors in the results.

· Use the comment function to show the formula for “projected on-hand inventory.” The formula must be understandable (e.g. ”beginning inventory + production…” NOT “D12 + E12…”) and a comment must be entered in each cell that you calculate projected on-hand inventory.

Data:

The “Assignment 2 ” spreadsheet contains
five
different worksheets:

· MPS plans for Clodhopper and Clodbuster for the first three months of 2018.

· Bill of materials data for ClodHopper and ClodBuster, including quantities required per product and assembly times/lead times

· Inventory records and lot-sizing rules for all subassemblies and component parts

· Scheduled receipts (production commitments and purchase orders that have been scheduled by previous MPS and MRP plans for delivery in 2018.

· MRP forms to use for your choice of five items in the BOM

Additional information:

· Remember that the FOQ lot-sizing rule allows multiple orders. That is, if your plan requires 450 units in a given period, current inventory is 75 units, and the FOQ order size is 100 units, then you would request 400 units (four orders) from the vendor or assembly production line.

· For items purchased using the POQ lot-sizing rule, consider that the time between orders begins at the start of the plans. That is, if the POQ period is 3, you can only have planned receipts (or scheduled receipts) in periods 1, 4, 7, and so on. Unlike the other ordering schemes where you first determine when a new order must be received and then schedule the order earlier according to the lead time, for POQ you look at future needs that must be satisfied until another POQ order can be placed.

· Do not schedule MRP planned orders in the line for scheduled receipt items. Scheduled receipts have already been started or ordered on previous MRP plans for delivery in 2018 and you are not allowed to make any additional entries on this line. The values on this line are to be added to the projected on-hand inventory when each scheduled receipt arrives. Do not reflect the scheduled receipts into planned order releases.

· Some parts and subassemblies have desired safety stock levels. Be sure to order enough or early enough to ensure these levels are maintained, limited only by any lead-time constraints. When there is no safety stock, maintain the projected on-hand greater than or equal to zero.

· Use the power of Excel as much as possible for filling out your MPS and MRP forms. Doing so will save you considerable time in completing your assignment and is representative of how these processes are done in industry. Use the comment function to show the formula for “projected on-hand inventory” calculations for both the MPS and MRP forms.
Hint: Entering new order quantities (planned receipts) when needed is best done manually whenever the on-hand inventory values indicate that you would not have enough unless an order is placed.

Assignment 2 Grading:

There are 50 points possible on this assignment. Grading criteria include proper application of the planning methods, following all directions, and professional appearance of your work, including application of Excel.

Use the Excel file: BA 314 Assignment 2 W18 for the required information and forms

Clodhopper
A
Handlebar Assembly
C
Powerhead Assembly
H
Instruction Manual
M
Steel Tubing
D
Control Cable
E
Control
Assembly
F
Tiller Blade
Unit
K
Engine
(standard)
J
Gearbox
Assembly
I
Grips
G
Clodhopper BOM
Clodbuster
B
Handlebar Assembly
C
HD Powerhead
Assembly
P
Instruction Manual
M
Steel Tubing
D
Control Cable
E
Control
Assembly
F
HD Tiller
Blade Unit
N
Engine
(heavy duty)
L
Gearbox
Assembly
I
Grips
G
Clodbuster BOM
Clodhopper
A
Handlebar Assembly
C
Powerhead Assembly
H
Instruction Manual
M
Steel Tubing
D
Control Cable
E
Control Assembly
F
Tiller Blade Unit
K
Engine (standard)
J
Gearbox Assembly
I
Grips
G
Clodhopper BOM
Clodbuster
B
Handlebar Assembly
C
HD Powerhead Assembly
P
Instruction Manual
M
Steel Tubing
D
Control Cable
E
Control Assembly
F
HD Tiller Blade Unit
N
Engine
(heavy duty)
L
Gearbox Assembly
I
Grips
G
Clodbuster BOM

2

>

M P

S –

A

pproved B

A 3

1

4 Assignment 2 – Worksheet 1: MPS Approved plan I

tem: A C

lodhopper (Winter2

0

18) L

ot Size Rule: Lead Time

: Safety Stock Level: Current Inventory: J

an 2018 F

eb 2018 Mar 2018 Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9 Week 10 Week 11 Week 12 Forecast 4

50 450

450 450

600

600 600 600

2

400 2

500 2600 2

700 Customer Orders Booked 150 350

500 600 0

200 1000

0 0

2200 2000

0
Projected On-

H

and Inventory

MPS

Scheduled Receipts

MPS

Quantity MPS Start Available to Promise Item

: B Clodbuster (Winter2018) Lot Size

Rule:
Lead Time:

Safety Stock Level:
Current Inventory:

Jan 2018 Feb 2018

Mar 2018
Week 1

Week 2

Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9 Week 10 Week 11 Week 12

Forecast

300

300 300 300 400 400 400 400 1000

1100

1100

1200 Customer Orders Booked 200 0 450 0 50

900

0 0 500 0 0 0
Projected On-Hand Inventory MPS Scheduled Receipts

MPS Quantity

MPS Start

Available to Promise

BOMs

ata

BOM (Product A)

ame

Quantity

Lead Time

A ClodHopper 1

C

1

D

2 weeks

F

1 1 week

2 1 week

H

1 1 week

I

1 1 week

J

1 2 weeks

1 1 week

M

1

BOM (Product B)

Quantity Assembly Time Lead Time

B ClodBuster 1 1 week

C Handle Bar Assembly 1 2 weeks
D Steel Tubing 1″ dia 10 feet 3 weeks
E Control cable 8 feet 2 weeks
F Control lever assembly 1 1 week
G Grips 2 1 week

P

1 1 week

I Gearbox assembly 1 1 week

L

1 3 weeks

N

1 2 weeks

M Instruction Manual 1 4 weeks

BA 314 Assignment 2 – Worksheet 2: Bill of Materials

D

ClodHopper

N

Assembly Time

1 week

Handle Bar Assembly

2 weeks

Steel Tubing 1″ dia

10

feet

3 weeks

E

Control cable

8 feet

Control lever assembly

G

Grips

Powerhead assembly

Gearbox assembly

Engine (Standard)

K

Tiller blade unit

Instruction Manual

4 weeks

ClodBuster

Name

HD Powerhead assembly

Engine (Heavy Duty)

HD Tiller Unit

Inventory Records

Item Name

Lot Size

A ClodHopper

1 week

B ClodBuster

FOQ 1000 1 week

C Handle Bar Assembly

2 weeks 1000

D Steel Tubing 1″ dia

3 weeks

feet

E Control cable

FOQ 20,000 2 weeks

feet

F Control lever assembly 2500 L4L 1 week
G Grips 2000 L4L 1 week
H Powerhead assembly 700 FOQ 4000 1 week
I Gearbox assembly

FOQ

1 week 750

J Engine (Standard) 500 FOQ 2500 2 weeks 750
K Tiller blade unit

1 week 750

L Engine (Heavy Duty) 450 FOQ 1000 3 weeks 500
M Instruction Manual

FOQ 10,000 4 weeks 2500

N HD Tiller Unit

2 weeks 500

P HD Powerhead assembly 900 FOQ 2000 1 week

BA 314 Assignment 2 – Worksheet 3: Inventory Records and Lot-Sizing Rules
Record Date:	31-Dec-17
Current Inventory On-Hand	Lot-Size Rule	Lead or Assembly Time	Desired Safety Stock Level	Units
5500									FOQ								2500	1500
	4000				750
3100			L4L
50,000	POQ Period = 4 weeks			20,000
5,000			10,000
650		5000
850	POQ Period = 2 weeks
6000
1250	POQ Period = 3 weeks

Scheduled Receipts

Item Name Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9 Week 10 Week 11 Week 12
A ClodHopper
B ClodBuster
C Handle Bar Assembly
D

E

20,000

F Control lever assembly
G Grips
H Powerhead assembly 200
I Gearbox assembly 5000
J Engine (Standard) 2500 2500 2500 2500
K Tiller blade unit
L Engine (Heavy Duty) 1000 1000
M Instruction Manual 10,000
N HD Tiller Unit
P HD Powerhead assembly 1000

BA 314 Assignment 2 – Worksheet 4: Scheduled Receipts
Note: These are purchase order and production commitments scheduled in 2017 that are already scheduled for completion in 2018. The sizes of the orders or production lots are listed by week of scheduled delivery. Include these quantities in “Projected on-hand inventory” – do not include them in “Planned order releases.”
Steel Tubing 1″ dia (feet)	100,000
Control cable (feet)

MRP Form – Results

Lead Time:
Safety Stock Level:
Current Inventory:
Jan 2018 Feb 2018 Mar 2018
Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9 Week 10 Week 11 Week 12

Scheduled Receipts

Projected On-Hand Inventory

Lot Size Rule:
Lead Time:
Safety Stock Level:
Current Inventory:
Jan 2018 Feb 2018 Mar 2018
Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9 Week 10 Week 11 Week 12

Gross Requirements

Scheduled Receipts

Projected On-Hand Inventory

Planned Receipts

Planned Order Releases

Item: Lot Size Rule:
Lead Time:
Safety Stock Level:
Current Inventory:
Jan 2018 Feb 2018 Mar 2018
Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9 Week 10 Week 11 Week 12

Gross Requirements

Scheduled Receipts

Projected On-Hand Inventory

Planned Receipts

Planned Order Releases

Item: Lot Size Rule:

Lead Time:
Safety Stock Level:
Current Inventory:
Jan 2018 Feb 2018 Mar 2018
Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9 Week 10 Week 11 Week 12

Gross Requirements

Scheduled Receipts

Projected On-Hand Inventory

Planned Receipts

Planned Order Releases

Item: Lot Size Rule:
Lead Time:
Safety Stock Level:
Current Inventory:
Jan 2018 Feb 2018 Mar 2018
Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9 Week 10 Week 11 Week 12

Gross Requirements

Scheduled Receipts

Projected On-Hand Inventory

Planned Receipts

Planned Order Releases

BA 314 Assignment 2 – Worksheet 5: MRP Forms
			Item:					Lot Size Rule:
				Gross Requirements
				Planned Receipts
				Planned Order Releases
Item: