Forecast Monthly Rounded Growth

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Ans = List.Accumulate(
    {"Aug", "Sep", "Oct", "Nov", "Dec"}, 
    Table.AddColumn(
      Source, 
      "Jul", 
      each Number.Round(List.Average(List.Skip(Record.ToList(_))), - 1)
    ), 
    (s, m) =>
      Table.AddColumn(
        s, 
        m, 
        each Number.Round(Record.Field(_, List.Last(Table.ColumnNames(s))) * 2.1, - 1) / 2
      )
  )
in
  Ans

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  MRound = (n, m) => Number.Round(n / m) * m, 
  S = Table.FromRows(
    List.Transform(
      Table.ToRows(Source), 
      each _
        & List.Accumulate(
          {1 .. 5}, 
          {MRound(List.Average(List.Skip(_)), 10)}, 
          (s, c) => s & {MRound(List.Last(s) * 1.05, 5)}
        )
    ), 
    Table.ColumnNames(Source) & {"Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}
  )
in
  S

let
  A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  B = List.Accumulate(
    {"Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}, 
    A, 
    (x, y) =>
      Table.AddColumn(
        x, 
        y, 
        each 
          let
            a = Record.ToList(_)
          in
            if y = "Jul" then
              Number.Round(List.Average(List.Skip(a)), - 1)
            else
              Number.Round(List.Last(a) * 1.05 / 5, 0) * 5
      )
  )
in
  B

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Dates = Table.AddColumn(Source, "Month", each 
 let 
 a = List.Count(List.Skip(Table.ColumnNames(Source))),
 b = {"Jul", "Ago", "Sep", "Oct", "Nov", "Dic"},
 c = Number.Round(List.Average(List.Skip(Record.ToList(_)))/10)*10,
 d = List.Transform({1..5}, each Number.Round(c*Number.Power(1.05,_)/5)*5)
 in Table.FromRows({{c}&d}, b)),
 Sol = Table.ExpandTableColumn(Dates, "Month", Table.ColumnNames(Dates[Month]{0}))
in
 Sol

Por lo que veo, el único que no se conocía esta técnica era yo 😅😅😅😅 , con esto me ahorré 3 pasos.


                    
                  
          

let
  Source = Excel.CurrentWorkbook(){0}[Content], 
  Dates = Table.AddColumn(
    Source, 
    "Month", 
    each 
      let
        a = List.Count(List.Skip(Table.ColumnNames(Source))), 
        b = {"Jul", "Ago", "Sep", "Oct", "Nov", "Dic"}, 
        c = Number.Round(List.Average(List.Skip(Record.ToList(_))), - 1), 
        d = List.Transform(
          {1 .. 12 - a - 1}, 
          each 
            let
              e = c * Number.Power(1.05, _), 
              f = Number.RoundUp(e) - Number.RoundDown(e, - 1), 
              g = if f < 3 then 0 else if f > 2 and f < 7 then 5 else 10, 
              h = Number.RoundDown(e, - 1) + g
            in
              h
        )
      in
        Table.FromRows({{c} & d}, b)
  ), 
  Sol = Table.ExpandTableColumn(Dates, "Month", Table.ColumnNames(Dates[Month]{0}))
in
  Sol

let
  Source = Excel.CurrentWorkbook(){[Name = "tData116"]}[Content], 
  Transform = List.Transform(
    Table.ToRows(Source), 
    each 
      let
        _PM = List.Skip(_), 
        _NM = List.Count(_PM), 
        _F1 = Number.Round(List.Sum(_PM) / (10 * _NM)) * 10, 
        _FM = List.Generate(
          () => [i = 0, f = _F1], 
          each [i] < 12 - _NM, 
          each [i = [i] + 1, f = Number.Round([f] * 1.05 / 5) * 5], 
          each [f]
        )
      in
        _ & _FM
  ), 
  ToTable = Table.FromRows(
    Transform, 
    Table.ColumnNames(Source) & {"Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}
  )
in
  ToTable

let
 Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
//Custom rounding function
 RoundTo5 = (nr) => let 
rd = Number.RoundDown( nr,-1),diff = nr - rd
in 
if diff < 3 then rd else
if diff > 2 and diff < 8 then rd + 5
else Number.RoundUp(nr,-1),
//get list of all month numbers in the record, extract the last month number, calculate result based on month name
 MonthCalc = (month, record) => [ AllMonths = List.Skip( Record.FieldValues(record) ), LastMonth = List.Last(AllMonths), Calc = if month = "Jul" then Number.Round(List.Average(AllMonths),-1) else RoundTo5(LastMonth * 1.05) ]
[Calc],
//List of Months to be added
 Month_List = List.Transform( 
{7..12},
each Date.ToText( hashtag#date(2023,_,1), [Format = "MMM", Culture = "en-GB"] )
),
//calculate forcasted amount for each month
 Forcast = List.Accumulate( 
Month_List,
Source,
(Table,Month) => Table.AddColumn(Table, Month, each MonthCalc(Month,_) )
)
in
 Forcast
                    
                  
          

=VSTACK(HSTACK(A1:G1,TEXT(SEQUENCE(,6,7)&-20,"mmm")),
REDUCE(A2:G6,SEQUENCE(6),LAMBDA(a,n,HSTACK(a,IF(n=1,ROUND(MMULT(N(+a),SEQUENCE(7)^0)/6,-1),MROUND(TAKE(a,,-1)*1.05,5))))))

=HSTACK(
A1:G6,VSTACK(TEXT(SEQUENCE(,6,7)*29,"mmm"),
REDUCE(MROUND(MMULT(B2:G6,SEQUENCE(6)^0)/6,10),
SEQUENCE(5),
LAMBDA(a,d,HSTACK(a,MROUND(TAKE(a,,-1)*1.05,5))))))

=HSTACK(A1:G6,
VSTACK({"Jul","Aug","Sep","Oct","Nov","Dec"},
REDUCE(MROUND(MMULT(B2:G6,SEQUENCE(6)^0)/6,10),
SEQUENCE(5),LAMBDA(a,d,
HSTACK(a,MROUND(TAKE(a,,-1)*1.05,5))))))

=REDUCE(MROUND(MMULT(B9:G13,SEQUENCE(6)^0)/6,10),SEQUENCE(5),LAMBDA(a,d,HSTACK(a,MROUND(TAKE(a,,-1)*1.05,5))))

=LET(a,A1:A6,b,B2:G6,c,BYROW(b,LAMBDA(x,ROUND(AVERAGE(x),-1))),d,DROP(REDUCE("",c,LAMBDA(v,w,VSTACK(v,TOROW(SCAN(w,SEQUENCE(5),LAMBDA(x,y,MROUND(x*1.05,5))))))),1),HSTACK(a,VSTACK(TEXT(DATE(2023,SEQUENCE(,12),1),"mmm"),HSTACK(b,c,d))))

=LET(_j, BYROW(B2:G6, LAMBDA(r, MROUND(AVERAGE(r), 10))), VSTACK(HSTACK(A1, TEXT(30*SEQUENCE(, 12), "mmm")), HSTACK(A2:G6, REDUCE(_j, SEQUENCE(5), LAMBDA(i, x, HSTACK(_j, MROUND(i*1.05, 5)))))))

=LET(
 _col, SEQUENCE(COLUMNS(B1:G1), , 1, 0),
 _jul, MROUND(MMULT(B2:G6, _col) / ROWS(_col), 10),
 HSTACK(
 A2:G6,
 REDUCE(
 _jul,
 SEQUENCE(12 - COLUMNS(B1:G1) - 1),
 LAMBDA(a, v, HSTACK(a, MROUND(TAKE(a, , -1) * 1.05, 5)))
 )
 )
)

=> A1:G6 

=LET(t,A1:G6,d,DROP(t,1,1),c,COLUMNS(d),j,MROUND(MMULT(d,SEQUENCE(c)^0)/c,10),HSTACK(t,VSTACK(PROPER(TEXT(SEQUENCE(,12-c,c+1)&"/23","mmm")),REDUCE(j,SEQUENCE(11-c),LAMBDA(a,b,HSTACK(j,MROUND(a*1.05,5)))))))

=LET(a,A1:G6,
b,OFFSET(a,1,1,5),
c,ROUND(BYROW(b,LAMBDA(v,AVERAGE(v))),-1),
d,REDUCE(c,SEQUENCE(5),LAMBDA(v,x,HSTACK(v,TAKE(MROUND(v*1.05,5),,-1)))),
e,TEXT(DATE(,SEQUENCE(,6,7)+1,),"Mmm"),
HSTACK(a,VSTACK(e,d)))

df=xl("A1:G6", headers=True)
df['Jul']=df.mean(axis=1).round(-1)
for c in ['Aug','Sep','Oct','Nov','Dec']:
 df[c]= (df.iloc[:,-1:]*2.1).round(-1)/2
df