Pivot Tables and Multi-Attribute Decision Making

Sunshine Floor Barn, one of our top clients at Diligent Consulting Group has just closed the books for the first three-quarters of the year (January through September). As one of the most reputable firms in the country with the drive to increase profitability, Sunshine Floor Barn requests for analysis to be performed on the sales of its five product lines over this 3-quarter period. The company has four regions namely; East, South, Mid-states and West regions and each Regional Manager at the company compiled the data for his/her region. As the lead consultant for the Diligent Consulting Group, I was tasked with performing in-depth analysis on the data by use of Pivot Table and Pivot Charts and also to make recommendations afterward, on how Sunshine Floor Barn should improve its sales performance going forward. 

Methodology 

Before constructing a pivot table and a pivot chart, the unorganized data was first entered into an excel spreadsheet. Pivot charts were used because they give meaning to large amounts of data which may seem incomprehensible at first sight (Donnelly, & Abdel-Raouf, 2016) . Pivot tables were also used in order to make the work neat and presentable and also easy to interpret and to be able to make conclusions about Sunshine Floor Barn’s sales data. The unorganized data was first put into a tabular form which could be sorted in terms of regional sales performance. A pivot table was then constructed using regions as filters because the main objective was to identify which regions and quarters had the highest sales. 

A Pivot Table Report with Charts 

Pivot table 

Pivot Chart 

Analysis 

The pivot table computed above shows the total sales revenue of each quarter for each of the city where Sunshine Floor Barn operates, together with the grand total. The table has been sorted in a descending order such that the city with the largest sales is displayed first followed by the city which came in second all the way to the city with the least number of sales. Amarillo was undoubtedly the city with the highest number of sales followed by Jacksonville while Memphis and New Orleans came in second last and last. The pivot chart was drawn simply provides a visual representation which makes it easier to understand the pivot table. 

Sales by quarters 

In order to determine quarterly sales, the above pivot table was constructed. Quarter one period generated the most sales with a total sales revenue of $ 4,048,287. These results imply that Sunshine Floor Barn company performed exceptionally well at the start of the year in the months of January, February, and March which is in the first quarter. This period was by far the most productive period for the Sunshine Floor Barn and this can be attributed to several factors. The first major cause of success was the availability of raw materials particularly in the West during the first quarter period. At the start of the year, the trees that had been planted in the previous year have already matured and are ready for harvesting for example mahogany and maple trees. The availability of raw materials at this time was therefore crucial to the production of more goods which led to increased sales. The other major contributing factor to sales was weather (Tainer, 2006). Good climate and weather conditions at the start of the year favored the production and sales of goods at this time. 

The second quarter period generated the second most sales with a total sales revenue of $ 4,048,287. The Sunshine Floor Barn failed to surpass the revenue sales realized in the first quarter since in the second quarter their total sales had a deficit of $ 123,092. Their sales dropped from $ 4,048,287 to $ 3,925,195 in the months of April, May, and June. This minor drop could be attributed to consumers not buying frequently like in the first quarter as they saw no need to replace commodities which they had just bought. The third quarter period generated the third most sales with a total sales revenue of $ 3,911,140. This was a deficit of $14,055 from the second quarter sales and a deficit of $137,147 when compared to the first quarter. This was the lowest selling period for the company as the sales recorded were the lowest. A drop in consumer spending can be attributed to this drop in sales due to harsh economic times. Another factor which might be responsible for this drop is the use of the outdated marketing techniques by Sunshine Floor Barn company. The company could be relying on the same stale marketing approach yet there is increased competition in today’s modern market. There are other numerous companies producing the same products which are equally efficient and well-advertised compared to Sunshine Floor Barn products. The New Orleans store particularly recorded the least amount of sales and this fact could be attributed to the poor hiring of staff. The staff hired play a huge role in achieving better sales and making the company profitable. 

Recommendations 

As the lead consultant at the Diligent Consulting Group, I would recommend certain improvements for the company Sunshine Floor Barn to maximize its operations. First and foremost, Sunshine Floor Barn should hire skilled and talented staff. Stores located in cities such as Memphis and New Orleans performed dismally when compared to other stores simply because of the kind of staff working in these stores. The company should, therefore, take time in future to look for qualified and experienced personnel. Consequently, the company could train its current workforce rigorously or at least once each year to keep them abreast with the latest technology and marketing techniques. In addition to this, I would strongly recommend for a change in the marketing techniques of Sunshine Floor Barn company. Their sales dropped significantly due to stiff competition from their counterparts who had better marketing strategies. The company should, therefore, invest in advertising in order to increase their popularity and brand awareness. Increased brand awareness would automatically lead to a huge customer base and eventually larger sales. The last recommendation to the company would be for them to improve their resources technology wise. Improved and effective technology translates to better, quality products which will keep consumers satisfied and thereby maintaining their customers’ loyalty. 

Conclusion 

Profit is the main driving factor for any business and Sunshine Floor Barn is no exception. This is why the company decided to contract the services of the Diligent Consulting group to have their sales analyzed professionally. Pivot charts and pivot table were used since they best simplify data and make meaning out of it. From the tables, it was clear which cities were performing well and which ones dismally. In addition to this, Sunshine Floor Barn was able to know on which quarter period was they at their peak and in which period at recess. 

References

Donnelly, R., & Abdel-Raouf, F. (2016). Statistics (1st ed.). Indianapolis, Indiana: Alpha, a member of Penguin Random House LLC. 

Tainer, E. (2006). Using economic indicators to improve investment analysis (1st ed., p. 56). Hoboken (N.J.): J. Wiley.