Inventory Excel Spreadsheet
This project received 18 bids from talented freelancers with an average bid price of $86 USD.Get free quotes for a project like this
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.
Looking to make some money?
- Set your budget and the timeframe
- Outline your proposal
- Get paid for your work
Hire Freelancers who also bid on this project
Looking for work?
Work on projects like this and make money from home!Sign Up Now
- The New York Times
- Wall Street Journal
- Times Online