# Building a capital purchasing forecast based on production

#### IrishHawg09

##### New Member
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!

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Can you post some sample data and the formulas you are currently using?

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!

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.

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!

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)

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

Thanks so much for all your help!

You're very welcome.

Replies
2
Views
330
Replies
0
Views
197
Replies
4
Views
812
Replies
10
Views
2K
Replies
0
Views
661

1,207,439
Messages
6,078,569
Members
446,349
Latest member
Malroos7912

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back