Question

Prepare a master schedule given this information: It is now the end of week 1; customer orders are 25 for week 2,16 for week 3, 11 for week 4, 8 for week 5, and 3 for week 6. Use the MPS rule of ordering production when projected on-hand inventory would be negative without production. Suppose that there are currently 64 pumps in inventory and a production lot size of 70 pumps is used. (Leave no cells blank-be certain to enter "O" wherever required.) 

Answer 1

Please Note the formulas to be used

MPS will be provided in a lot of 70 when the available or (projected on-hand inventory becomes 0 or negative

(Please note I have used Available in place of Projected -On-Hand Inventory(same))

The available row can be calculated by

Current period available = Previous period available + MPS – (Greater of forecast or orders)

For example in in 3rd period you consider from the solution provided previous period available = 1

MPS = 70 and the maximum between Customer order(16) and forecast(30) is 30 so the Projected on hand is

1+70-Max(30,16) = 41

For ATP Calculation

ATP is available to promise for the customers

Fist period ATP = On-hand + any first-period MPS -$\sum$ (all orders until the next MPS)
Later period ATP = MPS in that period - $\sum$ (all orders in that and subsequent periods until the next MPS)

For example first period ATP = Onhand(64) - No MPS in 1st Period (0)- sum of all orders till 3rd period (33+25) = 6

ATP of 3rd period = MPS in 3rd Period (70)- Sum of all orders till next MPS( 16+11)= 43

Book1 Microsoft Excel Home InsrtPage Layout Formulas Dat Revie Vew SplitVew Side by Side 团Ruler 図Formula Bar Hide Symchronous Scrolling Page Page Break CustomFull V Gridlines図Headings Zoom 100% Zoomto New Arrange Freeze Layout Preview Views Screen witch Macros Selection Window A PanesUnhide Reset Window Position Workspace Windows - Workbook Wews Show Window Macros Customer orders Projected Onhand MPS ATP 25 16 31 41 41 31 61 On hand Lot size 12 13 17 21 25 4 SheetiSheet2 Sheet3 Ready 100%

Bookd Microsoft Excel Home InsrtPage Layout Formulas Data Revie View 園 ヨーコHide Split H View side by Hide Symchronous Scrolling ave chronousside ing Ruler 図Formula Bar Page Page Break CustomFull Layout Preview V Gridlines図Headings Zoom 100% Zoom to New Arrange Freeze witch Macros Views Screen Selection Window A PanesUnhide Reset Window Position Workspace Windows - Workbook Wews Show Zoom Window Macros 18 Customer orders Projected Onhand MPS ATP 33 25 16 -64-MAx(C3:C4) -31-MAx(D3:D4) =E5+D5-MAX(E3:E4) =E5-MAx(F3:F4) =G64f5-MAx(G3:G4) -65-MAx(H3:H4) =164H5-13 s]6415-13 -C9-SUM(C4:D4) 0 -E6-SUM(E4:F4) On hand Lot size 12 13 15 16 17 21 23 25 4 SheetiSheet2 Sheet3 Ready 100%
I hope you got a better idea from the explanation

Please comment if any doubt and Rate me

Thanks