Dear Members,
Great Day!!!
I need excel model to forecast future incoming stock and allocating it to sales forecasted or Purchase Orders received from customers
I am using SAP MM & SD module in my business. The system could support me with current incoming stock and allocation of the stocks. Unfortunately, it could not support the plans for future incoming stocks and future delivery plans. Basically, I need Excel Model that could support me with the stock forecasting.
Business background:
1. I have about 500 SKU in my stock
2. I categorize my Stocks in to 4; where A – Fast Moving Product; B- Average Moving Product & C – Slowing Moving Product & D- Very Slow-Moving Product
3. We operate in 3 warehouses namely, Central, North & South
4. All items are received in Central warehouse and transferred weekly basis (based on demand) to north and south warehouse.
5. We purchase our product from Local Suppliers and Import from oversea which have various delivery lead time (2 week to 4 months)
6. We are a trading house in the country
Information available to feed the system:
1. Product master date from SAP
2. Customer master data from SAP
3. Supplier master date from SAP
4. Stock balance from SAP
5. Goods in Transit (with ETA date)
6. Sales forecast by customer by SKU for 4 months (rolling)
7. Minimum stock required
Requirement:
1. The excel only cater for stock quantity as the value of the stock are taken care through SAP
2. Able to upload information such as Stock balances, GIT in transit (with ETA date), sales forecast by date (next 4 months) from excel.
3. Able to key in purchase order from customer (who is tagged to branch) with delivery date and reserve the stock for customer
4. Able to generate a report by daily basis for next 4 months (future) where current stock balances by location, goods in transit, customer orders and stock available (+/-) by location are show on one sheet.
5. Able to generate a stock balance report for any particular date for the next 4 month.
6. Able to generate stock card where stock movement and customer who bought it is listed.
7. Able to generate a report to measure stock availability by category (A, B, C & D) (order/sales forecast vs stock balance)
8. Able to do Interbranch transfer through the system.
9. Able to find which customer the current stock or future incoming stocks are reserved for and reassign it to other customer if required.
Can anyone help me to create a excel/vba file to solve my above requirement??? I willing to pay for the service. Please contact me.
Bookmarks