[VBA] Dynamic target stock

[maywood]

Hi,
for a case-study in my university I need to create an excel-file. But I have
some problems with a variable which should be dynamic.

Below you can find a link to an example with further descriptions. I need to
calculate the target stock in Sheet 2 via a VBA code because target stock is
dynamic and dependant on shortage, capacity and stocks.

Hope you can help me

http://www.2shared.com/file/9489655/610940d1/dynamic_target_stock.html

 

[Barb Reinhardt]

What specifically are you having issues with. I typically don't download
files from this site, but if there are questions you have, we may be able to
help.

 

[maywood]

Hi Barb!
Maybe this website is better for a download:
http://www.file-upload.net/download-2040953/dynamic-target-stock.xls.html

Well, here is the problem description:
Initial Stock (B5) and Demand (C9:F9) for 4 months are given. The Demand
can be satisfied by the use of stocks above the target and by production

Calculations:
Usable Stocks:
There exists an initial Stock (B5) (->last month) and a preassigned
target-stock by hand (Sheet 2, C4:F4)
If the final stock of the previous month is greater than the target-stock,
the difference (C10:F10) can be used to satisfy a part of the demand
Demand minus Usable stock is the required production (C11:F11).

Production:
IF the required production is less than the production-capacity (C12:F12),
the required production is fully produced in Prod. Iteration (J12:M12)
If the initial stock or the final stock of the previous month is below its
target-stock, the difference is shown as a delta stock (J13:M13). If the
capacity is still sufficient, this delta additionally should be produced to
refill stocks
The required production plus an eventual delta stock is actual production
(C14:F14), which can't exceed capacity

Shortage:
If the capacity is not sufficient to produce the required production (here:
January) then there is a shortage (C17:F17)

Stock:
Actual Stock of next month = Stock of the previous month - demand +
production + shortage

Problem:
Stocks above the target-stock are used to satisfy demand, stocks below the
target should be refilled by additional production
You can see that in January there exists a shortage because there is no free
production-capacity. You can also see that there is free capacity in the
previous months (C15:F15).
The shortage appears because I can't produce in January and I also cannot
use stocks to satisfy demand

Solution:
At the moment I would higher the target-stock in December or November by
hand. This ensures higher production to refill stocks which I can use in
January
But I am searching for a macro which calcualtes the target stocks
automatically
How to do that with VBA?

Restrictions:
The target Stock has a minimum -> the min-stock (Sheet2, B5:F5)
If there is a shortage in January, the production in december should produce
as much of this shortage as it can
If the production in december cannot produce that much because of the
capacity the production in november should produce the rest of the shortage
in january...and so on

Idea:
Target Stock of previous month = Min Stock + sum of alle shortages in future

Long description. Hope you can understand it.

 

[Herbert Seidenberg]

Excel 2007
With Solver
http://c0444202.cdn.cloudfiles.rackspacecloud.com/11_27_09.xlsx

 

[maywood]

Hi Herbert,

the Solver is a possibility to solve my problem if the shortage is static or
in one month like in my example.

But in my more detailed Excel Sheet here at home, I am importing new data
each month. And then I do a little Scenario where I vary the demand. From
this it follows that the shortage can exist in no month, in one month or in
up to 12 months.
Can I use the solver to optmize 12 cells at a time?

 

[Herbert Seidenberg]

Increased range to 12 months.
Without further restrictions,
the number of solutions is unmanageably large.
Mark up my work, not yours, for further help.
Download same link.

 

[maywood]

What does "R11C2" or "R11C4" mean in the Solver?
I only have cells beginning with a Z in my Excel-Sheet

 

[Dana DeLouis]

Hi. I don't quite follow the worksheet, but in general...
My guess is that your Solver setup will not work because your model is
using IF() and MAX() statements. These are discontinuous functions, and
Solver does not have enough logic to work around these.
Solver will usually give up without warning if it gets confused with
these functions.

What does "R11C2" or "R11C4" mean...

You are using R1C1 reference style. (Columns numbered 1,2,3... instead
of A,B,C...etc.) Starting with Excel 97, Solver requires A1 reference
style. You can change this back in Excel options. (uncheck "R1C1
reference style.")
My opinion is that your model will have to be reworked.
= = = = = = =
Dana DeLouis