Home

Projecting Data Figures - Years in Advance

%3CLINGO-SUB%20id%3D%22lingo-sub-745165%22%20slang%3D%22en-US%22%3EProjecting%20Data%20Figures%20-%20Years%20in%20Advance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-745165%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone!%3CBR%20%2F%3E%3CBR%20%2F%3EI'm%20sailing%20into%20unchartered%20territory%20in%20Excel%20and%20am%20quite%20stumped%20and%20endless%20searching%20on%20the%20internet%20hasn't%20helped%20me%20too%20much%20to%20find%20the%20answer%20that%20I'm%20after.%3CBR%20%2F%3E%3CBR%20%2F%3EI'm%20needing%20to%20project%20future%20year%20totals%20from%20the%20data%20I've%20been%20provided.%3CBR%20%2F%3E%3CBR%20%2F%3EI've%20got%20figures%20of%20jobs%20per%20industry%20from%20years%202012%2F2013%20and%20then%20in%202017%2F2018%20which%20leads%20to%20a%20positive%20or%20negative%20change%20after%205%20years.%3CBR%20%2F%3E%3CBR%20%2F%3EI'd%20like%20to%20forecast%20what%20changes%20there'd%20be%20in%20another%205%20or%20so%20years%20time%20but%20can't%20get%20my%20head%20around%20on%20how%20to%20do%20it%20exactly.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EAny%20information%20would%20be%20very%20much%20appriected!%3CBR%20%2F%3E%3CBR%20%2F%3EI've%20attached%20my%20excel%20spreadsheet%20with%20the%20relevant%20data%20that%20I'd%20like%20to%20project%20from.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-745165%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eforecast%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-746151%22%20slang%3D%22en-US%22%3ERe%3A%20Projecting%20Data%20Figures%20-%20Years%20in%20Advance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-746151%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373431%22%20target%3D%22_blank%22%3E%40Jamos7%3C%2FA%3E%26nbsp%3B%20A%20forecast%20based%20on%20only%20two%20data%20points%20from%20the%20past%20will%20be%20very%2C%20very%20vague.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOne%20possibility%20would%20be%20to%20apply%20the%20same%20change%20percentage%20to%20the%20forecast%20value%2C%20i.e.%20if%20the%20change%20in%20the%20last%205%20year%20period%20was%20-12%25%2C%20apply%20that%20percentage%20to%20the%20latest%20value.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-746669%22%20slang%3D%22en-US%22%3ERe%3A%20Projecting%20Data%20Figures%20-%20Years%20in%20Advance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-746669%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20the%20reply%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERealised%20I've%20missed%20out%20some%20of%20the%20data!%3CBR%20%2F%3E%3CBR%20%2F%3ESomehow%20I%20need%20to%20create%20a%20linear%20model%20that%20predicts%20the%20number%20of%20people%20employed%20(FTE%20Number)%20for%20future%20years%20based%20off%20business%20activity%20(Bus%20number%20-%20in%20the%202018%20and%202017%20columns).%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20that%20makes%20any%20sense%20as%20I'm%20struggling%20to%20wrap%20my%20head%20around%20it%20with%20no%202017%20data%20as%20to%20how%20many%20staff%20were%20employed%20per%20industry%20for%20that%20year.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-746691%22%20slang%3D%22en-US%22%3ERe%3A%20Projecting%20Data%20Figures%20-%20Years%20in%20Advance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-746691%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373431%22%20target%3D%22_blank%22%3E%40Jamos7%3C%2FA%3E%26nbsp%3B%20As%20I%20said%20above%2C%20any%20forecast%20based%20on%20just%20two%20past%20data%20points%20will%20be%20very%20difficult%2C%20but%20if%20you%20don't%20even%20have%20two%20data%20points%2C%20then%20a%20crystal%20ball%20might%20be%20more%20help%20than%20Excel.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESorry%20to%20be%20so%20blunt%2C%20but%20Excel%20is%20great%20at%20crunching%20numbers.%20It%20can%20do%20forecasts%20and%20predictions%2C%20too%2C%20but%26nbsp%3B%20it%20really%20needs%20data%20for%20that.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Jamos7
New Contributor

Hello everyone!

I'm sailing into unchartered territory in Excel and am quite stumped and endless searching on the internet hasn't helped me too much to find the answer that I'm after.

I'm needing to project future year totals from the data I've been provided.

I've got figures of jobs per industry from years 2012/2013 and then in 2017/2018 which leads to a positive or negative change after 5 years.

I'd like to forecast what changes there'd be in another 5 or so years time but can't get my head around on how to do it exactly. 

Any information would be very much appriected!

I've attached my excel spreadsheet with the relevant data that I'd like to project from.

3 Replies

@Jamos7  A forecast based on only two data points from the past will be very, very vague.

 

One possibility would be to apply the same change percentage to the forecast value, i.e. if the change in the last 5 year period was -12%, apply that percentage to the latest value.

Thanks for the reply @Ingeborg Hawighorst 

 

Realised I've missed out some of the data!

Somehow I need to create a linear model that predicts the number of people employed (FTE Number) for future years based off business activity (Bus number - in the 2018 and 2017 columns).

If that makes any sense as I'm struggling to wrap my head around it with no 2017 data as to how many staff were employed per industry for that year.


@Jamos7  As I said above, any forecast based on just two past data points will be very difficult, but if you don't even have two data points, then a crystal ball might be more help than Excel.

 

Sorry to be so blunt, but Excel is great at crunching numbers. It can do forecasts and predictions, too, but  it really needs data for that.