Custom Dashboards Case Study
How we saved one Business Over $4,000 a Month with a Custom Sales, Expenses & Profitability Dashboard
Do you really know exactly what your sales, expense and profitability figures are right now?
Not sure?
Well, here’s a case study where one business owner thought she was on top of things, only to discover that her business was leaking cash faster than a Wikileaks report.
Whether you’re at your office or overseas, if you rely on 3rd Party reports, you’ll know how often they tend to be inaccurate, out of date or simply inaccessible – just when you need them the most.
This brings me to our restaurant case study where, in under 3 weeks, we identified $4k savings in monthly expenses for a local restaurant.
Issue #1: Time wasted & Frustration at not having ready access to Business Information
The restaurant owner mostly used her phone or a tablet to access her business reports – all in Excel spreadsheets. However, she found it almost impossible to keep track of sales and expenses from email attachment sent to her by her Admin when she was away.
An obvious solution was to move her Excel files to the cloud.
That was easily done by creating a Dropbox account so she and her Admin staff could share files. However, with that done, we discovered that the spreadsheets themselves contained over-complicated (broken) formulae spanning multiple workbooks.
Long story short, interpreting and comparing figures (especially on her phone) was an exercise in futility.
Solution #1: Cloud based Custom Dashboard Reports
We gave her the option of using whatever cloud service she felt comfortable with to host her Excel spreadsheets and her new Dashboard Reports. She decided to stay with Dropbox because to her it was easy to use, reliable and cost effective.
She needed up to date graphical “Sales, Expenses and Profitability” reports with the ability to drill-into any aspect of interest e.g. a spike in expenses or excessive kitchen wastage. We suggested a customized, three-page live Dashboard Report, one page each for Sales, Expenses, and Profitability.
We built useful charts, tables, and filters for each page (tapping in a chart actually cross-filters the other elements on the page which was a feature that she found extremely useful).
She could now view her operations in easy to understand visuals that she could drill into to analyze all aspects of her Sales & Expenses.
She could also tell at a glance what items were best sellers or those with excessive costs or low sales that she should remove.
Outcome #1: Live, Actionable Business Intelligence
Once her expenses were visible in gorgeous charts – even on her phone – she and her team could instantly identify areas of concern.
It was almost as if someone had suddenly turned on the lights. For the first time their sales, expenses and profitability reporting made much more sense to the whole restaurant team.
Issue #2: Excess Orders not aligned with Sales
With enhanced visibility into their data, one area of concern that became immediately apparent was a lack of cost and quantity control in their purchasing procedures. This highlighted disconnects between the Inventory and Receiving departments from people not knowing who had done what.
Ordering and receiving inconsistencies were quickly apparent. Some daily purchases exceeded expected sales thereby resulting in excessive supplier payments that cut into profits.
As an example: Using their dashboard to cross-check activities between departments, Purchasing claimed to have ordered 4kg of cheese, but a delivery of 10kg had been accepted and signed for by Receiving. And the Accounts department confirmed they’d received an invoice from the supplier for 10kg and had raised a payment voucher that’d been entered into Excel.
Checking further between their procurement, receiving & inventory records, revealed that cheese wasn’t the only discrepancy. In no time, they’d uncovered similar discrepancies from multiple suppliers going back 6 months.
Solution #2: Shared Purchasing and Inventory Management
We also shared their Excel Purchasing & Inventory data with their suppliers via Dropbox.
The result was that suppliers could now automatically update prices online to reflect seasonal fluctuation and could also see their orders from the restaurant prior to preparing their deliveries.
No more “he said – she said”. The suppliers and the restaurant’s purchasing department were all on the same page.
Outcome #2: Less Time and more Accurate Ordering
Shared Excel files replaced Whatsapp, email and phone calls, saving them hours. Anyone involved (including the owner), just needed to view the shared data to know exactly what was being delivered and when.
With their Purchasing & Inventory data live on-line, easily view-able and shared across all relevant departments, their extra ordering problem was immediately eliminated.
Issue #3: Kitchen ordering errors costing $1,600 a Month
With the new system in place, the restaurant’s owner quickly discovered how badly her bottom-line was being impacted by important details that were regularly missed by people busily scrolling through screeds of ‘messy’ spreadsheets to manually extract numbers for her.
She later discovered just how much deeper the problems went. Fruit & vegetable ordering was also regularly incorrect.
The Kitchen would place orders e.g. for 5kg of spinach, but the supplier delivered and charged for 6.2kg of spinach daily.
It was the same for many other items, which overall added an additional $50-$70 per day, or $1,600 each month due to erroneous supplier payments.
Solution #3: Improved Cross-Checking
Up until then, there was no cross-checking or knowledge of what the chefs or bar staff had actually ordered the night before.
We implemented a similar Dropbox Online ordering system for the Kitchen to order fruit and vegetables, also giving Receiving and Accounts Departments access to the shared information.
Outcome #3: Ordering Errors almost Eliminated
Prior to implementing the Dashboards, with online Dropbox ordering, the Receiving department relied on quantities and costs that the supplier had entered on the delivery docket.
With this small change, ordering errors were minimized.
Summary: Overview of a business that needed a Technology Transformation
With no prior knowledge of how to use Dropbox, but a keen desire to adopt new technology and wanting graphical visuals to view weekly and monthly performance, (rather than manually crunching numbers), all that was required of them was to share their spreadsheet data with us in Dropbox.
Our team created an elegant online Dashboard for them displaying their business performance in beautiful visuals that they could slice & dice and drill-into at the tap of a screen.
“Guestimated” or out-dated figures that were previously tossed around at monthly meeting were no more.
Before then, none of her staff had ever actually seen their figures in a comparative format that were pin-point accurate, and they were amazed at how easily they could find data that they needed to do their jobs.
In previous years, critical data had been buried in complex, (broken) formula-driven excel spreadsheets in hidden folders that were unavailable to most.
On top of that, with the restaurant’s profitability calculations being based on income and expense amounts entered in isolation and with no cross checking between departments, their reports were riddled with errors – which is why the owner had been so keen to engage us to gain an up-to-date true financial picture of her restaurant.
The transparency and visibility that their online Restaurant Management Dashboard, with great visuals, brought to her business made a huge difference. With data instantly available and easily understood, her team were more empowered than ever before.
With very fine profit margins in the hospitality industry, a Business Dashboard to instantly view your sales, expenses & profitability is a no-brainer because you can instantly rectify issues as they happen rather than waiting for weekly or monthly figures.
Feel free to contact us to discuss how an Excel (or other data sources) to Dashboard Reporting Solution could benefit your business.