Introducing sales dashboard excel template. Its purpose is recording the sales. Small to medium company can use this template. It can be used for personal use too. The sales dashboard excel is perfect for any company or business industry.
There are 3 menus in sales dashboard excel template. The menus can be accessed by clicking the sheet or the menu buttons on top of each tables:
Menu for recording the sales transaction. The fields are:
- Sales Date
- Sales Time
- Transaction Number: Sales ID
- SKU/Product Number: Product ID. Drop down field from Inventory menu.
- Description: the items name. It’s automatically generated after you select a value in SKU/Product Number field.
- Tax: tax percentage for the item in a transaction sales.
- Sales tax: calculated tax amount in nominal value. The field is automatically calculated.
- Total: total sales items. Like sales tax, it is auto-calculated.
Menu for displaying the summary of sales transaction in previous menu for sales analysis dashboard excel. This menu consists of pivot table: SKU, Description, Date, Sales Amount, Sales Tax, and Total as well Grand Total row.
This menu is for displaying only, so it is not advised to change any records.
Menu for recording the items for Sales Data menu. There are only 2 fields: SKU number and Description fields.
Because sales dashboard excel template is the dashboard for sales performance, it not fully developed for inventory management. The main purpose of Inventory is only for determining which inventory for sales rather than recording inventory amount.
HOW TO USE
To use the sales dashboard excel template, simply enter the provided blank fields.
First on Inventory menu, enter the SKU ID and item name in description.
On Sales Data menu, Enter the transaction Date, Time and Transaction Number. Then, select SKU number from drop down menu. Enter the Sales amount and tax.
If in the same Transaction Number the customer buy 2 or more different items, simply add new record(s) with same Date, Time and Transaction Number.
The Sales Amount is supposed to be final, after been calculated with quantity, discount, or shipping price.
In Sales Report, the pivot table display the sales transaction summary. If the value is not changed, select the pivot table and right click > Refresh. Or go to Data tab > Refresh All.
If there are two or more transactions in one items, the next row will be displayed without SKU ID and Description.
The Sales Report can be given to management or analyzed for sales kpi dashboard excel.
The sales dashboard excel template is easy to modify too. The template only use simple math formula (summary, multiplication, addition, etc), Data Validation, and Pivot Table function.
Even through it is not for Inventory, sometimes it need to enter the sales quantity. To add quantity table, simply insert new column named Quantity. Enter the number amount. Then, on Sales Report menu, select the field in Pivot Table. On right, the Pivot Table toolbar will appear. Below the Search box, look at the Quantity field and tick the checkbox. The quantity field will be displayed on the pivot table.
If you want to add price per unit, on Inventory table add the column for Unit Price. On Sales Data menu, add the said column too. On the column field, enter this formula for automatic generated:
=IFERROR(IF(ISNA(VLOOKUP([@[SKU/PRODUCT NUMBER]],tblInventory,3,0)),””,VLOOKUP([@[SKU/PRODUCT NUMBER]],tblInventory,3,0)),”No description found”)
Then, update the Sales Amount formula. You can follow the quantity method to add to pivot table.
You can turn the sales dashboard excel template into Purchase template simply by changed the name or adjusting some fields.
The excel sales dashboard templates free and easy to use, modify, or download. The main usage is for recording and reporting the Sales template. The Sales Report menu is set on A4 paper, landscape orientation.
You can download the sales dashboard excel template after you click one of this sharing buttons: