【Consulting Case】Restaurant (3) : Monthly Snapshot

Recap

In the last post, I went over how we improved the accounting and financial reporting processes. Then, I used the processed data to visualize the sales trend with breakdown by product groups and products.

 

2020/07/13 - [Research & Analysis/Financial Modeling] - 【Consulting Case】Restaurant (2) : Exploratory Analysis

2020/07/13 - [Research & Analysis/Financial Modeling] - 【Consulting Case】Restaurant (1) : Accounting

 

In the following posts, I will illustrate how the processed data were used for internal analysis, particularly to extrapolate information necessary to set up future operational strategies.

 

 

Monthly Sales Snapshot

 

One of the most important goal from this initiative was identifying key product lines for marketing focus and re-pricing. To achieve this, I created this Monthly Snapshot workbook, which directly sources from the stacked transaction-level data. This workbook is 100% synced to the POS data.

 

 

Overall Sales & Change

This section presents the breakdown of sales and counts by product group. Based on the sales amount and counts, each product group is given ranks.

 

Sales & Change by Product
within a Specified Product Group

To the left of the Sales & Change by Product table is a dropdown cell that contains the list of product groups. 

 

Here, the product group of interest is defined, and this prompts the table to calculate the appropriate metrics for the menus within the group.

 

 

Below the dropdown menu, there is a banner that contains the sales amount and count of the Best Seller and Highest Growth products.

 

If you change the top-left cell with the Month to a different month, i.e. 2018/02/01, the tab recalculates all fields automatically.

 

 

 

Formula Used

1. eomonth: used to get the start and end date of the specified month ("2018/03/01").

 

 

2. sumifs: used to calculate the sales/counts between the dates defined by the eomonth formula.

 

function used for the cell highlighted in Green

 

3. rank: used to rank %change in sales amount and counts.

 

4. vlookup: used to retrieve the name of menus under the specified product group.

 

5. index: used to retrieve the name of the menu with the highest sales amount or highest growth rate (defined by "1" in the first match() statement.