INTRODUCTION
Buddy's Floor Barn closed the books for three quarters (Q1, Q2, and Q3) of the year. However, Buddy’s Floor Barn has 4 major regions (Mid-states, East, West, and South) and about 18 store locations. There is a request to analyze total sales performances taking into consideration 5 product lines, namely (Cherry, Bamboo, Mahogany, Oak and Maple). Sales revenue data provided by the company through the regional managers from these four regions are collected in excel and analyzed using pivot tables and charts to determine the sales performance. Data provided is raw and must be rearranged from the lists to tabular results that can be used to automatically generate pivot tables and charts (Vogt, 2009).
ANALYSIS
The Pivot chart illustrated below displays the total revenue collected for all the three quarters specifically for 18 locations. Commencing the analysis regionally then comparing the results from each region would be the best option since it will help reduce data redundancy and encourage proper analysis. Proper analysis can be done regionally following the order of East, Mid-States, South and West.
1.0 East Region
Considering the fact that a large amount of data was collected in excel format and the list is big, data breakdown helps in understanding into details the prospects of revenue collected regionally and what attributed to the same and well as the collection of low revenue. In this case, fig1 below illustrates how East Region performed in terms of revenue collection and the member states that are located in the same regions (Statistic, 1995). The 5 species of trees sold in the entire year can be tracked and recorded in the pivot chart below for specific regions, and in this case, it is the east region.
Fig1. Pivot Chart for East Region (Appendix A)
The highest revenue was collected in Boston quarter 3 where Cherry was sold at $59,594 and the lowest revenue collected was Oak with the total of $25,311 in the second quarter specifically at Buffalo state. This could be attributed to the low and high demand of certain species during different quarters of the year. Weather changes, species, prices and transportation could be the main reasons why the revenues collected in different states at particular period or quarters in the year. Generally, in the East, transport could not be the main challenge but the species on demand where Cherry records high revenue across the year.
2.0 Mid-State
Mid-State reported high revenues generally based on a high number of collections reported for Bamboo in Columbus that recorded $59,942 in the first quarter of the year. The lowest recorded revenue was recorded for Mahogany with $25,587 in the third quarter of the year also in Columbus state. The total revenue collected for Mid-State was higher than that of East State based on the fact that Bamboo as a species is highly priced and is in high demand as compared to all other tree species. In addition, Mahogany is also on demand. The majority of the species on demand comes from the mid-state thereby increasing the total revenue collected. The Pivot Chart below illustrates the revenue comparison recorded for the Mid-State (Vogt, 2009). Considering the entire year, the first quarter recorded the highest revenue at $956,056 as compared to the second and last quarters that recorded $870,157 and $874,223 respectively according to the records.
Fig2.0 Pivot Chart for Mid-State (Appendix A)
3.0 South Region
South Region has different states including Atlanta, Jacksonville, Memphis, Montgomery and New Orleans. These states recorded the highest revenue on Oak, Bamboo and Mahogany as compared to other tree species. Oak recorded $55,938 as the highest revenue collected for the entire year. However, the lowest recorded revenue was for Oak in the third quarter of the year at $25,163 in Memphis. Considering the fact that Oak recorded the highest in the south region and the lowest in the same region, we conclude that different states had different demands and some other attributes such as distance and transportation could affect the total revenue collected. However, it is also important to note that the highest and lowest revenue were collected in different quarters of the year meaning that the weather conditions and other factors such as demand could result in this major difference of about 30,000 in total revenue.
Fig3.0 Pivot Chart for South (Appendix A)
4.0 West Region
West Region Recorded the highest revenue in Maple, Bamboo, and Oak. The records show that Maple is the highest among the tree species with the total of $59,975 in Denver and the lowest revenue for $26, 179 in San Diego. The difference of about $33, 000 between the highest revenue and the lowest revenue is higher compared to other regions. Comparing the cities, West cities performed much better as compared to East, South, and Mid-State. Figure 4.0 below illustrates the performance of West Region.
Fig4.0 Pivot Chart for West (Appendix A)
West Region recorded a total of $1,081,945 in revenue while East region recorded the lowest at $940, 349 in revenue collection. In terms of quarters, the highest recorded profit was collected in the first quarter $4,048,287 and the lowest recorded $3,911,140 in the last quarter. During winters towards Christmas and New Year holidays, the sales go down since the demands are lower as compared to January to April.
Data Analysis
Region data customization makes it simpler to perform data analysis in terms of trends, high and lows. Various Cities and regions perform differently based on both internal and external factors. It is not true that when the entire region does not perform it mean that all the Cities did not perform; however, Regional performance can be attributed to City performance. Pivot table offers a good platform for data analysis in terms of regions, Cities, and the actual products. The popularity of goods that are sold can be tracked due to the trends of their performance regionally and according to various Cities. The highest level of revenue was slightly lower than $60, 000.
Complicated data sets are inevitable when using MS excel, but the same can be managed properly using the Pivot tables. In this case, four pivot tables have been used to analyze data from regions independently in order to go down to the Cities within the regions and take note of their product performance. In order to trace and manage the revenues collected, it is important to begin from the City level then Region level. Aforementioned findings can be optimized in order to minimize storage and transport costs. In case the same findings are utilized, it would be very easy to predict revenue collected quarterly and yearly.
Recommendations
Data analysis should be done after every quarter in order to understand the performance of the products per region properly. The report collected from the analysis can help the management to limit additional expenses and improve in profit maximization. Revenue collected can also be improved by cross-examination of all the regions and take note of the potential performance. In this case, it was noted that bamboo performs well in all the regions alongside Oak and mahogany. These three products can be produced in larger numbers as compared to other products that do not perform well in certain regions. It is also important to take note of the main cause of low revenue collections. Total revenue is directly promotional to revenue input.
` It is important to note that customers from all the cities and regions may have preferences for certain products over others. Customer preference can be recorded using questionnaires and the collected information used to improve sales in terms of product delivery and production. In some areas, climate change affects sales and this is natural therefore low production during low seasons would lower expenses and high production during high season will ensure that the company gets value for money (Vogt, 2009). Transportation and distribution are very important, and this should be taken into consideration more for products on demand in different regions. Marketing and promotion more so in the East and South Regions is highly recommended in order to reach out to more customers. Other Cities in the West such as Boise performed poorly compared to Baltimore in the East. Therefore customer promotion should be spread across all the regions despite the current performance.
References
Kovar, B., Kovar, S & Vogt, R. (2009). Tutorial: Conducting Data Analysis using Pivot Tables. American Accounting Association. Retrieved from: <http://info.cba.ksu.edu/bkovar/PivotTableTutorial.pdf>
Statistics, J. R. M. (1995). Data Analysis. College Station, TX STATA Corporation. Retrieved from: <http://www.cse.unsw.edu.au/~en1811/15s1/lectures/week02.pdf>
Appendix A
