Can Excel functions solve iterative problems?

Question

I want to use Microsoft Excel to project the growth of a pig farming project into which I will inject a fixed amount of cash per month and then use the cash to raise the number of full pigs that the working cash can raise, with any excess cash that isn't enough for a full pig being carried over into the next cycle.

At the end of each cycle (four months) when I sell the current batch of pigs, I will reinvest all the cash raised into the farm, and if the existing capacity is not enough for the number of pigs that my new cash can produce, then I will increase the capacity by building new shed(s) that each hold a fixed number of pigs. Obviously, building a new shed or new sheds reduces the working capital, which in turn reduces the number of pigs that I can raise, which in turn reduces the number of pig sheds required.

I want to balance the number of pigs raised in each cycle and the number of pig sheds built each cycle to exhaust my working capital without running into debt, and I want to use Excel to calculate the number of pigs that will be raised each cycle and the number of new sheds, if any, that will be built each cycle.

Practical Example:

Monthly cash injection: KES 50,000 (the currency is besides the point). Cash injected per four-month cycle: KES 200,000 Cost of building one pig shed: KES 75,000 Capacity of one pig shed: 15 pigs. Cost of raising one pig: KES 19,000. Selling price per pig: KES 27,000

In the first cycle, working capital will be KES 200,000. If I build one shed, I'll be left with KES 125,000, which is enough for six pigs at KES 114000, with KES 11,000 being carried over into the next cycle. When I sell this batch, it will raise KES 162,000.

In the second cycle, I will now have working capital of KES 200,000 from regular monthly injection, KES 162,000 from sale of previous batch, and KES 11,000 left over from previous cycle. Total KES 373,000. This is enough for 19 pigs with KES 12,000 left over. However, since I only have capacity for 15 pigs, I will need to build another shed at KES 75,000, and so my new working capital will be KES 298,000, which is only enough for 15 pigs at KES 285,000 with KES 13,000 left over. This means that for the second cycle, I don't need to build a new shed, since if I do so I won't have enough working capital to need the second shed, meaning I will raise the 15 pigs with KES 88,000 left over. Either that, or I build a new shed that will be idle during the second cycle. When I sell this batch, it will raise KES 405,000.

In the third cycle, I will now have working capital of kES 693,000 (405,000 from selling previous batch, 200,000 from regular injection, and 88,000 left over assuming I don't build a new shed that won't be used in the second cycle). This 693,000 is enough for 36 pigs with 9,000 left over, meaning I need two new sheds. However, the two new sheds will cost 150,000 and leave me with 543,000, which is enough for 29 pigs, which in turn means I won't need two extra sheds since I won't have enough cash to utilize them, which means that I can instead build one extra shed at 75,000, be left with 618,000 which is enough for 32 pigs. This means that practically, I will either build two new sheds and raise 29 pigs with one shed being idle during that cycle, or I will build one new shed and raise 30 pigs with some 48,000 being left over. This second option is the better use of money, so that's what I will do.

And so on for every cycle.

I want to calculate this with Excel and I have no idea how to go about it. I don't know any VBA. I'm suspecting it would take some VBA code to accomplish.

I have done the projection for a scenario where the pig sheds already exist (if I was leasing an existing pig farm, for example), which is straight forward. I now want to be able to project for a situation where I'm building the pig sheds myself as I go along.

Answer 1

The key decision to take is the number of sheds to build in each cycle. This number should be maximized with the constraint that you should have enough money left to fill all sheds, where only one is possibly not filled to capacity, but should not be empty.

This is a mathematical relation that can be solved.

The mathematics derivation

Let's call:

• 𝑚: available 𝑚oney (capital) after having sold all pigs that were raised during a previous cycle (if any) and after having received the cash injections during the previous cycle
• 𝑠: number of owned 𝑠heds
• 𝑏: price for 𝑏uilding a shed
• 𝑐: 𝑐apacity of a shed
• 𝑟: price for 𝑟aising a pig
• 𝑎: number of 𝑎dditional sheds to build
• 𝑝: number of 𝑝igs to buy

