Building a capital purchasing forecast based on production

[IrishHawg09]

Hi,

I'm working on a financial model, and I need to be able to forecast the purchase of equipment, based on rising levels of sales. For instance, we will initially need to replace the molds after production reaches 200,000 units (and then replace the molds once each additional 200,000). This will go on until year 3, where we will instead replace the molds every 800,000 units produced, and replace at the next 800,000 threshold, and so on, until infinity. I've tried using various IF statements, but can't seem to crack it. I have my monthly production totaling in a row called "Lifetime Production", which just keeps a running total of all produced units.

In a nutshell, I need to replace in years 1 & 2 at 200,000 units, and in years 3 and after at 800,000 units.

Thanks!

 

[bschwartz]

Can you post some sample data and the formulas you are currently using?

 

[IrishHawg09]

Months and units produced

Dec-15	Jan-16	Feb-16	Mar-16	Apr-16	May-16
5000	6041.666667	7083.333333	8125	9166.666667	10208.33333

<colgroup><col span="6"></colgroup><tbody>
</tbody>

Current formula, based on month: =IF(OR(MONTH(D1)=10,MONTH(D1)=11),400000,0)

$400,000 here being the cost of the equipment. Ideally, I'd like to have purchases modeled after units produced exceed the aforementioned landmarks. If there is a way to submit a copy of the actual spreadsheet I'm using, I'd be happy to do that, as well.

Thanks!

 

[bschwartz]

Are these units produced in that month or the running total?

Also, you can upload the spreadsheet to a file sharing site, all sensitive data changed, such as dropbox, or box.com and share the link.

 

[IrishHawg09]

Sure. The data is saved here.

https://app.box.com/s/iuhhfvi0pbe856rbdi3z

 

[IrishHawg09]

The running total is based upon another sheet's data, but essentially it is just the sum of the current month production + the prior month's running total amount (ie; you can just take one month, subtract the prior month's, and arrive at the current month's production). Since the buying requirements are based upon the cumulative produced amount, however, the running total should be all that is needed.

Thanks again!

 

[bschwartz]

Try this formula starting in R4:

=IF((R1-$R$1)/365<3,((R2-MOD(R2,200000))-(Q2-MOD(Q2,200000)))*2,((R2-MOD(R2,800000))-(Q2-MOD(Q2,800000)))/2)

 

[IrishHawg09]

It appears to work, except when you get to DS4 (Sept 2024), when it populates a -12,400,000

 

[IrishHawg09]

Sorry, I just read your note about starting in R4. That solved the issue.

Thanks so much for all your help!

 

[bschwartz]

You're very welcome.