Question

needing help with Excel

3. Complete the master production schedule based on the following information: 1 4 9 2 3 320 100 30 60 50 80 110 5 6 7 40 240 290 60 60 60 410 20 Week Forecast Customer Orders Projected On-Hand Inventory MPS Released MPS Due Available to Promise (ATP) Additional Information: On-hand inventory: 600 Schedule production whenever projected on-hand inventory drops below 30 MPS lot size: 300 Production lead time or lead time for MPS releases: 1 week

Answer 1

A B с D E F G H 1 J K L M N 0 1 2 3 4 5 2 3 4 5 6 9 1 320 7 290 8 60 6 100 30 110 240 410 40 60 7 60 50 80 20 60 Week Forecast Customer Orders Projected on-hand Inventory MPS Released MPS Due Available to Promise 280 180 100 290 230 290 300 240 130 9 0 0 0 300 0 300 300 0 300 10 0 0 300 0 300 300 0 300 0 11 540 * 220 * 240 300 * 300 12 13 ) =600+F9-=F8+G9 =G8+H9- =L8+M9 =M8+N9- Formula Used for Projected on =H8+19 =18+J9 =18+K9 =K8+L9- hand inventory MAX(F7,F MAX(G7,G MAX(H7, H6 MAX(M7,M MAX(N7,N MAX(17,16) MAX(17,36) MAX(K7,K6) MAX(L7,L6) 6) 6) 6) 6) Formula Used for MPS =IF(F8<MA =IF(G8<MA =IF(H8<MA =IF(18<MA EIF(J8<MAX =IF(K8<MA =IF(L8<MAX =IF(M8<MA 0 X(G6, G7)+3 X(H6, H7)+3 X(16,17)+30, X(36,37)+30 (K6,K7)+30, X(L6,L7)+30 (M6, M7)+30 X(N6, N7)+3| Released 0,300,0) 0,300,0) 300,0) ,300,0) 300,0) ,300,0) ,300,0) 0,300,0) Formula Used for MPS Due =G9 =H9 =19 =19 =K9 =L9 =M9 =N9 =09 14 15 16

All the formulae used are mentioned in the excel sheet.

********

IF YOU HAVE ANY DOUBT, KINDLY COMMENT. I WOULD LOVE TO HELP YOU!!!

IF YOU LIKE THE ANSWER, PLEASE GIVE AN UP-VOTE OR THUMB UP. THIS WILL ENCOURAGE ME TO ANSWER MORE!!