Formula Forensics-No. 036: Calculating Costs that Vary by Year and Age

Posted on November 26th, 2013 in Excel Howtos , Formula Forensics , Posts by Sajan - 7 comments

Van Gysel asked in a recent post at Chandoo.org for a way to calculate the costs of running a plantation.  The twist is that the costs vary by year, and based on the age of the trees.

The following is a slightly simplified version of the solution I offered:

=SUM(IFERROR(LOOKUP(“Year”&MMULT(N($B$3:B$7>0),TRANSPOSE(COLUMN($B$3:B$7)^0)), $B$11:$I$11, $B12:$I12),0)*B$3:B$7)      Ctrl+Shift+Enter

Today I am going to try and explain how the formula works.

As always at Formula Forensics, you can follow along with a sample file: Download Here

 

The Problem

In a plantation, the costs for planting and maintaining trees vary based on the age of the trees and by year.  The table below shows the acres of trees planted per year and the yield and costs per year that vary based on the age of the trees.

FF36-02

Let us look at the calculations needed for each year.

Year 2013

  • 300 acres of trees were planted in 2013.  Calculations for 2013 are as follows.  (Only Yield calculation is shown, but the process is similar for Nursery costs, Fertilizers, etc.)
  • The trees do not yield any fruits in the first year.  As such, Yield for year1=300*0=0

That was easy!

 Year 2014

  • 700 additional acres of trees will be planted in 2014.  Calculations for 2014 are as follows.  (Again, only Yield calculation is shown, but others are calculated similarly.)

FF36-Year2Calc

  • 300 acres of trees are 2 years old.  700 acres are 1 year old.
  • The 300 acres from 2013 now yield fruit since it is year2.  However, the new trees (700 acres) do not yield any fruits yet.  So total yield for 2014=300*Year2Yield+700*Year1Yield=300*5+700*0

Year 2015

  • 1000 additional acres are to be planted in 2015.  Calculations for 2015 are as follows:

FF36-Year3Calc

  • 300 acres are from 2013 (3 years old); 700 acres are from 2014 (2 years old); 1000 acres are from 2015 (1 year old).
  • Yield for 2015=300*Year3Yield + 700*Year2Yield + 1000*Year1Yield = 300*10 + 700*5 + 1000*0

 Year 2016

  • 1000 additional acres are to be planted in 2016.  Calculations for 2016 are as follows:

FF36-Year4Calc

  • Yield for 2016=300*15+700*10+1000*5+1000*0

How do we simulate the above calculation in an Excel formula?

A Solution

Let us first look at how we performed the calculations above manually, using the 2016 Yield as an example.

  1. We took each acreage value in 2016, and determined its age by counting how many years it has been since that acreage was planted.  You might have observed that the age can be counted by the number of times a value has been repeated up to that point.  (In other words, if I planted 300 acres in 2013, I should see that same amount in 2014, 2015 and 2016.) As such, 300 acres is repeated 4 times.  700 acres is repeated 3 times.  1000 acres is repeated 2 times. And the latest planting of 1000 acres exists only once.
  2. Once we determine the age for a given acreage, we looked up the yield for that age in the second table
  3. We then multiplied the acreage with the corresponding yield value.

Calculation #1 can be expressed as follows:

  • Age for acreage 1 (first planted in 2013)=count of B3:E3 where value is greater than zero.  i.e. COUNTIF(B3:E3,”>0”)
  • Age for acreage 2 (planted in 2014)=count of B4:E4 where value is greater than zero.  i.e. COUNTIF(B4:E4,”>0”)
  • Age for acreage 3 (planted in 2015)=count of B5:E5 where value is greater than zero.  i.e. COUNTIF(B5:E5,”>0”)
  • Age for acreage 4 (planted in 2016)=count of B6:E6 where value is greater than zero.  i.e. COUNTIF(B6:E6,”>0”)
  • Age for acreage 5 is zero since nothing has been planted for 2017 yet in 2016

The above approach would work if we were calculating the age one row at a time.  However, that can become tedious really fast.  We need to perform the calculation for the full range (B3:E7) together, but return the counts for each row individually.

