Excel Solution

Dynamic Sales Dashboard using Microsoft Excel

2020-09-06T11:00:00.005+05:30

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.

How to use SPARKLINE function in Google Sheets?

2020-08-14T01:40:00.004+05:30

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.

How to use the DAYS function in Microsoft excel?

2020-08-04T09:12:00.004+05:30

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

How to use IF function in Microsoft excel?

2020-07-30T11:37:00.002+05:30

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”)

How to use SUM function in Microsoft excel?

2020-07-29T22:29:00.003+05:30

How to use SUM function in Microsoft excel?

About Function:

The Excel SUM function returns the sum of values supplied. These values can be numbers, cell references, ranges, arrays, and constants, in any combination. SUM can handle up to 255 individual arguments.

Purpose of the Function:

Add numbers together

Output Value:

The sum of values supplied

Syntax:

=SUM(number1, [number2], …)

Arguments:

number1 – first value.

number2 – [Optional] second value.

Example:

Range	=SUM(D7:D10)	returns 22
Reference	=SUM(D7,D9)	returns 14
Constant	=SUM(5,10,15)	returns 30

How to use COUNT function in Microsoft excel?

2020-07-29T21:53:00.004+05:30

How to use COUNT function in Microsoft excel?

About Function:

The Excel COUNT function use to count of cells that contain numbers.

The COUNT function returns the count of numeric values in the list of supplied arguments. Arguments can be individual items, cell references, or ranges up to a total of 255 arguments. All numbers including negative values, percentages, dates, fractions, and time are counted. Empty cells and text values are ignored.

Purpose of Function:

Count the number of cells

Output Value:

The count of cells that contains numeric value

Syntax:

=COUNT(value1, [value2], …)

Arguments:

value1 – an item, cell reference or range.

value2 – [Optional] an item, cell reference or range.

Example 1 - Range:

Function	Output
=COUNT(C7:C14)	returns 5

In the above example, COUNT function is set up to count the values in the range C7:C14. And the output of the function will return 5 since there are four numeric value in that specified range. Text value and blank cells are ignored by this function.

Example 2 - Constant:

Function	Output
=COUNT(1,2,3,”world”)	returns 3

In the above example, out of four value, three are numeric and one is text. Therefore, the output of this function will return 3.

Calculator

2020-05-25T00:10:00.005+05:30

Hello Dear Friend,

I have developed simple calculator in Excel using VBA. You can access it from here and try for your use. The link to download the tool is provided in the Point No. 5 Links

1. PICTORIAL OVERVIEW:

2. SYSTEM REQUIREMENTS:

This tool is workable in all system having MS office version MS 2010 and later. Preferable version is MS 2013 or 2016.

3. PREAMBLES:

This tool is developed in excel using VBA. To access the calculator from the worksheet, you need to follow certain steps.

Download the tool from the below link provided in the point no. 5 Link and follow the steps as briefed here.

Step 1: Click on “Developer” tab from ribbon

Step 2: Click on “Visual Basic”

Step 3: When the code view is open, select “Calculator” from right side window and press “F5” key from your key board or “run” button from menu bar

And that’s how, the Calculator is here for your Use.

4. CHARGES:

Charges per user: Free

5. LINK:

Click here to download Calculator: Download Calculator

Simple Project Management Dashboard using Microsoft Excel

2020-05-23T21:08:00.058+05:30

Simple Project Management Dashboard using Microsoft Excel

PREAMBLE:

In any project of whatsoever nature, task management is the vital activity to measure the key performance metrics of the project team to complete the project on or before the time.

In order to provide such a solution, our Project Management Dashboard Tool is the dedicated solution for tracking project, task management, visual presentation through interactive auto updated dashboard and Gantt chart.

Today purchasing project management software’s for all the organizations or small enterprises is not possible due to its huge cost, especially for those, this Project Management Tool is the best solution.

This Project Management Dashboard tool using Microsoft Excel is useful in almost all types of industry sectors Vide Sales & Marketing, Engineering, Finance, IT, Health care, Research & Development, Real Estate, Quality, Procurement, Supply & logistic, Construction companies, Manufacturer, Human Resource, and etc.

Aside this Project Management Tool is also useful for tracking all your personal activities in routine life as well.

You can download demo version of this Project Management Dashboard tool at the link provided in this post at "Download" section.

SYSTEM REQUIREMENTS:

