Building a capital purchasing forecast based on production

IrishHawg09

New Member
Joined
Sep 9, 2014
Messages
16
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!
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

bschwartz

Well-known Member
Joined
Jan 5, 2013
Messages
1,367
Can you post some sample data and the formulas you are currently using?
 

IrishHawg09

New Member
Joined
Sep 9, 2014
Messages
16
Months and units produced
Dec-15Jan-16Feb-16Mar-16
Apr-16
May-16
50006041.6666677083.33333381259166.66666710208.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

Well-known Member
Joined
Jan 5, 2013
Messages
1,367
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

New Member
Joined
Sep 9, 2014
Messages
16
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

Well-known Member
Joined
Jan 5, 2013
Messages
1,367

ADVERTISEMENT

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

New Member
Joined
Sep 9, 2014
Messages
16
It appears to work, except when you get to DS4 (Sept 2024), when it populates a -12,400,000
 

IrishHawg09

New Member
Joined
Sep 9, 2014
Messages
16
Sorry, I just read your note about starting in R4. That solved the issue.

Thanks so much for all your help!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,001
Messages
5,526,199
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top