limited recurring batch

cashmire

New Member
Joined
Mar 26, 2002
Messages
30
Hi,

I could use some help on this one. I'm working on the headcount forecast for the breeding operation. In short, they start with 300 animals which get impregnated. The offspring (50% male, 50% female) get returned into the breeding programme after 2yrs (female only). After 5yrs the original batch get send to the slaughter. I'm running into problems in year 5. How to run a formula that automates the elimination of the original batch while continue to add the new additions? Here is my preliminary layout...
Book1
BCDEFG
20Calving
21YearHeifersBullsPregnantHeifersBulls
22130025235112112
23229425230109109
24328824226107107
25439424309147147
26549623389185185
27659323465221221
287
298
309
3110
variables
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
the problem is that you have never stored the "original" herd. You have to add somewhere what is the number of animals of the original herd.
Also, what is in C15 and C13?
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

As I see you posted this before and had no luck, I'll make a guess:

Keep your layout but:

C26: =(C25+F23)-(C25*C$15)-C22

C27: =(C26+F24)-(C26*C$15)-F22 then dragged down with the rest of the formulas.
 

cashmire

New Member
Joined
Mar 26, 2002
Messages
30
Harvey,

in the cells are some constants which I was unable to post. Mainly % for mortality, etc.

-Peter
 

cashmire

New Member
Joined
Mar 26, 2002
Messages
30

ADVERTISEMENT

Fairwinds,

thanks for your feedback. I tried it and unfortunately it didn't bring the results I was looking for. Problem is that I have problems with the constants which I am unable to paste for some funny reason.
 

cashmire

New Member
Joined
Mar 26, 2002
Messages
30

ADVERTISEMENT

Fairwinds,

you were on the mark with your assumptions but the problem that I have is the following that I have to take into account a mortality rate of 2% each year, thus my initial batch of 300 reduces and after 5 years is not 300 anymore. I also have a mortality rate for the calfs. cell values:
C13 "calving ratio" 80%
C14 "Calf mortality" 5%
C15 "mortality" 2%

-Peter
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
make it:

=(C25+F23)-(C25*C$15)-(C22*(1-((1-C$15)^5)))

this subtracts the mortality of 2% for those 5 periods (or survival of 98%^5)
 

cashmire

New Member
Joined
Mar 26, 2002
Messages
30
Harvey,

thanks very much. I had no experience working with the "power to" formula but this seems to work fine. Appreciate your help!

-Peter
 

Forum statistics

Threads
1,148,274
Messages
5,745,797
Members
423,974
Latest member
highvoltageacdc

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