Dynamic Sales Dashboard using Microsoft Excel

 

Dynamic Sales Dashboard using Microsoft Excel Template:

Description About Template:

Sales dashboards are one of the most popular excel tool for sales data visualization. Dynamic Sales Dashboard offers a complete autonomous data visualization of your business. Monitoring and tracking the sales is very crucial and may impact on your monthly/ quarterly sales targeted. The Dynamic Sales Dashboard offered by the Excel Solution provides the dynamic and autonomies visualization of your sales data.

Features:

First of all, you need to follow the below steps to get the Dashboard for your Sales Data

·         Paste or enter your data as per the prescribed format in “Sales Entry” menu

·         If you are pasting the existing data, please ensure the data shall be pasted under right Table heading and only values without format shall be pasted to maintain the template format.

·         You can change the Table Heads as per your requirement

·         You can change the Chart Name as per your requirement

Sales Entry:

In the Sales Entry menu bar, paste or insert the data as per the format of the excel template. All the data as per the table is mandatory.

You can modify the table heading as per the requirement. Total Cost is the cost price of all Quantity. Total Sales is the sales price of all quantity.

Insert the sales data in “Sales Entry” menu bar in the excel template. Later than, by clicking on “Refresh Chart”, all the charts and graphs of the excel template will change automatically.

Dashboard:

Dashboard is fully dynamic and provides visual presentation of Sales Data. The Dashboard summaries the various details including, total sales, profit/ loss, sales distribution based on their emergency, category, customer type, etc. Certain navigation buttons are also available to get the Dashboard for a specific timeline.

Menu Bar:

Interactive Menu bar to redirect between multiple pages is provided at the top of the Tool. There are five (5) buttons Each button navigates to the respective sheet.

The selected sheet is highlighted with the different colour (Dashboard is selected in above pic). Refresh Chart buttons will update the Chart when Sales Entry is modified or changed.

At the right side corner, the Contact details and website address can be accessed by clicking on the Excel Solution button.

Dashboard Navigation:

Navigation Panel used to select a specific range of data from the available range. By default, all data range is selected. You can select a single or multiple year or months from available data range.

Total Sales and Profit:

This table provides summary of your total sales value and profit gained. The Profit is provided in terms of amount as well as in percentage. For the easiness of tracking, the dynamic color coding is defined. Green color is defined for to indicate the revenue profit and Red color is defined to indicate the revenue loss.

Sales Summary:

Column chart indicates the summary of gross sales. You can select any specific month or year from the Dashboard Navigation panel and to see the gross sales in that particular month or year.

Order Priority:

The orders which are fulfilled based on its priority, is also summarized through a bar chart.

Product Category Trend:

For the quick understanding the product trend based on all past orders/ sales, the Trend chart is prepared.

Sales Distribution:

Based on the order delivery in the specific region, the Pie chart is prepared to provide visualization of Sales distribution in all past sales.

Customer Type:

For the targeted customer in history of sales, the radar chart is provided.

Compare Sales:

Comparing your products in terms of total sales, profit may useful to take future business decision. With this comparison chart, you can compare any two products for its Total Sales, Quantity, Profit, Profit%.

You can select from the drop down menu (right side top corner) to see the comparison statement. Also from the side navigation, you can select any product see its performance graph.

This Comparison graph is also fully dynamic and update with respect to change in the sales entry.

Charges:

• Per User Charge: $ 9.99 FREE

We are doing this work for non-profit charity purpose. If you feel this tool is useful to you and worth, you can make any amount as a payment through below payment links. Total Payment we receive, will be used for good cause, charity works for orphans, street dogs, cow houses and needy people.

Payment Method:

Download:

Download the Dynamic Sales Dashboard by below Download Button. User manual is also provided along with Excel template for easy operation.

Other Relevant Posts:

• Simple Project Management Dashboard using Microsoft excel.
• How to use SPARKLINE function in Google Sheets?
• How to use the IF function in Microsoft excel?
• How to use the DAYS function in Microsoft excel?
• How to use the COUNT function in Microsoft excel?
• How to use the SUM function in Microsoft excel?
• Calculator using VBA in Microsoft Excel.

