Business Reports using Microsoft Excel: An introductory exampleShashwat Mahaseth
Microsoft Excel is unarguably the best software tool for business. It is the standard tool that is used for business reporting and analysis at all the levels in an organization. Be it an Intern, on his first assignment at work or a seasoned CEO, everyone swears by the utility of Excel in making his or her task more manageable. What makes Excel the de-facto standard in the business world? The answer to this is its robustness and versatility. A lot can be written in praise of this wonderful tool by Microsoft. However, today we will focus on the business reporting capability of Excel.
Any business generates a significant amount of data, and with the advent of computer systems, it has become simpler than ever to record and store large amounts of data. However, the success of the business depends on the decisions that are taken after careful analysis of the data. This analysis is mandatorily carried out at every level within the organization. A sales manager analyzes the territory sales data; the factory manager needs to investigate the production data, supply chain manager needs to analyze the data of the vendors, and the CEO needs to examine the high-level data of each function to see if everything collectively is delivering on the organizational goals. Each of these roles requires Microsoft Excel to do their analysis in an easy and timely manner. Creation of several business reports is the crux of Excel usage.
Following are few points that illustrate why Excel is an Excellent tool for business reporting:
- Pivot Table tool in Excel helps to filter, summarize and analyze large amounts of data within few clicks
- A large set of inbuilt functions that can perform many complex data manipulations in a jiffy
- You can define Macros to custom define any particular operation on the data
- Advanced charting and visualization tools are available in Excel to get an intuitive sense of data
- Huge range of existing templates is available to make standard reports quickly
- A robust support community exists in case you need any help with Excel
The above list is not exhaustive, but it does give an idea why Excel is widely used for business reporting and analysis. In case you are not using Pivot table, do have a look at the primer here.
Now coming to the example of the business reporting format, let’s take a look at the following setting.
You are a newly appointed Sales Manager, and you need to make a tool that makes it easy for your team members to fetch data from loads of worksheets in the Excel workbook. While this job can be done manually by going to each of the worksheets and copying the data, it is time-consuming, and you want your team to do more productive things instead. For someone who knows Excel, the job of fetching data is fairly easy, and for someone who doesn’t know Excel, data fetching indeed is pretty nifty.
The data you (Sales Manager) need to work on is the sales data across years. The data spans 7 years (2011 to 2017), and each year’s sales data is put in a different worksheet. In the datasheets, the sales are recorded for 15 products, i.e., Product 1, Product 2 to Product 15 for each month from January to December.
Above is the screenshot of sales data of the year 2011 data, similar to this there are data 6 more datasheets which need to be searched.
Following are the number of tabs, i.e., Sales2011, Sales2012 to Sales2017. Query Sheet is where we will put the search query.
You need to make a Query Page that searches across the sales data spanning seven years (2011 to 2017) to find the revenue of any particular product for a specific month.
- Year of Sale
- Month of Sale
- Product Name
- Sales Revenue for that particular Product in the given month and year.
The below image shows how the desired output should look like:
Note that to obtain the output (Sales Amount) the Excel needs to access different worksheets depending on the year specified.
We will approach this problem in a stepwise manner.
Naming the data for each year in the worksheets
To enable the Excel to navigate across sheets we need to name the area that we need to navigate. This makes it easy to refer the cells from different worksheets easily and intuitively.
Name the revenue data
We need to select the revenue data and name it accordingly. The naming of the selected cells can be done using the “Define Name” within “Name Manager” in the formula Tab as shown below.
So we will define each revenue data set as follows:
Select just the Revenue data:
Moreover, Name the selected data from “Define Name” within “Name Manager”, I have chosen the name “Salesdata2011” for the year 2011. Similarly, the sales data of each year is named as “Salesdata20xx”
Name the Row and Column
Like we named the sales data, we will select and name the Row and columns.
Here, the Row named as ‘Month’.
Here, the column is named as ‘Product’:
For the Row and columns, we just need to/must name a single worksheet as they are common and named just for reference.
Now, that the hard work of naming all cells is done, all we need to do is to key in the formula, and our work is done.
Use the Excel inbuilt functions
As the final part, we will use the Excel functions of Index, Indirect and Match.
The INDEX function returns a value or the reference to a value from within a table or range.
INDEX(reference, row_num, [column_num], [area_num])
Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.
The MATCH function searches for a specified item in a range of cells and then returns the relative position of that item in the range.
MATCH(lookup_value, lookup_array, [match_type])
The Final Formula
The formula that does the searching for us is:
Let’s dissect each part of the formula:
This gives us the reference to the particular data sheet. We name each year’s sales data as “salesdata20xx” depending on the year. Here we concatenate the word “salesdata” with year provided in the B4 cell.
MATCH(D4,Product,0) and MATCH(C4,Month,0)
These two functions return the Row and Column to be looked respectively.
So, when the “INDIRECT” and “MATCH” functions are solved, the ”INDEX” function takes in the arguments as:
And, gives out the desired result.
Resource Workbook :
Please, find the Excel sheet here to work it out yourself.
One of the basic uses of Excel is to do a multi-dimensional search. Excel is a fascinating tool, and you can create several valuable reports with it. To get up and running, I recommend you to join a free webinar on “How to Create Business Report Using Excel” by Rushabh Shah on June 10th 3:00 PM to 4:00 PM IST powered by the education platform Learno.
Rushabh is the founder and CEO of dltc.co, a company that specializes in Excel and Microsoft Office training for corporate professionals. Rushabh has helped organizations such as NABARD, Bliss GVS, and TCPL Packaging train their professionals and improve productivity.
Rushabh is also an avid blogger and runs a very successful YouTube channel on topics related to Excel.