Climate Change and Energy Consumption Reports Two reports of 3000 words each. Topics 1)CLIMATE CHANGE . 2) ENERGY CONSUMPTIONPLEASE CHECK THE SAMPLES BEFOR
Climate Change and Energy Consumption Reports Two reports of 3000 words each. Topics 1)CLIMATE CHANGE . 2) ENERGY CONSUMPTIONPLEASE CHECK THE SAMPLES BEFORE PLACING THE BID. Walton Supermarket
Executive Dashboard Review and
2015 Sales Growth Recommendations
Report
1|Page Walton Supermarket Executive Dashboard Review and
and 2015 Sales Growth Recommendations Report
Table of Contents
Executive Summary ……………………………………………………………………………………………………………………… 3
Executive Insight – What questions are senior management interested in addressing? …………………….4
Assumptions ……………………………………………………………………………………………………………………………….. 6
Performance Dashboards …………………………………………………………………………………………………………….. 7
CEO Dashboard………………………………………………………………………………………………………………………… 7
Sales Director Dashboard ………………………………………………………………………………………………………..10
Marketing Director Dashboard ………………………………………………………………………………………………..13
Finance Director Dashboard …………………………………………………………………………………………………….15
Logistics Director Dashboard ……………………………………………………………………………………………………16
Product Unit Pricing aligned to Loyalty Level influences overall sales ………………………………………..18
Targeting Cities which have a similar demographic to the top five cities ……………………………………18
Target Cities with 2 or more children ……………………………………………………………………………………….19
Increasing the volume of Units Sold ……………………………………………………………………………………. 20
Focus on Higher End Products with Households with 3+ children ………………………………………………20
FY 2015 Recommendations Putting this all together ………………………………………………………………21
References …………………………………………………………………………………………………………………………………22
2|Page Walton Supermarket Executive Dashboard Review and
and 2015 Sales Growth Recommendations Report
Executive Summary
Walton Supermarket is seen as an established player in the US grocery market food chain. With
presence in the US, Mexico and Canada, the business has expanded quite rapidly over the past three
years. This has resulted in the achievement of revenues of $ 121K over FY 2014, representing a 101%
increase over the previous financial year.
This growth over the 2013/2014 period was anchored around a number of tactical plays:
the geographic expansion into Mexico and Canada over FY 2014 (47K and 11K revenues
respectively)
the take up of the loyalty program in the Discount and Busy segment which represents an
additional 14K of customer revenue between 2013 and 2014
the acceleration of product sales in the Snack foods and Vegetable category
tripling of product unit sales from 18K to 57K
Profits have increased dramatically in-line with revenues, from 15K to 31K over this period.
Vegetables and Snack Foods represent the most profitable product lines.
The results experienced by Walton Supermarkets are broadly in-line with their overall strategy of
Increasing revenues
Controlling profit margin and product mix within sub 30% profit level
Decreasing costs by driving lower margins
Increasing cross sell opportunities via the loyalty program
The Uncle Sams loyalty program has had some good success with over 3699 members joining the
program in 2014, up from 1910 members the previous year. The Discount and Busy categories,
represent the most penetrated segments of the loyalty program, yet a high percentage of customers
remain non-committed to the overall program.
The Walton Supermarket board is seeking double digit revenue growth over the coming financial
year 2015. To support this growth initiative, it has been identified via the performance dashboards
that additional investment in the following areas are required:
conversion of more customers to the loyalty program in particular targeting the busy and
discount category.
targeting families with 3+ or more children which will drive unit sales and increase revenues
targeting new cities with 3+ children which have similar demographics to the top 5
cities/states which Walton currently services (Salem, Tacoma, Los Angeles, Seattle and
Portland). Focusing on the 10-30K and 30-50K income bracket.
running marketing promotions to increase unit sales beyond 4+ per household, leading to
increased revenues.
The remainder of this report discusses the reasoning behind the development of the 5 executive
dashboards as well as providing recommendations to the CEO to increase sales, in particular
leveraging the Uncle Sams loyalty program.
3|Page Walton Supermarket Executive Dashboard Review and
and 2015 Sales Growth Recommendations Report
Executive Insight – What questions are senior management interested in
addressing?
The feedback from the ELT management team (CEO, Sales Director, Marketing Director, Finance
Director, Logistics Director) identified a number of core questions which needed to be addressed:
What is the breakdown of sales revenue (quarterly, yearly and geographical coverage)
How is the business performing year to date from a sales and profit standpoint when
compared to previous financial periods?
Ability to identify product revenue breakdown by customer segment?
How does sales for each product category compared with overall sales?
Where are the best and worst performing cities?
What are the best and worst selling products from a revenue and margin standpoint?
Ability to segment product sales by region, customer type and profitability?
What influence does the Uncle Sams loyalty program have on sales by customer segment?
Does Walton Supermarket have the right product mix? Which products are moving?
Ability to target customers based on key demographical profile?
In addressing these key questions, the strategic roadmap dashboard can be broken down into four
key areas:- Sales, Profitability, Product and Customer Loyalty. As the needs of the audience (Few
Page 113) are focused on providing specific KPI business details. The dashboards are designed to
provide broad and different levels of visibility across these four areas for each of the executive
functional heads.
Some of the design around the executive dashboard incorporates the following thinking:
Information is organised in a logical and sequential manner
Condensed, primarily in the form of summaries (Few 2013 Page 94)
Dashboard reports to fit on one screen (computer desktop)
Ensure consistency in the alignment of the charts
Minimal use of words or non-essential graphics to ensure focus on the actual dashboard
results
Provide appropriate depth of information based on management level
With this in mind, the table below defines the respective high level dashboards approach broken
down across four key business areas (Sales, Profitability, Costs and Customer Loyalty) the target
audience, the data presented and the visualization option chosen.
Dashboard
Report
What?
Information
What
High Level KPI
Dashboard
Sales
Key KPI from
each area
Sales Revenue
Details
What
Purpose
Why these were chosen
Why those attributes are
present
Key Audience
Who are key executives, the reports
are addressing? The data fields and
why is the data laid out in the
fashion proposed
Sales revenue by
Month
Quarter
Track sales performance
over a period of time
Provide
granular
All ETL Members. Provide different
levels of data granularity through
the combination of bar chart
4|Page Walton Supermarket Executive Dashboard Review and
and 2015 Sales Growth Recommendations Report
Year
State
Region
Cities
Sales ytd
Sales last year ytd
Profit ytd
Profit last year ytd
geographical
view
sales over time
Provide YonY comparison
focusing on revenue and
profit margin
CEO, Finance Director, Sales and
Marketing Director. Trending report
generated via a bar chart to provide
comparisons from year to year
Sales by customer
– By month
– Quarter
– Year
– State
– Region
– City
– Number of Children
– By Income
– Portion
Understand
buying
behaviour based on
customer demographics.
Provide sales over a
timeframe, geographical
coverage
and
dependents
All ELT members except CEO.
Provide detailed level of information
(ie cities) using a combination of bar
charts and stacked
column
diagrams. This enables the slicing
and dicing of the data for different
data views.
Identify best and worst
selling products
Sales trends
– Top 5 sales regions
– Bottom 5 sales regions
– Sales Trending by Day
Identify the best and
worst
performing
regions.
Profitability
Product
and
Region
Profitability
Profitability
– By region
– By product
– By year
– By month
– By Quarter
– By Customer Segment
Highlight
the
profit
margins
based
on
product, FY periods and
location
All ELT Members. Dependent upon
the level of detail required, either a
tree graph or bar chart to illustrate
the split in sales across 43 products
category or product department. Pie
Chart was reviewed yet not selected
due to the volume of products and
the inability to understand the
best/worst selling products.
Marketing Director, Sales Director.
Providing day to day trending
information to capture sales daily
run rate via a column chart.
CEO, Sales Director, Finance
Director,
Marketing
Director.
Providing trending information using
bar charts to show movement in
profitability and margin levels.
Product
Product sales
Product Sales
– By Customer Type
– By region
– By Volume
– By profitability
– By Unit Cost
– By Unit Sales
– By product category
Provide different revenue
views
based
on
geographical
location,
volume and margin
Customer Loyalty
By region
By segment
Income Level
Number of Children
By product category
Outline the best/worst
profitable customers by
location and customer
loyalty segments
Year to date
sales and profit
Sales
Customer
By
Sales for each
product category
as a portion of
total sales
Customer
Loyalty
of
(Trending) and stacked bar charts.
Finance Director, Sales Director,
Marketing
Director,
Logistics
Director. Data is presented at either
the product department or category
level due to the volume of
information and audience. A
combination of (tree map or heat
map) and bar chart utilised to show
the
interrelationship
between
different variables.
All ELT Members. Understand
purchasing decisions based on
loyalty level utilising tree chart and
bar chart. Due to the volume of
information heat map was used to
provide a quick snapshot of product
vs loyalty level purchases.
5|Page Walton Supermarket Executive Dashboard Review and
and 2015 Sales Growth Recommendations Report
Assumptions
In preparing the dataset for analysis, a number of assumptions where made
Financial Year is based on a calendar year
Product Margins have been applied at the Product Department Level (ranging from 6% for
Carousel to 40% for health and Hygiene products) details located in the data spreadsheet
6 Loyalty Groups have been defined based on a combination of income levels, product
selection, unit pricing and product margin
Not all Walton customers are part of the Loyalty program
Products follow a hierarchical model -> Product Family, Product Department, Product
Category
Country, State and City make up the geographical hierarchical data model
Shelf Product information and future trending is excluded from the report, due to lack of
data from the original data source
6|Page Walton Supermarket Executive Dashboard Review and
and 2015 Sales Growth Recommendations Report
Performance Dashboards
CEO Dashboard
The purpose of this dashboard is to provide a broad view of the performance of the business across
the four business areas.
The dashboard contains an executive summary, highlighting the top level financial results.
CEO Dashboard
Diagram One (CEO Dashboard)
7|Page Walton Supermarket Executive Dashboard Review and
and 2015 Sales Growth Recommendations Report
The structure of the CEO dashboard is broken down into four sections to support the business
strategies. High level details of revenue, profit, costs and unit shipment are listed at the top right to
give running commentary on the top line performance figures.
In the case of the CEO, the information represented is at a macro level (ie country level instead of
city level) to provide broad snapshots across the business.
The first row of charts provide revenue details from a calendar trending, country and product family
perspective. The bar chart (diagram two) at a the country level was deemed a better view than the
geographical map view as it provided the necessary country sales information in a straight forward
way. The map view looked visually interesting, yet difficult to read within the limited space.
Diagram Two (Different CEO Country Revenue Views)
The bar chart to the far right (diagram three) covering revenue, profit and costs by product family,
are kept at a high level (instead of product category), to provide a broad snapshot of the breakdown
in revenues.
Diagram Three (Product Family View CEO Dashboard)
Part of the thinking with utilising the bar chart design was to provide a consistent view of
information as the CEO slices and dices the information. Having a mix of chart views (stacked bar
charts, column bar charts) for country and product family views would slow down the interpretation
of the results.
The final row covers the loyalty program from a calendar adoption, country and product family
perspective. The use of a combination of stacked and bar charts was utilised to provide consistency
in the data views to help with the speedy interpretation of the results.
8|Page Walton Supermarket Executive Dashboard Review and
and 2015 Sales Growth Recommendations Report
Looking at the results, a number of interesting observations are uncovered.
2014 represents a standout year with revenues of 121K. This represents a 101% increase
over the previous financial year of 60K and 26% margin. (diagram four)
Diagram four (Year on Year Snapshot of Results)
Revenues over the quarters was consistent (averaging 30K per quarter), with Q3 2014 being
the largest quarter recorded (33K)
Food category is the highest revenue earner (132K and a profit of 32K)
The US market makes up 45K revenue and 11K profit as part of the food category group (FY
2014)
A large number of Non members have not registered for the Uncle Sams loyalty group. Yet
steady adoption has been achieved year on year. 1910 Members to 3699 members over
2013/2014 period.
The busy and the discount loyalty groups represent the key segments which have adopted
the program.
Supporting the top line revenue views, the majority of the loyalty customers sit within the
US and the food family is the most popular group.
Drilling down on the busy loyalty group, it is noted that the bulk of the Busy revenues is
derived mainly from the US market (10K), yet the Mexico market (7.7K) over 2014 has
increased dramatically (diagram five).
9|Page Walton Supermarket Executive Dashboard Review and
and 2015 Sales Growth Recommendations Report
Diagram five (Loyalty Loyal Busy vs Country Revenue)
Sales Director Dashboard
10 | P a g e W a l t o n S u p e r m a r k e t E x e c u t i v e D a s h b o a r d R e v i e w a n d
and 2015 Sales Growth Recommendations Report
Diagram Six (Sales Director Dashboard)
The structure of the sales director dashboard (diagram six) follows a similar approach to the CEO
dashboard. The high level performance metrics are listed at the top combined with the four business
sections. The details of this report includes some micro level information such as revenue by city and
product category. The intent with this sales dashboard is to provide more granular detail to track
sales performance on a daily basis. This is reflected in the column report (diagram seven – far right)
showing sales performance on a day to day basis. Supporting this view is the bar chart (second row),
showing units sold by city to establish the highest city by revenue and products.
11 | P a g e W a l t o n S u p e r m a r k e t E x e c u t i v e D a s h b o a r d R e v i e w a n d
and 2015 Sales Growth Recommendations Report
Diagram Seven (Bar Chart Showing Units Sold)
Bar Charts are utilised in the revenue, profit and cost reports (by city and product category) as it
provides a base line view of the information in a concise manner. Alternative views involving
revenue by city were investigated. Visually these views looked interesting yet introduced a level of
complexity in the interpretation of the results (see diagram eight). In this example, it is difficult to
quickly determine if Salem or Tacoma is the largest city and if Los Angeles, Seattle and Portland are
achieving the same level of revenues.
Diagram Eight (revenue by city tree chart)
From a sales perspective, this dashboard reflects the following observations
4/10 top cities are US based (Salem, Tacoma, Los Angeles and Seattle). Hidalgo (Mexico)
with revenues of 11.3K is the highest revenue city achieved in this country (achieved over
2014). Revenues per city range from $500 to 18K over this financial periods. The bottom five
cities are Guadlajara $ 523, San Francisco $ 774, Bellingham $ 992, Mexico City $ 2.4K and
Walla Walla $ 1.1K
The highest selling day was 29/4/2014 resulting in $ 574 of revenues with Merida achieving
$80 as the highest selling city.
Produce 27K, Snack Foods 21K, Household 19K, and Frozen 17.3K are the highest selling
products by revenue, commanding 21% of total revenues.
The 10-30K income bracket group represents the second highest contribution group to
revenues with 39K in revenues (diagram nine)
12 | P a g e W a l t o n S u p e r m a r k e t E x e c u t i v e D a s h b o a r d R e v i e w a n d
and 2015 Sales Growth Recommendations Report
Diagram Nine (Relationship between 10-30K income bracket and loyalty membership)
This reflects across the loyalty group with budget and battlers making up the key
membership. Within this income group, household, snack foods, produce and frozen foods
represent the key selling products.
60K in revenues is associated with the 30-50K income bracket with the shoppers operating
outside the Uncle Sams loyalty program (43K). This represents a potential sales opportunity
for increased revenues over FY 2015.
Marketing Director Dashboard
The dashboard view for the marketing director is focused on providing more demographical data to
support the overall marketing strategy. This dashboard follows similar lines to the CEO and Sales
Director dashboard in providing base line revenue, profit and cost trending information over time.
This report differs slightly in the level of detail around the customer loyalty and shopper
demographical details.
Diagram Ten (Marketing Director Dashboard)
13 | P a g e W a l t o n S u p e r m a r k e t E x e c u t i v e D a s h b o a r d R e v i e w a n d
and 2015 Sales Growth Recommendations Report
Drilling down on the detail, 16K of the vegetables is sold within the US, with Tacoma (2.1K), San
Diego (1.6K) Salem (2.3K) being the key cities. On the snack food side, a similar story unfolds as
Tacoma (2.2K), Salem (2.3K) and Portland (1.5K). Walton Supermarket is managing 45 product
categories making the utilisation of a bar c…
Purchase answer to see full
attachment
