1 Reply Latest reply: Nov 24, 2011 4:48 PM by Jim Thomlinson RSS

    Balance Sheet Aggregation

    Jim Thomlinson

      I am attempting to create a financial balance sheet model and I am having difficulty in calcualting the aggregate balances. Here is what I have so far...

       

      My Source data looks something like this... Note that there are more periods and lots more transactions from many different locations.

       

      DatePeriodYearJE DescriptionTrans Amount
      2/1/1122011Beg Bal10,000
      3/1/1132011Beg Bal11,000
      4/1/1142011Beg Bal9,500
      2/10/1122011Sold-1,000
      2/20/1122011Purchased2,000
      3/10/1132011Sold-3,000
      3/20/1132011Purchased1,500
      4/10/1142011Sold-3,500
      4/20/1142011Purchased2,000


      Here is what I want to get when I select periods 2, 3 and 4 (Feb - Apr)

       

      1234
      BalanceChangeHistoryDetailed Balance
      Inventory8,000-2,0008,0000
      Feb11,0001,00011,00011,000
      Mar9,500-1,500-1,5009,500
      Apr8,000-1,500-1,5008,000

       

      I can calculate 2 the change as follows

      num(sum( {$<Year = {$(=Max(Year))},[JE Description] -= {'Beg Bal'} >} [Transaction Amount]), '#,##0')

       

      I can calculate 3 the history as follows

       

      num(sum( {$<Year = {$(=Max(Year))}, Period = {$(=Min(Period))}, [JE Description]={'Beg Bal'} >} [Transaction Amount]) +
      sum( {$<Year = {$(=Max(Year))}, Period = {">=$(=Min(Period))<=$(=Max(Period))"}, [JE Description] -={'Beg Bal'} >} [Transaction Amount]), '#,##0')

       

      I can calculate 4 the detailed balances as follows (4 is not neccessary if I can get 1)

      num

      (
      RangeSum(Above(
      sum( {$<Year = {$(=Max(Year))}, Period = {$(=Min(Period))}, [JE Description]={'Beg Bal'} >} [Transaction Amount]) +
      sum( {$<Year = {$(=Max(Year))}, Period = {">=$(=Min(Period))<=$(=Max(Period))"}, [JE Description] -={'Beg Bal'} >} [Transaction Amount])
      , 0, RowNo()))
      , '#,##0')

       

       

       

       

       

       

       

       

       

       

       

       

      I can not calculate 1. My issue arises trying to calculate the aggregate. The individual periods are correct but the aggregate does not work out. Instead of 8,000 which is my closing balance for April all I can get is 28,500 which includes all beginning balances.

       

       

        • Balance Sheet Aggregation
          Jim Thomlinson

          This seems to do it...

          =if(count(DISTINCT(Period))=1, sum( {$<Year = {$(=Max(Year))} >} [Transaction Amount]), sum( {$<Year = {$(=Max(Year))}, Period = {$(=Max(Period))} >} [Transaction Amount]))

           

          Not sure if there is a better way but I will stick with this for now.