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!
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,016
Messages
5,834,956
Members
430,330
Latest member
drAli77

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top