A Cheat Sheet to get more out of Microsoft Excel
Ever wanted just the Cliff Notes version of Excel? We've got you covered with the Excel Power Users Quick Guide - and it's free.
Nick Green of EchoXL.com joins Excel TV host Rick Grantham and cohost Microsoft Excel MVP Jordan Goldmeier and author and Excel TV Host Oz du Soleil on Excel TV discussing the development and release of his Excel forecasting tool at EchoXL.com.
Rick: Our special guest is Nick Green from Columbia by way of Australia. Welcome to the show, Nick. Will you start off by telling everybody about your Excel background and your background in general?
Nick: I was introduced to Excel in supply chain industry after university. I didn’t learn much Excel studying business in University, which is funny because I use it in 50% of my work. Supply chain is quite a chaotic industry and chaos tends to mean people use Excel a lot. So I was forced to self learn. I wish I had studied VBA more formally because it would have saved me a lot of headaches. If you don’t have the structure for it now you pay for it later.
Rick: Can you explain about the EchoXL name? What is your site and where did the name come from?
Nick: EchoXL is a forecasting tool. When I think of forecasting, I think of echoes because the further away you look the softer your signal is much like an echo. And, echo is short. EchoXL.com is a forecasting tool. I’ve used a lot of forecasting software as a demand planner on my job and I thought those tools were built by statisticians for statisticians. There were so many people who had to do forecasting that were intimidated by these tools so I made something with simplicity to get them 98% of the way while automating and simplifying the process. That is what EchoXL tries to do.
Oz: How do you get from chaotic to meaning people use Excel a lot?
Nick: Because a lot of these companies have multimillion-dollar, streamlined software but Excel is popular among users because people underestimate the need for flexibility. All these businesses have quirks in their softwares that need to be adapted. Excel becomes more relevant, especially in supply chain.
Oz: What is an example of how things change fast in supply chain?
Nick: For example I worked for a cosmetics retail company where I had to forecast sales and make sure their inventory and stock was in the right location across the world. Each location had quirks. Inventory would have to meet special events or did not meet [historical] sales. Their software was rigid and wouldn’t cater to that, so we had to build similar software with Excel. There isn’t much out there with Excel forecasting tools in general. I’m trying to fill that void.
Jordan: When you built your tool, you had a version with no VBA. What advantage is there to taking VBA out and why did you want to?
Nick: The first version I sent to you was a beta version with VBA. I started with VBA and tried to make it work like an application. The ribbons were automatic, but I found when I sent it to people they would have problems setting it up. It wouldn’t get through the firewall or you couldn’t use it. If you’re sending it to friends, that’s OK. But if you’re sending that to customers, they get impatient and give up. I wanted to make something completely stable. If you work with constraints and a little VBA, what is embedded in Excel won’t work. Echo light has no VBA. I will be making more advanced versions with VBA that will be more heavy duty for organizations.
Jordan: I still love the VBA, but over time, as I learn more formula tricks, I came to feel like it’s overused. In a certain sense, less is more. You can do a lot of sweet things and efficient things with formulas and you didn’t have to use a run button. [Jordan hates run buttons!]. Formulas may be difficult, and VBA is easy, but I hope more developers will do more with Excel formulas because you can’t break them and it makes a lighter and tighter Excel application.
Oz: I would add, from bloggers that pointed out in the past, that if you have some big complicated formula, you are more likely to find somebody who can help you figure it out. But if you do VBA, you’re going to have a tougher time with somebody figuring it out. And when you get into writing code, a person may be more likely to say, “I don’t want to figure it out, let’s start from scratch.” So formulas are good for those reasons. I try to stick with formulas.
Rick: I see some irony in this, Jordan. This will probably be Bill Jelen’s favorite day. I’m a guy who owns Option Explicit VBA Blog (and author of Dashboards for Excel) thinks VBA is difficult!
Jordan: I know I have evolved. I can’t argue.
Oz: We have to have room to evolve or we have politicians that are 60 years old answering for things they did when they were 19. [Insert Chappaquiddick or other relevant pun here]
Rick: Nick, you use quite a few forecasting methods, can you discuss those and how you integrated those into Excel?
Nick: There are four forecasting formulas, but the most important one is called Winters Forecasting. Jordan is familiar. It is triple exponential smoothing. You need to run formulas in the background and hide them from the end-user because it’s complex. You are basically separating your trending and seasonality from your forecasting. You have variables that will determine how far back the forecast will look, for example several months or several years. I did everything in a processing sheet. You can go to VBA if you’re not enabling macros and you can go to Properties Section to hide the sheet. I think they are less breakable if you hide the sheet. It took a lot of trial and error and headaches, but it integrated them.
Rick: We talked about part of the development process. I did things in VBA and then I realized that was a problem. John Peltier talked about when you build for yourself, and then you build for your friends, there is growth in that. Then when you send it out to other people, you go to a further level of development. Can you talk about what led up to that? What did that development process look to you, for the benefit of our viewers?
Nick: Like most of your viewers, I built applications for myself and close colleagues. But when you have to build it at scale for people you don’t know, you have to hold their hands through using the template. The difference is you have to think about that user interface design or user experience. You have to imagine what the lazy impatient user is going to think almost like you’re writing an article. People using things for the first time want it to do what it’s supposed to do and not be annoying. Simplify. I added many features and I asked myself if the benefit of that feature was worth the extra weight and the extra complexity it added to the interface. I had to trash so many ideas and features I had spent days building.
Oz: Can you give us an example of what is a good idea that might not be worth having?
Nick: Initially, I had more tabs that would allow the user to control everything such as custom seasonality or weight given to Christmas sales, but the benefit was so small versus the learning curve and confusion introduced. So even though I put my sweat into it, I had to be honest and scrapped it.
Jordan: for our viewers, Nick, when we’re talking forecasting, for less statistically minded, we’re not just talking a weather forecast. Can you give information for forecasting in your world?
Nick: There are two kinds of forecasts in business and demand forecasting. There are finance people and engineers who have to forecast and predict sales of a product, say. The main one I’m referring to is historical forecasting where you’re looking backwards at historical trends and seasonal flows for a typical consumer product, for example, products with sales popular at Christmas, but diving in January. You use statistics to rationally predict and extrapolate the data into the future, basically relying on guest work.
Jordan: Using historical data to make guesses about the future. The different methods you have in your tool account for trends or cycles and looking at the past to get a pretty good guess about the future.
Nick: It is guessing. The least worst guess.
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.
Get the FREE Excel Power User Guide