If 𝑎 would be given, we could use the remaining money for buying pigs, and so then:

      𝑝 = ⌊(𝑚 − 𝑎𝑏) / 𝑟⌋

From that we can derive the number of sheds needed to place those pigs, and thus how many we would need to build more:

      𝑎 = ⌈𝑝 / 𝑐⌉ − 𝑠

We want none of the sheds to be empty, so the 𝑎 we started with, should be the 𝑎 we end up with. We must satisfy this equation (substituting 𝑝):

      𝑎 = ⌈(⌊(𝑚 − 𝑎𝑏) / 𝑟⌋ / 𝑐)⌉ - 𝑠

      𝑠 + 𝑎 = ⌈(⌊(𝑚 − 𝑎𝑏) / 𝑟⌋ / 𝑐)⌉

      𝑠 + 𝑎 = ⌊(⌊(𝑚 − 𝑎𝑏) / 𝑟⌋ + 𝑐 − 1) / 𝑐⌋

      𝑠 + 𝑎 ≤ (⌊(𝑚 − 𝑎𝑏) / 𝑟⌋ + 𝑐 − 1) / 𝑐

      𝑠𝑐 + 𝑎𝑐 ≤ ⌊(𝑚 − 𝑎𝑏) / 𝑟⌋ + 𝑐 − 1

      𝑠𝑐 + 𝑎𝑐 − 𝑐 + 1 ≤ ⌊(𝑚 − 𝑎𝑏) / 𝑟⌋

      𝑠𝑐 + 𝑎𝑐 − 𝑐 + 1 ≤ (𝑚 − 𝑎𝑏) / 𝑟

      𝑠𝑐𝑟 + 𝑎𝑐𝑟 − 𝑐𝑟 + 𝑟 ≤ 𝑚 − 𝑎𝑏

      𝑎𝑐𝑟 + 𝑎𝑏 ≤ 𝑚 + 𝑐𝑟 − 𝑟 − 𝑠𝑐𝑟

      𝑎(𝑐𝑟 + 𝑏) ≤ 𝑚 + (𝑐 − 1 − 𝑠𝑐)𝑟

      𝑎 ≤ (𝑚 + ((1 − 𝑠)𝑐 − 1)𝑟) / (𝑐𝑟 + 𝑏)

      𝑎 ≤ (𝑚 − ((𝑠 − 1)𝑐 + 1)𝑟) / (𝑐𝑟 + 𝑏)

So, maximizing 𝑎, the number of sheds to construct in each cycle is equal to ⌊(𝑚 − ((𝑠 − 1)𝑐 + 1)𝑟) / (𝑐𝑟 + 𝑏)⌋.

Implementing this in Excel

We could use the range A1:B5 to define the parameters:

	A	B
1	Shed building cost (b):	75000
2	Shed capacity (c):	15
3	Pig raising cost (r):	19000
4	Pig selling price:	27000
5	Cycle injection:	200000

The table would start with a heading at row 6, and some hardcoded initial values (all zero) in the row below it:

	A	B	C	D	E	F	G
6	Cycle	Capital (m)	Sheds to buy	Sheds (s)	Money left for pigs	Pigs	Money left
7	0			0		0	0

The derived formula will end up in column C from the 8th row onwards.

Here are the formulas for the 8th row:

• A8: =A7+1
• B8: =G7+$B$5+F7*$B$4
• C8: =FLOOR.MATH((B8-((D7-1)*$B$2+1)*$B$3)/($B$2*$B$3 + $B$1))
• D8: =D7+C8
• E8: =B8-C8*$B$1
• F8: =MIN(FLOOR.MATH(E8/$B$3),D8*$B$2)
• G8: =E8-F8*$B$3

Copy (or drag) all these formulas down to the subsequent rows for as long as you need them.

Here is a screenshot of what I got as result: