supply chain

mathgenuis2021

attachment_211.pdf

Home >Business & Finance homework help >Management homework help >supply chain

SCM 453, Sections 2 & 3: Supply Chain Planning Group Assignment #2, Part 1 Fall 2021

This assignment is to be submitted online via the Canvas learning system by the due date and time posted

on Canvas. A single person from each group should submit two files online. One file should be an Excel

workbook containing all relevant and completed Excel worksheets associated with the assignment. The

second file should be an appropriately formatted PDF document containing all the relevant answers to

questions 1 to 4 and references to relevant cells in the Excel worksheet where appropriate. Any diagrams

or tables which are included as part of the PDF document, should be formatted to look professional. Points

may be deducted for formatting related issues. Not following directions will result in a penalty of

undetermined size.

Materials Requirements Planning at A-Cat Corp Case Analysis Questions

Read the Materials Requirements Planning at A-Cat Corp case in the online coursepacket. Download

the Excel template for the A-Cat Corp case from Canvas. Assume that the current status of A-Cat Corp is

as described in the case and that the analysis is being performed by “a colleague of Menon who is skilled

in operations management”. Fill in the Excel spreadsheet with the appropriate data and formulas to

compute the required information. Note that the excel file has THREE worksheets which need to be

completed. Use the following tables for Exhibits 1, 3, 4 and 5 instead of the Exhibits 1, 3, 4 and 5

tables found on pages 4 and 5 in the case. Assume that a minimum safety stock level of 21 units is

required for each part number (i.e., P0110, P0X, P0Y, P1X1, P1X2).

Exhibit 1: Bill of Material

ITEM NO

PART NO

QTY LEVEL LEAD TIME

ON HAND INVENTORY

LOT SIZES

1 P0110 1 0 2 300 622

2 POX 2 1 1 250 680

3 POY 2 1 2 420 850

4 P1X1 4 2 1 850 2500

5 P1X2 3 2 2 980 3100

Exhibit 3: Master Schedule

WEEK 1 2 3 4 5 6 7 8

WEEKLY NET REQUIREMENTS

400 350 300 450 300 450 225 925

Exhibit 4: Inventory level of components

ITEM NO

ON HAND INVENTORY

1 300

2 250

3 420

4 850

5 980

SCM 453, Sections 2 & 3: Supply Chain Planning Group Assignment #2, Part 1 Fall 2021

Exhibit 5: Costs Associated

ITEM COST PER UNIT (RS) SET UP COST (RS)

P0110 125 350

POX 70 120

POY 90 130

P1X1 20 450

P1X2 30 330

Inventory carrying cost rate = 0.4 percent.

1. Complete the current MRP for A-Cat Corp using the details provided in Exhibits 1, 3, 4 and 5

above, Exhibit 2 in the case, and the template in the worksheet titled “Current MRP”. Since your

completed Excel file will be included as part of your assignment submission, DO NOT include a

copy of the MRP tables in your PDF document for Part 1 of the assignment. Use the following

assumptions in creating your MRPs:

Assumption 1: For each part, there is a scheduled receipt to arrive in the first period

which is equal to the lot-size used for that part.

Assumption 2: Any orders placed for a part must be for the lot-size amount indicated in

Exhibit 1 for that part.

Note that for an MRP, a positive projected available balance in a period means that there are

some units expected to remain in inventory at the end of that period (i.e., ending inventory). A

negative (or zero) projected available balance, in a period, would mean that there are no units

expected to remain in inventory at the end of that period. Average inventory is the average of

the ending inventory in the previous period and that of the current period (except in the first

period where it is the average of the on-hand inventory and the ending inventory for the first

period). Total costs is the sum of ordering/setup and holding costs for the entire MRP period

(i.e., eight weeks). Holding costs should be based on average (not ending) inventory values.

a. Provide the total cost values for each of the MRPs that you created.

b. Based on Assumption 1 and Assumption 2, it is possible that some of the parts in

your final MRP may contain projected available balances which are less than the

required safety-stock level. In practice, such situations in an MRP would generate an

exception code indicating that the MRP is not feasible in its current form. Which of

the MRPs that you created (if any) is not feasible in its current form?

c. For any MRP which is currently infeasible, relax Assumption 1 so that a single

scheduled receipt of any size can be delivered in period 1, and provide the

minimum scheduled receipt (i.e., quantity) that would make that MRP become

feasible. Re-do the relevant MRP(s) using your scheduled receipt choice(s) and

provide the resulting total cost(s). Describe what may need to happen in practice for

the scheduled receipt(s) in Assumption 1 to be converted to your chosen scheduled

receipt(s).

SCM 453, Sections 2 & 3: Supply Chain Planning Group Assignment #2, Part 1 Fall 2021

2. As detailed in the case, Sunita Menon is looking to use various lot-sizing techniques to try and

reduce production costs. Complete the tables in the excel worksheet titled “P0110 Lot-size

Calculations” to determine the total costs (i.e., ordering + holding costs) for the P0110 part using

three popular lot-sizing methods (i.e., EOQ, POQ and PPB). EOQ is the economic order quantity,

POQ is the periodic order quantity and PPB is the part period balancing technique. Assume that

any order made in a period is available for use in that period (i.e., ignore lead time concerns in

your lot-size computations). Note that for period-based order quantities (i.e., POQ and PPB) the

first order quantity can take the on-hand inventory into account. Consider the 21-unit safety

stock requirement in relevant lot-size calculations for the first-period order quantity, only. Do

not include the cost of holding safety stock in your PPB calculations. Based on your completed

“P0110 Lot-size Calculations” worksheet, provide the total cost for each lot-sizing technique,

and indicate which technique(s) results in the lowest total cost. Total cost is the sum of

ordering/setup and holding costs for the entire MRP period (i.e., eight weeks). Holding costs

should be based on average (not ending) inventory values. Which of the three popular lot-sizing

techniques (i.e., EOQ, POQ and PBB) is the “best” to use for product P0110? Provide a

justification for your answer based on your analysis along with any insights from the MRP

material covered in your SCM 453 class.

3. Based on your answer to #2, use your chosen technique to complete the worksheet titled “Best

P0110 Lot Size_MRP”. The two assumptions in #1 still apply, with the lot-size(s) for P0110 now

being the value(s) from your chosen lot-sizing technique instead of the value listed in Exhibit 1.

Note that the new lot-sizing only applies to P0110, but it will lead to changes in the gross

requirements of certain parts. For the worksheet titled “Best P0110 Lot Size_MRP” continue

using the existing lot-sizes in Exhibit 1 for all other parts (i.e., P0X, P0Y, P1X1, P1X2).

a. Provide the total cost values for each of the five newly created MRPs.

b. Did the new lot-sizing approach lead to a reduction in total costs from their current

levels? Explain why the new lot-sizing approach either led to a reduction in total costs or

an increase in total costs.

c. Which of the five new MRPs that you created (if any) is not feasible in its current form?

How does this compare to your answer in #1b?

4. What other actions could be taken on the MRPs in #3 to i) further reduce the total costs and ii)

make any infeasible MRP in #3 become feasible? (if all the MRPs in #3 are already feasible then

only focus on objective i). List two or more actions along with the total cost of the resulting MRP

for each part, when the actions are collectively applied (note: the same action performed on

two separate MRP tables would count as two actions). Also note that Assumption 1 and

Assumption 2 do not need to apply to your chosen actions. Support your answer by creating a

new worksheet titled “MRP with new lot sizes”, which incorporates your suggested actions

(note that you can create the new worksheet by making a copy of the “Best P0110 Lot

Size_MRP” worksheet and then making appropriate modifications). Include the new worksheet

as part of the Excel workbook that you submit for the assignment. Describe what may need to

happen in practice for your suggested actions to be implemented.