Excel’s MMULT function comes to the rescue!

MMULT (which stands for Matrix Multiply) multiplies two matrices and returns a third matrix based on rules for matrix multiplication.  I am planning to devote a whole article to explain the MMULT function.  As such, for this article, we will summarize the utility of the function as “take a 2-dimensional array, add each column’s value for each row, and return a 1-column array”.

MMULT requires that its arguments be numeric.

So to obtain the counts for the year 2016, we can use the following:

MMULT(N($B$3:E$7>0),TRANSPOSE(COLUMN($B$3:E$7)^0))

As you can see from the picture below, MMULT’s results are the addition of each column for each row.

FF36-TRUE FALSE to1s 0s

In the above formula, you may have noticed that the range uses absolute and relative referencing (signified by the $ sign or lack thereof).  This is to ensure that the range grows or shrinks as needed.  The upper left address is held constant ($B$3).  However, the lower right address for the range has columns that vary but row that is fixed on row #7.  This ensures that the formula would work if we copy to the left, right, etc. in the final results.

Now that we have the age for each acreage value, we can look up the corresponding yield value using (what else?) LOOKUP function.

But before we can use LOOKUP, we will need to convert the numeric values returned from MMULT into the strings Year1, Year2, etc. found in the Costs table.  Of course, you know how to do that… concatenate the string “Year” to the result from MMULT

“Year”&MMULT(N($B$3:E$7>0),TRANSPOSE(COLUMN($B$3:E$7)^0))

For the 2016 example, we get {“Year4″;”Year3″;”Year2″;”Year1″;”Year0″}

We can now use LOOKUP as follows:

LOOKUP(“Year”&MMULT(N($B$3:E$7>0),TRANSPOSE(COLUMN($B$3:E$7)^0)), $B$11:$I$11, $B12:$I12)

FF36-2016 calc

You may recall that LOOKUP looks up a value in the array indicated by the second argument, and returns the corresponding value from the third array argument.  In this case, instead of looking up a single value, we look up an array of values (supplied in the first argument) to the function.

The above formula translates to the following:

LOOKUP({“Year4″;”Year3″;”Year2″;”Year1″;”Year0″}, {“Year1″,”Year2″,”Year3″,”Year4″,”Year5″,”Year6″,”Year7″,”Year8″}, {0,5,10,15,20,25,30,35})