The Project Management Dashboard tool is workable in all systems having MS office version MS 2010 and later. Preferable versions are MS 2013 or 2016.

Anyone who can operate the Microsoft excel at basic level, can operate this project management tool and modify as per their own requirement.

DASHBOARD:

Project Dashboards is the visual presentation of the project data provided by team members of the Project.

Project Dashboards give the overall idea about what are the total tasks associated with the project, how many tasks are completed, in progress or pending and the tasks which are targeted on certain time lines but not initiated or completed within desired time schedule.

Dashboard is integrated with the project data and will automatically update when the status of task or project is updated.

GANTT CHART:

Gantt chart is a great way to understand how much for each of the task associated with the projects are allotted. It will also visualize the project or task workflow.

In Gantt chart, you can change the weekly off or holidays as per your requirement.

In this case, using complicated software may waste a time and cost where this simple Gantt chart using Excel is the best and easy solution for this.

If you are:

Project Manager, you can have a high level view and real time data of the projects;

Team Leader, you can see a key metrics of the task allotted to your team members;

Finance Officer, you can perform management tracking;

Business Development, you can get the overall business intelligence;

TASK-MASTER:

Task-master is the key page of the Project Management Tool. Here all the tasks are listed with all minimum details including task name, task owner, other associated team members, task schedule and target completion and status of the project.

Basically this sheet is the database of the entire project which is bifurcated in all micro leveled tasks or activities related to the project.

In this sheet, you will be required to identify each task of the project along with its workflow and timeline.

Once you prepare the Database with all such information, you only need to update the %completion column only. Balance all status of the Dashboard, Gantt chart and Task Master sheet will be automatically changed.

Creating more powerful and accurate tracking of the project, micro leveled tasks shall be identified and listed in this sheet. More Accuracy in the database will give more accurate results.

CHARGES:

Charges per user: ~~$ 19.99~~ FREE

PAYMENT METHODS:

PAYMENT TOWARDS CHARITY:

The part of the amount which you spent to purchase this Project Management Tool will be utilized in the charity works for a good cause including help to poor and needy people, orphans, old-age home, cow-house, cow fodders, etc.

DOWNLOAD:

Demo version of the Project Management Dashboard Template is completely free and can downloaded from below button.

See Other useful information about Microsoft excel function and its working at Excel Solution

Excel Solution

Dynamic Sales Dashboard using Microsoft Excel

Dynamic Sales Dashboard using Microsoft Excel Template:

Description About Template:

Features:

Sales Entry:

Dashboard:

Menu Bar:

Dashboard Navigation:

Total Sales and Profit:

Sales Summary:

Order Priority:

Product Category Trend:

Sales Distribution:

Customer Type:

Compare Sales:

Charges:

Payment Method:

Download:

Other Relevant Posts:

How to use SPARKLINE function in Google Sheets?

How to use SPARKLINE function in Google Sheets?

ABOUT:

PURPOSE OF THE FUNCTION:

OUTPUT VALUE:

SYNTAX:

ARGUMENTS:

EXAMPLE 1:

EXAMPLE 2:

EXAMPLE 3:

OTHER RELEVANT POSTs:

How to use the DAYS function in Microsoft excel?

How to use the DAYS function in Microsoft excel?

ABOUT FUNCTION:

PURPOSE:

OUTPUT OF THE FUNCTION:

SYNTEX:

ARGUMENTS:

NOTES:

EXAMPLE:

Function:

Description:

Output:

Function:

Description:

Output:

OTHER RELEVANT POSTs:

How to use IF function in Microsoft excel?

How to use IF function in Microsoft excel?

About Function:

Purpose of the Function:

Output Value:

Syntax:

Arguments:

Example:

Other Relevant Posts:

How to use SUM function in Microsoft excel?

How to use SUM function in Microsoft excel?

About Function:

Purpose of the Function:

Output Value:

Syntax:

Arguments:

Example:

Other Relevant Posts:

How to use COUNT function in Microsoft excel?

How to use COUNT function in Microsoft excel?

About Function:

Purpose of Function:

Output Value:

Syntax:

Arguments:

Example 1 - Range:

Example 2 - Constant:

Other Relevant Posts:

Calculator

Simple Project Management Dashboard using Microsoft Excel

Simple Project Management Dashboard using Microsoft Excel

PREAMBLE:

SYSTEM REQUIREMENTS: