Using Excel to model feed inputs and growth cycles - how to stop/restart after goal?

Don't rewrite original posts cancelling your request.

I have spent the best part of the day looking at your problem, and as there were no replies showing when I checked the new posts, I carried on.

I had your thread book-marked so that I wouldn't miss anything.

If you cancel a thread (or solve it on your own.), say so in a new post.

And then mark it [Solved]

That way it will be flagged to anyone working on the problem.

My apologies, I don't know what I was thinking.

Here is the original post:

Hi everyone.

I have searched the internet and forums to no avail - I don't think I know the correct search terms to find what I'm looking for. I thank you in advance for any and all help.

I am putting together a spreadsheet in Excel 2007 to assist me with modeling some farming options we are investigating as a business model. We're looking to raise a certain number of fish, and we want to split that number up into smaller batches to make the work more manageable for our small staff. Fish are fed at different rates at different stages of growth, and there are feed conversion ratios that can change based on the species and even the variety within the species.

My goal is to create a spreadsheet which takes the information from the basic input variables and then displays a sheet which shows how much feed is being fed to each tank, what the total feed being used for all tanks is, how to stagger the batches, the estimated harvest dates, etc. This information is especially crucial during the initial startup phase, when some of the tanks will be empty.

I got the first tank all planned out, and I've copied and pasted it for the other potential tanks with an adjustment to the 'day' column which is derived from the initial input. I think I'm doing pretty well, given my total lack of Excel knowledge before starting on this project, but I am hitting a roadblock.

I want to find a way to have the feeding charts stop and start over from the beginning fingerling size when the fish get to harvest size (454 grams, or 1 pound).

In addition, since we are planning on using this spreadsheet in multiple locations, we want to be able to adjust for the number of tanks. In an ideal world I would also love to only display the feeding charts for those tanks that are being utilized (i.e., hide or somehow not show them in the first place) based on the initial number of tanks input variable.

I realize this is all about as clear as mud, but hopefully if you take a look at the spreadsheet it will hopefully explain what I'm talking about.

In the workbook I've uploaded I have three sheets. The first two are what I have actually come up with, and the third is what I really wish the second one looked like given the input variables.

I know this is not as simple of a question, and I'm open to partial solutions or any tips to get me going in the right direction. And if another program would be better to accomplish this goal, please feel free to let me know about it. I'm open to learning whatever I need to.

Marie

I do sincerely apologize, again, Marcol (and anyone else).

I became a bit discouraged after no replies, so I attempted to solve it with the help of a phone call to a friend.

If you would like to see what I have come up with, I have attached the revised version.

I am still having some problems, notably with:
Key Variables - E4 - Days to Harvest Size
I want this to only count the days up until the target weight is reached, and not after.

Sorting the data after calculating all of it. How can I have Excel calculate the total feed input by day for all tanks combined?

Most sincerely,
Marie

Here are the files for the post above.

Okay, apology accepted, but please understand that this is a volatary service, sometimes the requests are not instantly answerable, at least not on a free of charge basis, and this site is entirely free.

A few questions for you.

1/. You have a theoretical cut- off weight of 454g. Is there a tolerance on this?
What is the size limit that determines when a tank must be de-stocked?

2/. Are tanks re-stocked immediately they are harvested, is the original stock moved to larger tanks or are younger fish added to the original quota on a strictly defined time scale?

3/. If re-stocking isn't immediate is there a time allowed for cleaning/disinfecting/whatever?

4/. Purely on a file size basis, what is the maximum amount of tanks any one farm can reasonably be expected to have?
The number of farms the workbook is capable of handling is probably irrelevant.

I have a germ of an idea for you, most of which seems to work, but some more "inside" information would help to clarify your requirements.

Give me a day or so after you answer this.

Cheers
Alistair

1/. You have a theoretical cut- off weight of 454g. Is there a tolerance on this?

It is only a theoretical average cut-off weight, although we would not want to go much less than that as we want to be producing good size fillets. We actually intend to harvest on certain days of the week, so there is definitely a bit of a tolerance. The 454g mark is the minimum, with around 500g being the max before the fish start to get crowded.

2/. Are tanks re-stocked immediately they are harvested, is the original stock moved to larger tanks or are younger fish added to the original quota on a strictly defined time scale?
3/. If re-stocking isn't immediate is there a time allowed for cleaning/disinfecting/whatever?

We have a holding tank that at harvest time we will move the fish to a clearing tank (filled with a slightly salted water to improve the taste quality). The fish are not fed during this time period, so it isn't included on the spreadsheet.
This allows us to re-stock as soon as possible, preferably immediately, after harvest. Tanks are not emptied or cleaned (there is adequate sterilization measures in the filtration system). All tanks are the same size, the fish are not moved except for the final clearing tank.

4/. Purely on a file size basis, what is the maximum amount of tanks any one farm can reasonably be expected to have?
The number of farms the workbook is capable of handling is probably irrelevant.

My best guess at our maximum number of tanks would be the number required to harvest fish weekly. After that the tank size would likely be increased before any additional tanks would be added. That is a good deal off in the future, though. In the more immediate future we plan on having medium size systems with 4-12 tanks.

I have a germ of an idea for you, most of which seems to work, but some more "inside" information would help to clarify your requirements.

I am happy to provide you with more inside information, as needed, and I am looking forward to seeing what you have been working on.

Thanks again,
Marie

Okay we have a time zone difference of some 6 hours, might not get back to you until tomorrow.

It helps to know a bit of the background, I had visions of the fish being raised to a certain size, then split to severals tanks for further growth prior to releasing to an angling site, the keyword in your reply is "fillet" that says it all!

Hold in there back as soon as possible, we are not far away from a trial workbook.

Slainte
Alistair

Hi Alistair and Roger,

Although the feeding rates and amounts do appear counter-intuitive I am using information from several trusted sources to calculate them. An example article is here, where on page 2 the authors state

As the fish weight increases, the
percent body weight fed decreases

and then refer to Chart 2 where the stages of growth and feeding rates are detailed.

Of course I am not 100% on this since I am human and there is always the chance of error. I have been involved with the raising of fish before and have found these rules to hold true, generally speaking.

Alistair - this is a very big step in the right direction, thank you very much! I look forward to playing with your revision a bit more over the next couple of days.

To answer your questions:

will each Farm only stock one species at all times? Could an emptied tank be restocked with a different species/size, etc...?

It is unlikely that any one farm would stock more than one species, as the feed type and water temperature requirements usually differ significantly. On the other hand, it is possible (perhaps probable) that there will be some weight variation in the fingerlings that are restocked into the tanks.

...if I knew the rules/limits for calculating this a bit better it might be possible to use native formula for this, as it stands it's a bit of a cop-out!

I am really just trying to summarize the data from the growth and feeding rates to show how long each tank is being occupied per batch. I don't know if there is a native formula, just what I came up with (which is a simple COUNTIF up to the desired weight) which really doesn't work anyway. I'm open to any other solutions!

Thank you both for your input. I'm hoping to have some time this coming weekend to devote to this project and I look forward to checking in with an update soon!