Inventory Excel Spreadsheet

Closed

Description

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

Other requirements

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:

Definitions

Lead Time = LT

Sales Weekly Quantities = SWQ (Selected by LT date range)

Opening Inventory = OI

Minimum Order Requirement = MOQ

Possible Calculation

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 =

Order 1

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

Observation

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.

Ps:

We need this be done ASAP. Please bid on the completion of the spreadsheet and notify the time.

Skills: Excel, Inventory Management, Mathematics, Visual Basic

See more: excel formula moq requirements, excel inventory spreadsheet, formula calculate moq excel, excel inventory reorder formula, inventory spreadsheet example, inventory sales spreadsheet, inventory order calculation spreadsheet, moq lead time formula, inventory event speadsheet, reorder point excel formula, sales budget calculation excel, excel spreadsheet inventory system, calculate moq, on line supplier, on line stock supplier, formula writing, excel out source, lead time formula excel, excel inventory outage, excel spreadsheet formula, budget spreadsheet, x4, visual lead, spreadsheet 2, Speadsheet

Project ID: #2621360

17 freelancers are bidding on average $85 for this job

mrbrave

Lets start...

$110 USD in 1 day
(72 Reviews)
5.4
FIBIN

Hi, I am an expert in excel and VBA Programming in Excel. I am interested to work for your project .I have experience working on lot of Excel based projects. I will make sure that I deliver you the best with 100% ac More

$100 USD in 3 days
(42 Reviews)
5.3
naveenvarthaan

Hi, I am online and I started to work on this project with the information you have provided in the project description , as you have said that it is URGENT. I will send a sample in one hour. I can complete this More

$100 USD in 1 day
(13 Reviews)
4.2
kmittal

Have overall 4.9* rating. Willing to take it immediately.

$100 USD in 1 day
(6 Reviews)
4.1
FLtowhid

Please see my PM.

$50 USD in 4 days
(4 Reviews)
3.8
rengrish

Hi Excel Expert Here. I can do this within 1 day.

$40 USD in 1 day
(17 Reviews)
3.7
vikashgadia

excel expert with more than 13 years of experience

$60 USD in 1 day
(13 Reviews)
3.5
rohitpithisaria

Hi... I would like to apply for the job.

$100 USD in 5 days
(5 Reviews)
3.1
sumshank

Thanks for your time in reading my bid. Here is what I can offer: - Excellent MS Excel skills, having used it extensively during my work with the Information Technology industry. - My Excel work includes usage of More

$100 USD in 10 days
(3 Reviews)
2.4
muzammil21

we do not offer package we offer Only guaranteed results..100% quality work within time limit and ...Read more in PMB

$95 USD in 2 days
(2 Reviews)
2.1
fredysisenye

I have experience in excel and i have done a similar job on this site. Please check my PM.

$100 USD in 2 days
(1 Review)
1.4
chamiya1977

Please Check my PM

$50 USD in 1 day
(0 Reviews)
0.0
jonhaus

I am new to freelancer, but bring extensive experience. For the last three years I have worked developing and maintaining VBA macros for a large institutional bank. I have read and understand your technical requirement More

$40 USD in 1 day
(0 Reviews)
0.0
stephensalinas

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.

$150 USD in 1 day
(0 Reviews)
0.0
zkutch

Hello. More 15 years vba, vb , office programming experience. See pmb, please.

$100 USD in 0 days
(0 Reviews)
0.0
saurabhthakkar

Hi, This project won't require a lot of time and can be made in 1-2 days. Can deliver this project in a day if given an opportunity.

$100 USD in 1 day
(0 Reviews)
0.0
cjayasinghe

I understood the job well. I am sure I can do the project properly.

$75 USD in 3 days
(0 Reviews)
0.0
cmalburg1153

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.

$80 USD in 2 days
(0 Reviews)
0.0