At a glance
An apple grower in New Zealand has a warehouse stocked with different types of apples stored in crates on shelves.
It would be useful for him to know the location not just of the type of apples and where they are in the warehouse, but also how old they are.
With this information, he could better manage his inventory and improve the speed at which the apples could leave the warehouse and more stock could be added.
New features in Excel
One low-cost solution to this, perhaps surprisingly, lies in Microsoft Excel. It’s not found in the tried and tested spreadsheet which most businesses have used for a decade or more, but in one of the newer features which has transformed Excel from a data entry tool to one offering self-serve business intelligence.
The apple warehouse example was one of the business cases which came across the desk of Excel expert Mynda Treacy, who operates the training site My Online Training Hub.
Treacy, who has the status of a “Most Valuable Professional” (MVP) accredited by Microsoft, is in the business of helping her clients solve data and business intelligence issues with Excel.
When the New Zealand apple grower got in touch, she recognised the problem could be addressed with a new feature called 3D Maps, which is now fully integrated in Excel 2016 as part of the Office 365 suite.
3D Maps was previously called Power Maps and was available under particular licences as part of Excel 2013, but is now simply a tab which can be accessed on the Excel programs downloaded by hundreds of thousands of Australian businesses as part of their Office subscription.
“It was super easy and very intuitive,” says Treacy. “You just drag and drop from a data spreadsheet onto 3D Maps.”
3D in Excel
Data on the grower’s apples was taken from the spreadsheet and dragged onto 3D Maps, which created a three-dimensional representation of the warehouse with apples marked by location, type and age.
An added benefit was that the representation was in 3D, giving the location of the apples by height, tracking their position on stacked shelves.
“There was also a rule created which marked the age of the apples, and which could be updated and refreshed,” says Treacy, who uses the example to show that mapping can be applied in many ways.
Any physical environment, she says, has the potential to be mapped.
Still in the agricultural sector, she cites the case of a dairy farmer who tracked the location of his cows using chip implants, an Excel database and 3D Maps.
“He was able to track the movement of the cows at any time of the day just by hitting refresh,” says Treacy.
“And there was no coding, no formula, it’s all a point-and-click interface which is all about how you set up the underlying data.”
Using Excel 3D maps for forecasting
Liam Bastick is another Microsoft MVP who has also used 3D Maps to solve issues for clients.
Bastick operates his company Sum Product from Melbourne, with offices in Sydney, London and New York.
In his case, he was working with a service provider in a highly regulated industry. Under the terms of its contract, the company was issued with penalties for any failures in restoring service to customers over a wide geographical area.
“They had to provide a service and if they didn’t do so adequately there was a calculated penalty which could run into millions of dollars,” says Bastick.
“Many of the factors which determined their service were out of their control, so their challenge was how they could forecast where the problems would be so they could respond quickly and minimise the penalties.”
Using Excel functions to collect and analyse data
Bastick says he and his team took the client’s data from several information systems and “cleaned it” using Excel functions Power Query and Power Pivot.
Then, using the Excel sister product Power BI, they created an interactive report which illustrated correlations between particular incidents and service failures. From this, they plotted the incidents on a 3D Map.
“So now they have a good idea of where to have repair crews, what type of problem it is likely to be and what kind of equipment they will need to restore the service as quickly as possible,” says Bastick.
“The savings so far into the project are in the area of seven figures, up in the millions of dollars, and really they were just working off the low hanging fruit.”
Although it wasn’t available at the time, another Excel feature called FORECAST.ETS could have added even more insight to this situation and enabled some future forecasting on likely service failures.
Available on Excel 2016, FORECAST.ETS can be used to predict future sales, inventory requirements, consumer trends, or even service failures based on historical data, all with the press of a return key.
