Management Report on Marketing Dashboard

Executive Summary

This project entails building a marketing dashboard for Company X to monitor the significant metrics that will enable informed decision-making at the company’s board. Company X is an e-commerce business that is interested in using product analytics to boost its marketing performance. The first step involved collecting data on relevant variables, including date, product name, product category, buying price, selling price, payment mode, sales type, and quantity.

Next, we performed data analysis by generating pivot tables to help perform calculations for the KPIs and visualize them. Some of the relevant formulas used in the calculations include SUM, RANK, OFFSET, and VLOOKUP. We used graphical features like bar graphs, slicers, area charts, and pie charts to visualize the significant metrics on the dashboard.

The dashboard works as per the plans. It enables users to choose the features they want to analyze to track the key metrics, such as total revenue, profitability, top product, top category, payment modes, and sales type. By using slicers, users can choose the month or year they are interested in analyzing to monitor the metrics.

Implementing this dashboard will aid the company in making efficient decisions concerning the significant performance metrics that affect the overall growth. The major limitation of this dashboard is the use of MS Excel formulas only, which makes the tool less dynamic with large datasets. We suggest using macros or other visualization tools to make the dashboard more interactive in the future.

Introduction

ABC Consultants was contracted by Company X to build an integrated sales dashboard that computes the essential and relevant metrics for informing strategic decision-making at the company board. This report provides justifications for the sales dashboard design and reasons why the chosen metrics suit the business setting of Company X.

Company X is a retail business positioned in the e-commerce industry, targeting consumers from the entire United Kingdom region. This firm deals with various products, mainly clothes, books, consumer electronics, computing resources, food stuff, household items, and office equipment.

The project aimed to create a prototype of a dashboard using sample sales data to advise the company on how it can leverage the selected metrics for informed strategic decision-making. We will dig deeper into explaining why the chosen metrics are appropriate for the firm and how they can be used to increase sales revenue and the overall competitive advantage in the ecommerce industry.

An Overview of the Dashboard Components

The Microsoft Excel dashboard provides Company X with a bird’s-eye view of the ecommerce sales KPIs in a single place. It offers standard reporting of the relevant metrics for monitoring business success. Precisely, the dashboard uses the following components to report on the significant metrics for the business:

1. Bar graphs. Bar graphs use rectangular bars to display the comparison of KPIs and data across categories (Orlovskyi and Kopp, 2020). The dashboard uses bar graphs to display the monthly sales revenue and revenues for each product. With this graphical component, the company can quickly generate insights about the best-performing product or months in terms of sales revenue.
2. Pie charts. Pie charts are effective graphical tools in displaying the share of products sold and how the revenue is distributed across the categories. The dashboard uses pie charts to represent the numerical proportions and percentages of revenue for sales-type and category in a visually engaging manner. This feature allows the dashboard users to quickly develop insights into the best-selling categories by comparing the slices.
3. Area chart. An area chart combines a line chart and color to show trends of numerical data points over time. The dashboard includes an area chart to display a series of revenue data for all the days in the selected month. With this feature, the company can monitor the fluctuations in daily sales revenues over the entire month. This component will let the firm analyze the best and worst performing days in terms of sales revenue to make appropriate strategic decisions.
4. Slicers. Slicers are interactive controls that help in filtering data to select and represent a particular field. These features offer vast benefits, including providing quick filtering and showing the item currently displayed on the dashboard (Jelen, 2021). The dashboard uses slicers for selecting year, month, sales type, and payment mode to display the corresponding sales metrics. This tool will enable the company to select the categories they want to generate revenue efficiently.

Description of Metrics and their Uses

Sales are the pillars of revenue generation for any business. As a result, monitoring sales metrics enables a company to know what is functioning and which area needs adjustment to increase revenue and better contribute to a firm’s growth. Sales metrics refer to the data points businesses use to gauge sales performance and determine whether they are progressing toward the targeted goals (Mintz, 2023). Tracking these metrics enables a business to make data-informed decisions on whether its sales activities are paying off. Any metric that proves efficient in contributing to the firm’s success becomes a key performance indicator (KPI) (Sultan, 2022).

The following are the relevant sales metrics the created dashboard will let Company X track its overall business performance:

