# Windows

## Question

Locked

By ·
Hi

This is re. Stock control with spreadsheet. I have been trying to create an excel formula that will be applicable for all the scenarios given below:

Scenario 1
Opening Stock.............................10
Receipt to Store 1.....................200
Orders from members............500
Dispatch to members............210
Closing Stock at Store 1

Scenario 2
Opening Stock..............................0
Receipt to Store 1.........................100
Orders from members.................50
Dispatch to members.................50
Closing Stock at Store 1

Scenario 3
Opening Stock.............................20
Receipt to Store 1........................50
Orders from members...............100
Dispatch to members..................70
Closing Stock at Store 1

Scenario 4
Opening Stock.........................50
Receipt to Store 1....................100
Orders from members............50
Despatch to members...........50
Closing Stock at Store 1

Scenario 5
Opening Stock.........................-150
Receipt to Store 1.....................100
Orders from members.............50
Despatch to members...........100
Closing Stock at Store 1

Trust I am clear. I am looking for help in arriving at ONE formula for all the above to arrive at the Closing Stock at Store 1

cheers

Kris

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

Collapse -

### looks easy

This looks too easy to be a real problem. You seem to have 4 input values and 1 output value (Closing stock). Put the input values in, say, a2, b2, c2 and d2 (with appropriate text in column 1). In f2 write "=" and whatever exact combination you want (you are not clear about how the amounts are inter-related: the obvious relationship, a2 + b2 - d2, would not give your "correct answer" for Scenario 5.). Perhaps you should be a little more precise as to the relationship, and perhaps also check your answers.

Collapse -

### it looks simple...

by In reply to looks easy

Hi John

thanks for your input. It is not that simple. Here the closing stock or the opening stock could be negative, which means back orders (pending). And the receipt in store could be more than required and that is an easy scenario. However, if the receipt is less than the required qty to supply, a simple formula cannot work. And lastly, it takes a **** a lot of time to check 5000 lines.

thanks again

cheers

Kris

Collapse -

### Clarification

What exactly do you mean by "receipt"? Amount received? And "despatch"? With the usual meanings, scenario 5 is impossible.

Collapse -

### Clarification

Hi John
It is a stock and sales management spread sheet which also shows back orders (that could not be supplied for want of stock) as negative. Does it make sense now

Collapse -

### Clarification

Senario 3 looks like a type should it not be negative 30.
I would suggest you need to a one more figure to the mix. Break apart outstanding and new orders

• 48

• 25

• 18

• 4