To glean vital marketing information (Winston 2014) from data, use the PivotTable option in the MS Excel; this tool helps you summarize and project your data in multiple ways. So get your spreadsheet up and running. This blog talks specifically about how to use the PivotTable and Slicer feature. For support on business analytics, please visit our business analytics page.

In our example, ABC Corporation has three subsidiaries (Guires, Pepgra, PHD Assistance, and Stats Work) offering various services. The marketing manager requested the analyst for the following four types of reports:

1. Report on sales percentage at every location.
2. Report on monthly sales percentage.
3. Report on every product’s revenue.
4. Report on products that generate major portion of revenue.

With the help of the available marketing data you must follow these steps and figures carefully to generate 4 reports.

Creating a PivotTable

Here are the steps to create a PivotTable from the example given in Figure 1.

Let’s begin by placing headings at the top for each of the table columns. See Row 1 A – D for MS Excel to recognize your table.

Figure 1. A sample marketing data.

In the menu bar, click on Insert and in the ribbon pane click on pivot table; you will see this next. See Figure 2.

Figure 2. Invoking the PivotTable.

After you click OK, you will invoke the PivotTable as posited in Figure 3. Field List pane on the right side of your spreadsheet and you must select the fields by dragging and dropping into the boxes.

Figure 3. After invoking the PivotTable.

To hide the field list, right click inside the table and then select Hide Field List from the pop up pane; refer Figure 4.

Figure 4: How to Hide Field List?

Calculating percentage of grand total

Follow these steps to make specific calculations, say calculating sales percentage of the grand total from the organization.

In the PivotTable Field List pane on the right side, drag Company into the Row Labels box below. Items that you drag into the Row Labels Box always show up on the left of the table towards the left of the spreadsheet.

Changing values to currency

Now look at Figure 5. You can see the values in currency; double click row 3B and type in currency but do not click OK yet.

Figure 5. Value Field Settings.

Figure 6 illustrates how to change from percentage to currency; In the dialogue box that appears, click on Number Format button and select Currency from the list on the left of the box.

Figure 6. From percentage to currency.

Change Percentage to Currency. After you select Currency from the list, you must change the text field under Sample from Percentage to Currency as given in Figure 7.

Figure 7. Changing values.

Click OK and click OK once again. Here is what you will see (Figure 8).

Figure 8. Changed to $$$.

Determining revenue of your service offerings

In our example, the organizations offer biostatistics, CRO, analytics, and dissertation services. To calculate revenue of these services, follow these steps.

• Drag Services to Row Labels box.
• Drag Price to Values box.

Figure 9. Drag and drop.

To alphabetize the list, sort row 3A from A to Z by clicking on the drop down arrow next to Row Labels as highlighted in Figure 10.

Figure 10. Sorting alphabetically.

Ascertaining services that generate the most revenue

This is 80/20 rule; follow these steps to find out services (20%) that contribute (80%) the most to the various organizations. Refer Figure 11 to get an idea as to how it is done.

Figure 11. Leveraging Pareto’s principle.

Figure 12. Filtering top 20% of services.

After you click OK, here is what you see in your spreadsheet. Refer Figure 13.

Figure 13. Top revenue generating services.

Hence, we can conclude that only 3 services out of the 4 are needed to generate 80% of the revenue.

Slicing data

The Slicer is a quick and easy tool to filter a PivotTable. There are times when you do not want to see all the subsets of the data; however, if you want to select only a few subsets then you can use the Slicer feature in Excel; Figure 14 shows you the option in the ribbon pane.

Click on Insert on the menu bar and then click on Slicer in the ribbon pane.  In the figure below you will see services—Biostatistics and CRO—selected in the Slicer pane. You update the PivotTable by selecting the button-like options in the Slicer pane.

Figure 14. The Slicer tool.

The slicer tool pane (view Figure 15)  appears with various buttons pertaining to the services. Select the services that you require and the table is updated based on your preference.

Figure 15. Buttons in the Slicer.

So there you have it. A unique Excel based alternative to review your marketing data using the Slicer and PivotTable tools in Excel. In the fast-paced corporate world we live in, these tools help us glean valuable marketing insights. Guires also offer support in Big Data and Clinical Analytics, talk to us for more details.

Guires Solutions Pvt. Ltd., is a management consulting, outsourcing and technology company. With humble beginnings in 2008, the company has come a long way. A dream that commenced with research content development has now diversified and extended its reach into several domains. The company has experienced a remarkable growth where we now serve several industry sectors along with research content development. Our services now include; industry and market research, data analytics, branding and marketing. We have emerged as a leading multidisciplinary service provider offering solutions that are remarkable, at par with industry best practices and offer the best value for money. We deliver flexible and cost-effective solutions with maximum impact. www.guires.com

