Linear Extrapolation / Forecast for upcoming months

T

Trepidatioussaurus

New Member
Joined
Oct 12, 2022
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
Hello Everyone,

I have got a data model at hand with several dimensions including date, hierarchical and structural fields as seen in the table below. What I now would like to do is to calculate sales for upcoming months of the current year. How it's supposed to be calculated is shown in the pivot table here:

Team(All)
Region(All)
MonthSumme von Actual Year SalesSumme von Previous Year Sales
Jan3421
Feb3230
Mrz3835
Apr4039
Mai3438
Jun3339
Jul4128
Aug2945
Sep4534Sales per Day (previous months)Days in upcoming monthsResult lin. Extrapolation
Okt03013137
Nov02913036
Dez03313137



Take the average sales per day of passed months of current year. Multiplay sales per day with number of days of the upcoming months.

The tricky part now is to do that in a column added to the flat table, so it calculates correct values per month in pivot table, even when you select a team. I tried but couldn't get it done and I'm not sure it's possible since some data points are missing when filter for certain teams or regions.

Maybe someone has an idea what to do. And if there's already a threat that adressed a similar topic, please let me know.

Thanks in advance and kind regards

Stefan

Excel 2016 (soon O365)


Linear Extrapolation.xlsx
ABCDEFGHI
1YearMonthMonthNoDepartementTeamRegionProduct CategoryActual Year SalesPrevious Year Sales
22022Dez12DDDDUKLCCShoes06
32022Dez12DDDDUKLÜSShoes03
42022Dez12DDDDUKLÜAShoes01
52022Dez12DDDDUKLÜSJackets01
62022Dez12DDDDNOKCCShoes08
72022Dez12DDDDNOKÜSShoes03
82022Dez12DDDDNOKÜAShoes03
92022Dez12DDDDNOKÜSJackets03
102022Dez12DDDDNOKCCJackets01
112022Dez12DDDDNOKÜAJackets01
122022Dez12DDDDKGEÜSShoes02
132022Dez12DDDDKGECCShoes01
142022Nov11DDDDUKLÜSShoes04
152022Nov11DDDDUKLCCShoes02
162022Nov11DDDDUKLÜAShoes02
172022Nov11DDDDUKLÜAJackets01
182022Nov11DDDDNOKCCShoes012
192022Nov11DDDDNOKÜSShoes01
202022Nov11DDDDNOKÜAShoes01
212022Nov11DDDDNOKÜAJackets01
222022Nov11DDDDKGECCShoes02
232022Nov11DDDDKGEÜSShoes01
242022Nov11DDDDKGEÜSJackets01
252022Nov11DDDDKGEÜAJackets01
262022Okt10DDDDUKLCCShoes05
272022Okt10DDDDUKLÜSShoes01
282022Okt10DDDDUKLCCJackets01
292022Okt10DDDDUKLÜSJackets01
302022Okt10DDDDNOKCCShoes09
312022Okt10DDDDNOKÜSJackets04
322022Okt10DDDDNOKÜSShoes03
332022Okt10DDDDNOKÜAShoes02
342022Okt10DDDDKGEÜSShoes02
352022Okt10DDDDKGECCShoes01
362022Okt10DDDDKGEÜSJackets01
372023Sep9DDDDUKLCCShoes40
382023Sep9DDDDUKLÜSShoes40
392023Sep9DDDDUKLÜAShoes30
402023Sep9DDDDUKLÜSJackets30
412023Sep9DDDDUKLCCJackets10
422022Sep9DDDDUKLÜSShoes07
432022Sep9DDDDUKLCCShoes03
442023Sep9DDDDNOKÜSShoes60
452023Sep9DDDDNOKÜAShoes60
462023Sep9DDDDNOKCCShoes30
472023Sep9DDDDNOKÜAJackets20
482023Sep9DDDDNOKCCJackets10
492023Sep9DDDDNOKÜSJackets10
502022Sep9DDDDNOKCCShoes010
512022Sep9DDDDNOKÜSShoes05
522022Sep9DDDDNOKÜAShoes05
532022Sep9DDDDNOKÜSJackets02
542022Sep9DDDDNOKCCJackets01
552023Sep9DDDDKGECCShoes50
562023Sep9DDDDKGEÜSShoes40
572023Sep9DDDDKGEÜAShoes20
582022Sep9DDDDKGEÜSShoes01
592023Aug8DDDDUKLCCShoes30
602023Aug8DDDDUKLCCJackets20
612023Aug8DDDDUKLÜSShoes10
622023Aug8DDDDUKLÜAShoes10
632022Aug8DDDDUKLCCShoes04
642022Aug8DDDDUKLÜSShoes04
652022Aug8DDDDUKLÜAShoes04
662022Aug8DDDDUKLÜSJackets03
672023Aug8DDDDNOKCCShoes120
682023Aug8DDDDNOKÜSJackets30
692023Aug8DDDDNOKÜSShoes20
702022Aug8DDDDNOKCCShoes018
712022Aug8DDDDNOKÜSShoes06
722022Aug8DDDDNOKCCJackets02
732022Aug8DDDDNOKÜAShoes01
742022Aug8DDDDNOKÜAJackets01
752023Aug8DDDDKGECCShoes30
762023Aug8DDDDKGEÜAShoes20
772022Aug8DDDDKGECCShoes02
782023Jul7DDDDUKLCCShoes50
792023Jul7DDDDUKLÜAShoes30
802023Jul7DDDDUKLÜSShoes20
812023Jul7DDDDUKLÜSJackets20
822023Jul7DDDDUKLCCJackets10
832022Jul7DDDDUKLCCShoes05
842022Jul7DDDDUKLÜSShoes03
852022Jul7DDDDUKLÜAShoes01
862022Jul7DDDDUKLÜSJackets01
872022Jul7DDDDUKLÜAJackets01
882023Jul7DDDDNOKCCShoes90
892023Jul7DDDDNOKÜSShoes70
902023Jul7DDDDNOKÜAShoes30
912023Jul7DDDDNOKÜSJackets30
922022Jul7DDDDNOKCCShoes07
932022Jul7DDDDNOKÜAShoes03
942022Jul7DDDDNOKÜSShoes01
952022Jul7DDDDNOKCCJackets01
962022Jul7DDDDNOKÜSJackets01
972023Jul7DDDDKGECCShoes30
982023Jul7DDDDKGEÜSShoes20
992023Jul7DDDDKGEÜAJackets10
1002022Jul7DDDDKGEÜSShoes02
1012022Jul7DDDDKGECCShoes01
1022022Jul7DDDDKGEÜAShoes01
1032023Jun6DDDDUKLCCShoes50
1042023Jun6DDDDUKLÜAShoes40
1052023Jun6DDDDUKLÜSJackets40
1062023Jun6DDDDUKLÜSShoes20
1072023Jun6DDDDUKLCCJackets10
1082022Jun6DDDDUKLÜSShoes07
1092022Jun6DDDDUKLCCShoes04
1102022Jun6DDDDUKLÜAShoes02
1112022Jun6DDDDUKLÜSJackets01
1122023Jun6DDDDNOKCCShoes60
1132023Jun6DDDDNOKÜSShoes30
1142023Jun6DDDDNOKÜAShoes20
1152023Jun6DDDDNOKCCJackets10
1162022Jun6DDDDNOKCCShoes08
1172022Jun6DDDDNOKÜSShoes04
1182022Jun6DDDDNOKÜSJackets02
1192022Jun6DDDDNOKCCJackets01
1202023Jun6DDDDKGEÜSShoes30
1212023Jun6DDDDKGECCShoes10
1222023Jun6DDDDKGEÜAShoes10
1232022Jun6DDDDKGEÜSShoes04
1242022Jun6DDDDKGEÜAShoes04
1252022Jun6DDDDKGECCShoes01
1262022Jun6DDDDKGEÜSJackets01
1272023Mai5DDDDUKLCCShoes40
1282023Mai5DDDDUKLÜSShoes30
1292023Mai5DDDDUKLÜAShoes20
1302023Mai5DDDDUKLCCJackets20
1312023Mai5DDDDUKLÜSJackets10
1322022Mai5DDDDUKLCCShoes07
1332022Mai5DDDDUKLÜSShoes02
1342022Mai5DDDDUKLÜAShoes01
1352022Mai5DDDDUKLCCJackets01
1362023Mai5DDDDNOKCCShoes120
1372023Mai5DDDDNOKÜSShoes20
1382023Mai5DDDDNOKCCJackets20
1392023Mai5DDDDNOKÜSJackets10
1402022Mai5DDDDNOKÜSShoes010
1412022Mai5DDDDNOKCCShoes09
1422022Mai5DDDDNOKÜAShoes04
1432022Mai5DDDDNOKCCJackets01
1442023Mai5DDDDKGECCShoes20
1452023Mai5DDDDKGEÜSShoes10
1462023Mai5DDDDKGECCJackets10
1472023Mai5DDDDKGEÜSJackets10
1482022Mai5DDDDKGEÜSShoes02
1492022Mai5DDDDKGEÜSJackets01
1502023Apr4DDDDUKLCCShoes40
1512023Apr4DDDDUKLÜSShoes30
1522023Apr4DDDDUKLCCJackets20
1532023Apr4DDDDUKLÜSJackets20
1542022Apr4DDDDUKLÜSShoes010
1552022Apr4DDDDUKLCCShoes05
1562022Apr4DDDDUKLÜSJackets02
1572023Apr4DDDDNOKCCShoes120
1582023Apr4DDDDNOKÜSShoes40
1592023Apr4DDDDNOKÜAShoes40
1602023Apr4DDDDNOKCCJackets40
1612023Apr4DDDDNOKÜSJackets10
1622022Apr4DDDDNOKCCShoes011
1632022Apr4DDDDNOKÜSShoes04
1642022Apr4DDDDNOKÜAShoes01
1652022Apr4DDDDNOKCCJackets01
1662022Apr4DDDDNOKÜSJackets01
1672023Apr4DDDDKGEÜAShoes20
1682023Apr4DDDDKGECCShoes10
1692023Apr4DDDDKGEÜSShoes10
1702022Apr4DDDDKGEÜSShoes02
1712022Apr4DDDDKGECCShoes01
1722022Apr4DDDDKGEÜSJackets01
1732023Mrz3DDDDUKLÜSShoes70
1742023Mrz3DDDDUKLCCShoes60
1752023Mrz3DDDDUKLÜSJackets60
1762023Mrz3DDDDUKLÜAShoes10
1772022Mrz3DDDDUKLCCShoes05
1782022Mrz3DDDDUKLÜSJackets04
1792022Mrz3DDDDUKLÜSShoes03
1802022Mrz3DDDDUKLÜAShoes02
1812022Mrz3DDDDUKLÜAJackets01
1822023Mrz3DDDDNOKÜSShoes50
1832023Mrz3DDDDNOKCCShoes40
1842023Mrz3DDDDNOKÜAShoes40
1852023Mrz3DDDDNOKCCJackets20
1862022Mrz3DDDDNOKCCShoes010
1872022Mrz3DDDDNOKÜSShoes03
1882022Mrz3DDDDNOKÜAShoes03
1892022Mrz3DDDDNOKCCJackets03
1902023Mrz3DDDDKGEÜAShoes20
1912023Mrz3DDDDKGECCShoes10
1922022Mrz3DDDDKGEÜSShoes01
1932023Feb2DDDDUKLCCShoes30
1942023Feb2DDDDUKLÜSShoes30
1952023Feb2DDDDUKLÜSJackets30
1962023Feb2DDDDUKLÜAShoes20
1972022Feb2DDDDUKLCCShoes02
1982022Feb2DDDDUKLÜSShoes02
1992022Feb2DDDDUKLÜAShoes01
2002022Feb2DDDDUKLÜSJackets01
2012023Feb2DDDDNOKCCShoes90
2022023Feb2DDDDNOKÜSShoes30
2032023Feb2DDDDNOKÜAShoes20
2042023Feb2DDDDNOKCCJackets10
2052023Feb2DDDDNOKÜSJackets10
2062022Feb2DDDDNOKCCShoes08
2072022Feb2DDDDNOKÜSShoes07
2082022Feb2DDDDNOKCCJackets02
2092022Feb2DDDDNOKÜAShoes01
2102023Feb2DDDDKGEÜAShoes20
2112023Feb2DDDDKGECCShoes10
2122023Feb2DDDDKGEÜSShoes10
2132023Feb2DDDDKGECCJackets10
2142022Feb2DDDDKGEÜSShoes03
2152022Feb2DDDDKGECCShoes01
2162022Feb2DDDDKGEÜAShoes01
2172022Feb2DDDDKGEÜSJackets01
2182023Jan1DDDDUKLÜSShoes60
2192023Jan1DDDDUKLCCShoes30
2202023Jan1DDDDUKLÜAShoes10
2212023Jan1DDDDUKLCCJackets10
2222023Jan1DDDDUKLÜSJackets10
2232022Jan1DDDDUKLÜSShoes02
2242022Jan1DDDDUKLCCShoes01
2252023Jan1DDDDNOKCCShoes70
2262023Jan1DDDDNOKÜSShoes50
2272023Jan1DDDDNOKÜSJackets30
2282023Jan1DDDDNOKCCJackets20
2292022Jan1DDDDNOKCCShoes012
2302022Jan1DDDDNOKÜSShoes04
2312023Jan1DDDDKGEÜSShoes30
2322023Jan1DDDDKGECCShoes10
2332023Jan1DDDDKGEÜSJackets10
2342022Jan1DDDDKGEÜAShoes01
2352022Jan1DDDDKGEÜSJackets01
Sheet2
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
You must log in or register to reply here.

Similar threads

D
  • Question Question
Data Relationships between date tables, forecast tables and actual sales tables
Replies
1
Views
472
DrBacon
D
N
  • Question Question
Formula for a single list of Dates from two payment cycles and a list of fix dates
Replies
0
Views
1K
narendra
N
L
  • Question Question
Excel finance task
Replies
2
Views
503
Fluff
Fluff
C
  • Question Question
Pivot Table filtering question (Parameter with multiple parents)
Replies
1
Views
730
Chaytan
C
S
  • Question Question
Pivot Table Macro
Replies
0
Views
590
Smittybball40
S

Forum statistics

Threads
1,224,815
Messages
6,181,135
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top