The result from LOOKUP is {15;10;5;0;#N/A}

(The last value is #N/A because there is no acreage value for 2017 yet (as of 2016 column).  The concatenation resulted in Year0 which does not exist in the “Age of The Trees” range (B11:I11) above.)

By using IFERROR(LOOKUP(…),0) we get {15;10;5;0;0}

We can now multiply the above result with the acreage values for 2016 to get {4500;7000;5000;0;0}

Finally, we SUM the values to get 16500

FF36-Final Calc

Putting it all together, we get the following formula (shown for Production for year 2016)

=SUM(IFERROR(LOOKUP(“Year”&MMULT(N($B$3:E$7>0),TRANSPOSE(COLUMN($B$3:E$7)^0)), $B$11:$I$11, $B12:$I12),0)*E$3:E$7)

One of the benefits of the above formula is that you can copy the same formula to calculate values for additional years, as well as other plantation costs.

Download

You can download a copy of the above file and follow along: Download sample file.

 

Let me know (using the comments below) what you think of the above approach and solution, as well as any other approaches you have utilized to solve a similar problem.  In the meantime, I wish you continued Excellence!

-Sajan.

 

 Other Posts in this Series

The Formula Forensics Series contains a wealth of useful solutions and information.

Visit the Formula Forensics Home Page to read other articles in this series.

Your email address is safe with us. Our policies

Written by Sajan Thomas
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

7 Responses to “Formula Forensics-No. 036: Calculating Costs that Vary by Year and Age”

  1. K. NARAYAN says:

    Hi Sajan ,

    Congratulations for a wonderfully done explanation ; and thanks for taking the time and effort.

  2. WholesomeGoodness says:

    Great article. What an inspired approach to a problem. I’ll be looking forward to that article on the MMULT function!

  3. Tim Emonson says:

    We use an approach called the “Reverse Ticker” that allows you to come up with a profile for an item and then allow you to apply that profile to different quantities over different periods. One of the issues with the approach above, is that you need each year to be on a separate row. The approach we use has the planted area as a single row.

    The basic steps are:
    • Insert a counter row (i.e. 1, 2, 3 etc across the row). Say in cells B1:I1
    • Insert the Yield row (row 12 in the example above). Put these in B2:I2
    • In the next row, reverse the Yield profile (the ‘reverse ticker’). That is, the value that was in the first column should now be in the last column, the second value in the second last column etc. This is done with an OFFSET, and the formula would be =OFFSET($B2,0,MAX($B$1:$I$1)-B$1). Put this into cell B3 and copy across to B3:I3
    • In the next row, have the quantities of the planted area by year in cells B4:I4. The planted area is the ‘Sum of the Planted Area’ row in the example above.
    • In the next row, combine the reverse ticker with the planted area to determine the ‘Production’. This is done with a SUMPRODUCT and an OFFSET. So the formula would be =SUMPRODUCT($B4:I4,OFFSET($B3,0,MAX(B$1:$I$1)-1,1,-B$1)). Put this into cell B5 and copy across to B5:I5.

    The beauty of this approach is that it can be used on anything that has a life cycle to it. For example, the maintenance profile on a vehicle, life cycle costs per km of pipeline and where we use it all the time, depreciation calculations (no more “waterfall” calcs). Drawbacks – periods must be consistent across a sheet (you cannot go monthly, then quarterly), for things such as depreciation it works on average days in the period rather than actual, existing assets need to be modelled separately and some people struggle to understand the method. The approach used in the above post would also have the same drawbacks.

    So rather than having multiple lines (i.e. at least one for each year) you can condense it into 5 lines. And no arrays. :-)

    Cheers
    Tim Emonson
    http://www.bestpracticemodelling.com

    • K. NARAYAN says:

      Hi Tim ,

      Thanks for the idea.

      I have two comments , the second of which may or may not be right.

      1. Your formula should be : =SUMPRODUCT(($B4:B4)*(OFFSET($B3,0,MAX(B$1:$I$1)-1,1,-B$1)))

      The $B4:I4 must be a typographical error.

      2. Your concept returns the same results as Sajan’s method ( of course your concept returns the cumulative yield rather than the per-year yield ) only if the acreage planted each year is either the same as or more than the previous year ; if the planted acreage varies randomly , the results from your concept , and the results from Sajan’s method do not tally ; I have not checked which is correct ; can you confirm ?

  4. Tim Emonson says:

    Hi K. Narayan

    Good pickup on the reference! Yes, $B4:B4 is the correct reference in the first part of the SUMPRODUCT.

    For the numbers, I have tested it and come up with the same answers for the data set that Sajan has included (noting that his Total costs line doesn’t add though!). I also randomly varied the acres planted each year between the two methods and they came up with the same answers. So the method I used works if the acreage planted is less than the prior year.

    When you say you varied the acreage randomly, can you explain what you did as I am not understanding where you are getting a variance? Particularly given I varied it and came up with the same answers as Sajan when I put the same assumptions into that analysis.

    Regards
    Tim

    • NARAYAN RAO says:

      Hi Tim ,
      I have uploaded the acreage planted data that I tried out. Can you use this data and confirm ?
      Regards ,
      Narayan

  5. duncanwil says:

    I hope this comment gets through this time.

    I enjoyed working through this page and have two comments.

    1 the totals in the total costs table are wrong. It seems you hard coded the total at some stage and forgot to change them to formulas

    2 I reworked this model using formulas and not only does it work but it didn’t take too long and anyone with medium level Excel skills would be able to follow it. I say that because Sajan and the questioner both appreciate their methods can be tough to follow. I will happily share my efforts but my connection is bad at the moment.

    Duncan

Leave a Reply