Related posts, links, and references

Winston, Wayne L. Marketing Analytics: Data-Driven Techniques with Microsoft® Excel®. Indianapolis: John Wiley & Sons, 2014.

https://www.guires.com/business-research-solutions/big-data-analytics/

https://www.guires.com/business-research-solutions/Clinical-medical-analytics-services/

https://guires.com/resources/brouchure/Healthcare_Analytics_Guires-Brochure-2016.pdf

Major impediments which doctoral researchers need to surmount

PhD Assistance, is world’s reputed academic guidance provider for the past 15 years have guided more than 4,500 Ph.D. scholars and 10,500 Masters Students across the globe. Academic and scientific researches face challenges in their workaday life.

This blog reveals some of those impediments to success and guides researchers on how to surmount them.

Lack of motivation

Researchers are in for a long haul in their research work. Dissertations and publishing papers in journals is a tedious process overall and takes quite a chunk of time and researches need the impetus to carry out such projects despite the cumbersome nature of the project. Setting specific, measurable, achievable, realistic, time bound (SMART) goals coupled with incentives will go a long way in completing projects. Researchers must set personal incentives or reward themselves for reaching small milestones.

Dire need for self-reassurance

A doctorate research project opens a world of possibilities and options because researchers have plenty to choose from but they have to refine on the specific area for their thesis work. During this stage, they require the self-esteem and incentives to propel forward when they face plethora of options and alternatives. Researchers need to be wary of their level of self-confidence and self-belief by doing any of the following:

• Getting self-reassuring inputs
• Accepting your milestones thus far
• Taking inventory of your strengths
• Going the extra mile to do new tasks

Psychologists and psychiatrists are trained to address self-belief issues and can motivate you to get back on track.

Managing resources

For researchers, time is certainly a precious commodity. Dissertation projects require lot of planning and time and it is in this phase that managing resources such as time, people, and money come into play. Take the support of research mentoring services from PHD Assistance.

Need for help-guides

Researchers need help-guides too. During long-haul projects, researchers may go off a tangent or digress from their research objective; guidance from professors is always handy during such occasions and sometimes they may have to redraft their thesis if the project objectives change. Researchers must do an overall assessment and timely breaks certainly help during this arduous phase to get their focus back because too many details of the project can be overwhelming at times.

Lack of networking

Researchers must network with their contemporaries not only for the purpose of adding value their dissertations but to draw motivation and inspiration from peers’ work. Doing a doctorate can be a solo activity and building relationships is vital to get the necessary support. Here are some things researchers must work on:

• Build rapport with supervisor
• Leveraging all their contacts
• Seeking qualified mentors
• Working in groups

Lack of creativity

Beware of only doing things that you are comfortable doing and know you are fully capable of doing. This is known as your comfort zone and tends to be highly limiting. Try continually to stretch yourself to ensure that you keep learning, for example by volunteering for something that you don’t see as one of your strengths. For example, utilizing peer-review support will certainly provide much needed insights to a researcher.

Not taking risks

Researchers learn more from their failures and they truly are stepping-stones toward completing their research work. Failure is an impetus to perform better and researchers must seek advice from mentors during failures.

Lack of exposure

Researchers must identify gaps and explore all alternatives and make use of opportunities and take risks as well. They must try to find some work experience to expand their competencies and skills.

About Us

PhD Assistance, is world’s reputed academic guidance provider for the past 15 years have guided more than 4,500 Ph.D. scholars and 10,500 Masters Students across the globe. We support students, research scholars, entrepreneurs, and professionals from various organizations in providing consistently high-quality writing and data analytical services every time. We value every client and make sure their requirements are identified and understood by our specialized professionals and analysts, enriched in experience to deliver technically sound output within the requested timeframe. Writers at PhD Assistance are best referred as ‘Researchers’ since every topic they handle unique and challenging.

We specialize in handling text and data, i.e., content development and Statistical analysis where the latest statistical applications are exhausted by our expert analysts for determining the outcome of the data analysed. Qualified and experienced researchers including Ph.D. holders, statisticians, and research analysts offer cutting edge research consulting and writing services to meet your business information or academic project requirement. Our expertise has passion towards research and personal assistance as we work closely with you for a very professional and quality output within your stipulated time frame. Our services cover vast areas, and we also support either part or entire research paper/service as per your requirement at competitive prices. http://www.phdassistance.com/

Related posts and links

• http://www.phdassistance.com/services/phd-dissertation/
• http://www.phdassistance.com/services/manuscript/
• http://www.phdassistance.com/hire-a-phd-research-assistant/
• http://www.phdassistance.com/help-guide/