1. Total sales. Arguably, this is the most important financial metric for measuring the company’s revenue generation through the sale of products and services (Diana, 2020). This KPI measures the total amount of sales recorded by the company based on the selected month, sales type, and payment mode. By monitoring its gross sales, the company can use this metric to evaluate its financial health and business success. Additionally, this sales metric can be leveraged in measuring the efficiency of the marketing team. The dashboard shows the monetary value of the total sales to allow users to view the sum of revenue generated by the company.
2. Total profits. This financial metric indicates the net profit a firm gets from the total revenue after accounting for all expenses. Businesses use the net profits metric to measure their overall financial health by knowing their net income (Jayathilaka, 2020). Monitoring the net profits is critical for e-commerce analytics because it helps measure the company’s ability to earn from product sales. The dashboard displays the net profit value as a monetary figure.
3. Gross margin. This is a key financial performance indicator that tells how much profit the business is making as a percentage of its total revenue. Percentage profit is calculated as a ratio of the net profit to the cost of goods sold (COGS). This financial performance metric is crucial for the business to determine its production efficiency and compare its performance to industry peers. The gross margin metric is displayed on the dashboard as a percentage.
4. Top product. This KPI indicates the top-selling product based on the monetization factor. Product metrics are integral in gaining insights into how customers interact with a company’s products and services. This product metric gives the company quantifiable facts about the types of items that resonate well with customers.
5. Payment modes. The payment metric shows which payment mode generates the highest revenue. There are two payment modes for Company X, which are visually displayed on the dashboard using a pie chart. The company can use payment metrics to evaluate the performance of its payment systems by graphing the distribution of payments received from different payment methods used by customers.
6. Sales type. This metric compares the effectiveness of sales types based on revenue generation. The metric informs the company about the percentage of revenue by the sales types, including direct, online, and wholesale. It is visualized using a pie chart on the dashboard to monitor the proportion of sales revenue for the three types of selling methods. With this metric, the company can make decisions to improve the efficiency of product sales by targeting direct, online, or wholesale customers.
7. Category. This metric visually illustrates how product categories compare based on sales revenue. It’s created using a Treemap hierarchy chart. Categorizing the products is one of the practical ways the company can graphically assess its product acquisitions in the market. The company can use this metric to make strategic decisions to drive its sales volume for the most profitable items.

Justification of the Choice of Metrics

Marketing metrics function as scorecards for monitoring the performance of a business and gauging its ability to meet strategic goals. We selected a number of financial and product metrics to assess the marketing performance of Company X in the ecommerce industry. To justify the selection of the metrics, we have separated them into three groups for evaluating the business performance, efficiency, and overall growth.

The first practice of measuring the company’s marketing performance is to examine the amount of revenue generated from product sales (Baehre et al., 2022). By using suitable metrics, the business can link its sales to the marketing strategies to improve overall growth. The relevant metrics for measuring revenue and sales used in this report include total sales, monthly sales, daily sales, and profit margins.

The total sales profit margin metric measures the company’s revenue generation and profitability at any time scale, including daily, monthly, and yearly. This metric is efficient in determining the net sales of products or channels. With the help of the created dashboard, the company can make strategic decisions on boosting sales revenue by quickly identifying the least or highest-selling products. Users can select the year or months for which they want to analyze the revenues.

References

Baehre, S., O’Dwyer, M., O’Malley, L., & Lee, N. (2022). The use of Net Promoter Score (NPS) to predict sales growth: insights from an empirical investigation. Journal of the Academy of Marketing Science, 50(1), 67-84.

Bukoye, J. A., Muritala, T. A., Hadiza, S., Nwoye, M. I., & Ogedengbe, F. A. (2023). Impact of Penetration Strategy on the Performance of Manufacturing Industry in North West Nigeria. Journal of Human Resource and Sustainability Studies, 11(3), 472-493.

Diana, H. I. (2020). Indicators for measuring the financial performance of economic entities. Economic Sciences Series, 20(1), 945-953.

Jayathilaka, A. K. (2020). Operating profit and net profit: measurements of profitability. Open Access Library Journal, 7(12), 1-11.

Jelen, B. (2021). Using a slicer on a dashboard. Strategic Finance, 103(1), 60-61.

Mintz, O. (2023). Metrics for Marketing Decisions: Drivers and Implications for Performance. NIM Marketing Intelligence Review, 15(1), 18-23.

Orlovskyi, D. and Kopp, A. (2020). A business intelligence dashboard design approach to improve data analytics and decision-making. International Technology and Interactions, 1(1), 48-59.

Sultan, E. W. A. M. (2022). Key performance indicators (KPIS), key result indicator (KRIS) and objectives and key results (OKRS). Arabian Journal of Business and Management Review (Kuwait Chapter), 11(4), 147-157.