How to use SPARKLINE function in Google Sheets?

How to use SPARKLINE function in Google Sheets?

ABOUT:

This function used to create miniature charts contained within a single cell in Google Sheets. This function returns a chart based on the values provided from the reference data set.

Different type of charts like “Line”, “Bar”, “Column”, “Winloss” can be created by using the same function based on the requirement.

PURPOSE OF THE FUNCTION:

To create miniature charts contained within a single cell

OUTPUT VALUE:

A miniature chart

SYNTAX:

=SPARKLINE(data, [options])

=SPARKLINE(data, {“charttype”, “type of chart”})

ARGUMENTS:

data: Required. The range or array containing the data to plot

options: Optional. A range or array of optional setting and associated values used to  customize the chart

The “Charttype” option defines the type of chart to plot in cell, which include:

• "line" for line graphs (this is default)

• “bar” for stacked bar charts

• “column” for a column charts

• “winloss” for a special type of column chart that plots two (2) possible outputs that is positive and negative.

Further this charts also can be formatted by giving additional setting to the tool.

For “line” charts:

• "xmin" sets the minimum value along the horizontal axis.
• "xmax" sets the maximum value along the horizontal axis.
• "ymin" sets the minimum value along the vertical axis.
• "ymax" sets the maximum value along the vertical axis.
• "color" sets the color of the line.
• "empty" sets how to treat empty cells. Possible corresponding values include: "zero" or "ignore".
• "nan" sets how to treat cells with non-numeric data. Options are: "convert" and "ignore".
• "rtl" determines whether or not the chart is rendered right to left. Options are true or false.
• "linewidth" determines how thick the line will be in the chart. A higher number means a thicker line.

For “Column” and “Winloss” charts:

• "color" sets the color of chart columns.
• "lowcolor" sets the color for the lowest value in the chart
• "highcolor" sets the color for the higest value in the chart
• "firstcolor" sets the color of the first column
• "lastcolor" sets the color of the last column
• "negcolor" sets the color of all negative columns
• "empty" sets how to treat empty cells. Possible corresponding values include: "zero" or "ignore".
• "nan" sets how to treat cells with non-numeric data. Options are: "convert" and "ignore".
• "axis" decides if an axis needs to be drawn (true/false)
• "axiscolor" sets the color of the axis (if applicable)
• "ymin" sets the custom minimum data value that should be used for scaling the height of columns (not applicable for win/loss)
• "ymax" sets the custom maximum data value that should be used for scaling the height of columns (not applicable for win/loss)
• "rtl" determines whether or not the chart is rendered right to left. Options are true or false.

For “Bar” charts:

Bar type chart is used when two different values are available to plot into chart.

• "max" sets the maximum value along the horizontal axis.
• "color1" sets the first color used for bars in the chart.
• "color2" sets the second color used for bars in the chart.
• "empty" sets how to treat empty cells. Possible corresponding values include: "zero" or "ignore".
• "nan" sets how to treat cells with non-numeric data. Options are: "convert" and "ignore".
• "rtl" determines whether or not the chart is rendered right to left. Options are true or false.

EXAMPLE 1:

=SPARKLINE(B2:B13)

By default the Line chart is selected by syntax “SPARKLINE”. Hence, the above formula will take the data from the given range B2:B13 and returns a line type chart.

EXAMPLE 2:

=SPARKLINE(B2:B13,{"charttype","column"})

In this function we have specified the type of chart in arguments. We required column chart for the data set reference in B2:B13. Accordingly, the column chart will be generated.

EXAMPLE 3:

=SPARKLINE(C2:C13,{"charttype","winloss"})

In this function we required winloss type chart for the data set referenced in B2:B13. Accordingly, we have selected “winloss” chart type in arguments.

OTHER RELEVANT POSTs:

• Simple Project Management Dashboard using Microsoft excel
• How to use the IF function in Microsoft excel?
• How to use the DAYS function in Microsoft excel?
• How to use the COUNT function in Microsoft excel?
• How to use the SUM function in Microsoft excel?
• Calculator using VBA in Microsoft Excel

How to use the DAYS function in Microsoft excel?

 

ABOUT FUNCTION:

DAYS function in Microsoft Excel returns the days count between two reference dates.

PURPOSE:

To get a duration or days between two dates.

OUTPUT OF THE FUNCTION:

Total duration or number of days between two dates

SYNTEX:

=DAYS(end_date, start_date)

ARGUMENTS:

●     end_date: required. The end date

●     start_date: required. The start date

NOTES:

Excel stores a date as sequential serial numbers so that it can be used for calculation. By default, Microsoft Excel considers Jan 01, 1900 as a start date and its serial number as 1. Therefore, for the date Jan 01, 1901 the serial number will be 367 because it is 366 days after Jan 01,1900.

By default, first date in Excel: Jan 01,1900

If both date arguments are numbers, DAYS will perform End date - Start date to calculate the number of days in between start date and end date.

If any arguments in the DAYS function is text value, then function will return an integer date instead of time component

If date arguments are numeric values that fall outside the range of valid dates, DAYS returns the #NUM! error value.

If date arguments are strings that cannot be analyzed as valid dates, DAYS returns the #VALUE! error value.

EXAMPLE:

Below function in E7 cell returns the number of days between two dates mentioned in C7 and D7. It will return the duration or day count between start and end date is 1242 days.

Function:

=DAYS(D7, C7)

Description:

Days function will perform calculation to count days between END DATE - START DATE.

Output:

Returns 1242 days is the days count between two dates.

 

Excel also counts the days between two dates when arguments are Date instead of reference subject to valid format recognized by Microsoft Excel.

Function:

=DAYS(“01-01-2020”, “01-01-2019”)

Description:

When you are using direct dates as an argument in function, you need to enclose these values in double quotation marks.

Output:

Returns 365 days

OTHER RELEVANT POSTs:

●     How to use the IF function in Microsoft excel?

●     How to use the COUNT function in Microsoft excel?

●     How to use the SUM function in Microsoft excel?

●     Simple Project Management Dashboard using Microsoft excel

●     Calculator using VBA in Microsoft Excel

 

How to use IF function in Microsoft excel?

 

About Function:

The IF function perform a logical test and returns different value for TRUE result and FALSE result.

The example is shown in above picture with formula to get a quick performance analysis of student. In that table the student who have received 50% above marks are marked as “Pass” and who have received less than 50% are “Fail”.

More than one condition can also be tested in IF function by using nesting IF functions. The IF function can be combined with logical functions like AND and OR to extend the logical test.

Purpose of the Function:

To perform specific condition

Output Value:

The value for TRUE or FALSE condition

Syntax:

=IF(logical_test, [value_if_true], [value_if_false])

Arguments:

logical_test – value or expression that can be evaluated.

value_if_true – [Optional] the value to return if logical test is TRUE

value_if_false - [Optional] the value to return if logical test is FALSE

Example:

The IF function will perform the logical test on a specific cell and react with the logical test is TRUE or FALSE. The IF function will return the respective value for TRUE or FALSE result.

In our example, we have table of marks of different student. We have performed IF function to get a quick performance result of students. The student who have more than 50% marks are “Pass” and who have less than 50% marks are “Fail”

 

=IF(D5>50, “Pass”, “Fail”)

returns Pass, if mark is more than 50 otherwise returns Fail

More example of possible logical test in the same example is also shown in below table:

=IF(D5>=50, “Pass”, “Fail”)

=IF(D5<=50, “Fail”, “Pass”)

=IF(D5<50, “Fail”, “Pass”)

Other Relevant Posts:

• How to use COUNT function in Microsoft excel?
• How to use SUM function in Microsoft excel?
• Simple Project Management Dashboard using Microsoft excel
• Calculator using VBA in Microsoft Excel