Inventory Spreadsheet Requirement
Attached is the first line of an Excel based Inventory system that we are developing. As part of writing this speadsheet we are wanting to calculate the required reorder quantities and times.
In particular we require a formula that will calculate the Inventory reorder point and the required quantity when forecasted sales exceed available inventory. The formula's will be located in Cells CW4 - DS6. The variables are as follows:
1) Forecasted Weekly Sales (Cells A4:W4) - Units of inventory stock items sold in a week
2) Lead Weeks (Cell X4) - How many weeks it takes for product to be delievered after the time of ordering. A dropdown box allows up to 23 weeks (Data source "Leadtime" Cells A29:A51)
3) MOQ (Cell CV4) - Minimum amount that can be ordered from the Supplier
a) In the event of an order being greater than the MOQ we want it to round up to the nearest 5 units
b) We have only shown sales forecast for 23 weeks in this example but in fact we are wanting to use 53 weeks in the final version.
In simplfied terms the formula should be as follows:
Lead Time = LT
Sales Weekly Quantities = SWQ (Selected by LT date range)
Opening Inventory = OI
Minimum Order Requirement = MOQ
1) Select SWQ according to Lead Time value
2) Calculate SWQ - Opening Inventory (for the week being measured) = Stock Required
3) If Stock Required > than Opening Inventory then calculate, Stock Required - Opening Inventory =
4) MOQ - Order Requirement = Order 2
5) If Order2 > MOQ then return Order 2 (rounded to nearest 5) as the final result otherwise return
MOQ as the result
Once the model is operational actual weekly sales will be imputed to replace the budgeted values. In some instances this may result in an a "Inventory Out" situation caused by actual sales being higher than those previously forecasted and lead time being greater than the available time for new stock to arrive. In this situation we require a message "Warning Inventory Outage"
I have only shown sales forecasts for 23 weeks in the example above however we will in fact want to be able to budget for a full year (52 weeks)
We expect to run 3- 400 stock items in this inventory spreadsheet.
We need this be done ASAP. Please bid on the completion of the spreadsheet and notify the time.
17 freelancers are bidding on average $85 for this job
This sounds easy enough. I've done some similar spreadsheets for classes I took at MIT. It would be pretty easy to get this back to you next day. Looking forward to working with you.
Hello! I am very experienced with inventories, pars, sales forecast, and excel spreadsheets. I would be able to complete your project within 2 days. My experience comes from restaurant management.