Do Until Loop with Many If Statements

View attachment Production Exercise.xlsx

Hello,

I am very new to VBA and could use a little help with creating something to replace approximately 7 "if" statements that got out of control.

I have attached a spreadsheet that outlines the problem I am having.

Essentially, I am trying to forecast the production of widgets. I have only purchased 1,500 units of resources to make those widgets and once I run out I quit the business. The production inputs are the following:

• Peak year - I will be increasing my levels of production until then, this needs to be able to change.
• Growth rate - the rate each year I will be increasing the production until the peak year, this needs to be able to change.
• Decline rate - after the peak year, my production will be declining, this needs to be able to change.

In 2000, I produced 200 widgets and that's what I'm basing the future production on (i.e. in 2001, it grows to 200*(1+growth rate)).

The problem is, I have three plants, each of which cannot produce more than a certain amount each year. So when I make the simple production profile that goes "grow at x% until peak year, then decline at y% until my resources run out" is complicated by the fact that there is a ceiling to annual production. When the production hits that ceiling, it doesn't waste the resources and instead saves them for next year, thus the production profile is somewhat extended by those excess resources.

Basically, I need it to say "grow at x% until peak year as long as it's under annual capacity, then decline at y% until my resources run out and it's under annual capacity".

I would in utmost sincerity appreciate any help and leads on this.

Thank you a bunch!

Anastasia

Hi there,

Sorry, I was out sick yesterday. If someone else doesn't get to this first, I'll try to have a look at it tonight.

Hi there,

I've worked up two tables in here, based on what I think you're after. (No need for VBA.)

This assumes consistent growth of 5% per B4, and a linear decline of 10% per B5 once capacity is reached.

Based on my calculations the plants reach capacity in the following years:

• Plant 1: 2005
• Plant 2: 2004
• Plant 3: 2004

This means the overall capacity is reached in 2005. (I've added formula to calculate that automatically.

I then prepared a second table that shows the growth AND decline. Keep in mind that, because you're declining at 10% of the prior year annually, it will never reach 0.00 widgets. you may want to figure out a minimum production capacity so that it can be factored in as well.

Hope this is along the right lines of what you were looking for.

(Oh, and I hid some of your original work using outlining. Just click the + next to row 24 to show it again.)

Thank you so much!

I am glad to delay studying VBA :clap2:

Don't be too glad. It's SUPER useful... just not always the best way.