Gaining a Competitive Edge: Business Forecasting with Third-Party Data and Microsoft Excel

Safegraph Logo Blue White

Gaining a Competitive Edge: Business Forecasting with Third-Party Data and Microsoft Excel

In our most recent webinar, Ryan Squire, Senior Data Scientist at SafeGraph and Dave Mariani, Co-Founder and Chief Strategy Officer of AtScale shared how to turn Excel into a crystal ball for your business forecasting – painlessly.

Importance of Business Forecasting

Squire states, “Forecasting is all about predicting the future based on the past and the present. Business forecasting can predict all sorts of things about your business, things like future sales, future customer demand, future costs, supply costs. The better you can forecast, the better you can plan, adapt and capitalize on those opportunities.” 

Benefits Of Third Party Data

What are the benefits to working with third party data? Squire goes into detail, sharing the following:

  1. Compliments incomplete first party data
  2. Enriches first party data
  3. Provides data on competitors
  4. Captures contextual market and economic data

Solving Problems with Third Party Data

“The sky’s the limit in terms of what you can do, it’s really about what your business needs and what your business problems are,” says Squire. With third party data, you can enhance the following:

  1. Sales Strategy
  2. Demand Planning
  3. Predictive Analysis Accuracy
  4. Forecasting Customer Value

Ryan later shares two customer stories and how they’re using first and third party data:

  1. US Foods: Understand and predict the shifts in demand that were happening during the industry wide disruption during COVID-19.
  2. Neoway: Better optimize the product mix that they’re stocking at restaurants, bars, grocery stores, convenience stores and how to best predict the demand for their different products. 

Loading Foot Traffic Data

In this use case, Dave uses Excel to forecast inventory, and quantities of products, that he will need going to need by a product category, for the next three months for his sporting goods store. He shares how he uses SafeGraph’s third party data to account for the foot traffic difference in the stores or for and adjust those sales forecasts accordingly for COVID. 

Excel is Not a Database!

“This is why IT hates Excel. Sometimes we use Excel as a database as opposed to a forecast and modeling tool, which it is really made for,” says Mariani. He previews the alternative solution of connecting Excel live to data using, it’s built in OLAP interface, “All that summarization and all the slicing and dicing that we need to do is going to happen in Snowflake or on the server side. The data that we’re going to be dealing with Excel is just the granularity that we need to do our forecasting.” 

Loading Baseline Sales Data

Dave shares how he exports the sales data in Tableau to Excel.

Refreshing the Model

Updating that data is error prone,” says Dave. He shares the reason for why it’s “game over” when you copy and paste into Excel. What’s the alternative? Dave says that to use the live connection and GETPIVOTDATA and CUBE functions in Excel. Similar to solving the previous problem, the options you can use are an Excel add-in or an OLAP tool that integrates directly with Excel. 

Building a Sales Forecast Model

Dave shares how he built his forecast by referencing the pivot table data from the input, which was the foot traffic Delta where he adjusted the sales data from. Where did he run into problems? He recalls, “I actually had to do these formulas and do this Delta between this month, this year and last year. And I did that manually. And you know what, what if I wanted to have these to be a moving average instead of a straight average, or just a sum? These things can get kind of tricky pretty quickly.”

Trusting the Data

If  I’m manually splicing data, I’m almost assured that it’s not gonna match the corporate truth, because it’s probably not going to match my retail calendar. The data may be different and be refreshed differently. It may have been revised. I may be using old numbers,” says Mariani. He continues, “It’s a simple model, but they can get pretty complicated fast.” 

“I shouldn’t have to worry about also coming up with the calculations for the base data, whether it’s third party or first party data. So there’s different options for this.” Dave recommends using OLAP powered virtualization to enforce consistent data. The solution? “I can use an OLAP tool like SQL server analysis services, or I can use a virtualization tool that’s OLAP powered, like AtScale. I can also use any virtualization tool that supports Excel and SQL.” 

See AtScale in Action

In this demonstration, Dave shares how AtScale can make your queries run faster against a 343 million row database. 

Where to, next? 

Request a Demo