Improv with Excel? Yes, but…

Date Posted: February 24, 2021

Even if you’ve never tried improv, you’ve probably heard of the “yes, and…” rule. The idea is that when someone else suggests something outside-the-box, you simply accept it and then expand upon it. It enables the conversation to continue instead of shutting it down the way a “but” response would.

Strategy consultants encourage us to apply the same principle to business to promote new lines of thinking and further exploration of insights. In a brainstorming session, that’s relatively easy. In day-to-day conversations analyzing business performance and discussing ideas for improvement though, your tools might make it near impossible. How many times do you ask a follow-up question to hear:

“Yes, but… it will take a week to analyze the data.”

“Yes, but… we don’t have the data we need in here.”

“Yes, but… I’ll have to reconfigure the report.”

For example, you’re in a meeting with your team and you notice out-of-stocks at Walmart are high. You wonder, which products are having the greatest in-stock issues? Which regions? Do you have the same problem at other retailers? If you’re relying on Excel, don’t expect a fast answer that enables you to move on to solving the problem.

Yes, Excel is a highly flexible analytics tool, and it's great at many things, but speed isn't one of them. The way it's built doesn't enable quick ad hoc analysis to answer the next question and keep the conversation going.
-

Now, what if the same thing happened in a conversation with a buyer? You could miss out entirely on the opportunity to sell-in additional product because you’re not able to get the insight right away.

Relying on Excel for retail and supply chain analytics, even with Excel wizards, limits your growth when how quickly you can respond to changes determines winners and losers.

 

Excel cannot handle large data sets

Insights you can trust don’t come from small sample sizes. Retailers provide the robust data you need, but… Excel is not built for you to take advantage of it all.

  • Worksheet size: Excel can have a maximum of 1,048,576 rows and 16,384 columns in a single worksheet. That sounds like a lot, but businesses quickly exceed it with granular SKU/store/day data. A month’s worth of daily data for 1,800 doors will hit this limit if you have 20 or more SKUs!
  • File size: Depending on what operating environment you’re using, there may be a hard limit on file size. Even if not, the available memory and system resources limit you. Just opening a 250MB file feels like it can take hours, especially when you’re trying to quickly answer a question.
  • Processing speed: Excel is installed directly on your computer instead of a cloud application, so the computing power of your machine inherently limits you. This constraint extends to everything you might want to do for analysis, from a “simple” copy/paste to calculating metrics with formulas and creating and editing PivotTables and charts.
Combining multiple data sets is a pain

You can work around some of these limitations by storing data in separate worksheets or workbooks, but… what happens when you want to compare across time periods, or across retailers?

  • Setup time: Before combining data sets, you must first ensure the columns (and/or rows) are arranged exactly the same way. Swapping sales and returns, units on hand and units on transit or any two metrics for that matter can easily happen in large worksheets and produce some strange results.
  • Data harmonization: When you have data from multiple sources, like Amazon and Target, not only do you have to ensure it’s formatted the same way before it’s combined, but also that it’s in the same “language.” That means translating ASINs to DPCIs or vice versa, or translating both into your internal SKU numbers. Any category analysis means translating between their different product hierarchies, and any geographic analysis further means translating Target store numbers into zip codes. It’s all possible with lookup tables and other functions, but it’s a serious pain.
  • Rinse and repeat: Far from a one and done exercise, you have to go through these steps each time you want to do a slightly different analysis. Decide you want to compare month-over-month instead of quarter-over-quarter, or want to see what it looked like two weeks ago? Each analysis will be almost as painful as the last.
Manual data manipulation abounds

Now let’s talk about the actual analysis. Instead of combing through rows and rows of data, you want to summarize and visualize it in different ways to help you spot trends and areas for further analysis, but… that’s a manual process, adding time and potential for error to it.

  • Data groupings: Some of the most common ways to group data are by category, then product and product attribute, or geographically by region, then state and location. Of course, the raw data doesn’t come pre-grouped this way, so you have to format your worksheet accordingly. When you need to reorder the hierarchy based on questions that come up, say to start with all lemon-scented products, or introduce another layer of grouping, like location tier, you’ll have to manually rearrange all that data.
  • Visualizations: The right chart can really make insights pop. Getting it just right, though, can require playing with the chart type, what data is included, what metrics are displayed and even the colors. All that “playing” equates to a lot of clicking, waiting to see how the chart changes and making more adjustments.
  • Summary metrics: It’s easy to make a mistake when adding formulas on the fly, or unintentionally change a formula while trying to do something else. If you haven’t locked down your worksheet (making ad hoc analysis impossible), you always risk introducing human error. These accidents are particularly problematic because they are so hard to spot and troubleshoot. They can pollute your analysis and conclusions, unnoticed.
Excel only does one thing

Excel is a flexible analytics tool, but… it can’t help you sense what’s going on, alert you to opportunities or resolve problems like a complete solution.

  • Data collection: The data you analyze in Excel has to come from somewhere, and that problem is left entirely up to you to solve (often through more manual work exporting data from retailer portals, EDI feeds and other email spreadsheets). As a result, you’re working with data that is only as up-to-date as the last time someone pulled it.
  • Alerting: You don’t have all day to spend monitoring data and looking for insights; you just need to know about the top issues and when something unexpected happens. Excel can help identify these situations, but it has no way of bringing them to your attention automatically.
  • Sharing and permission management: You’ve identified some important insights, great! Now it’s time to share them with buyers and colleagues in operations. Except with Excel, you have to worry about who has access to what, version control and collaboration across different systems.

Dealing with all these hurdles is frustrating, yes, but it’s also costing you time and sales. Upgrade to Alloy’s cloud platform that automates data collection, harmonization and reporting with easy-to-customize dashboards, and you could be hearing:

“Yes, and I’d like to order more while I’m thinking about it.”

“Yes, and we can extend the promotion and load-in more product.”

“Yes, and we can get inventory there in time to prevent lost sales.”

Related resources

Customer Stories

A leading fitness equipment maker hits its stride with Alloy

The speed and flexibility of Alloy POS analytics enabled this consumer brand to make more timely decisions for new product success and inventory management.

View
Article

Talking better product launch and allocation decisions with Ferrero USA

The global confectioner mitigates waste, improves service levels and controls costs by connecting digital supply chain visibility with POS analytics.

Keep reading
Article

Say goodbye to constant supply chain firefighting: A guide

How to take an iterative approach to digital supply chain transformation with real-time alerts that motivate teams to collaborate on issue resolution

Keep reading