This post looks at some key business questions that can truly bring to light very interesting insights. Know thy business…
In this exercise our client, an aircraft hardware suppliers, wanted to know key information about their business. they presented us with a spreadsheet database, containing several tables related to purchase orders and sales transactions.
Here is the list of questions and requests from the client:
- Create a drop down menu for easier access to information.
- Explore whether the orders are evenly distributed around cost.
- What were the average purchase amounts per region?
- What the most common form of payment?
- Do customers tend to shop during work hours (8-5) or while they are at home?
All of those were very well defined questions and yielded some very interesting answers. To be able to answer those questions, I employed several tools to arrive at the answer to each question.
(Click to Enlarge)
1. Create a drop down menu for easier access to information.
- This menu should be set up in such a way where any selected order number would be followed by the item’s description, the supplier’s name, the discount price and the number of days between the order data and the corresponding arrival date of the order.
Answer: Using a combination of Spreadsheet querying techniques, tables, VLookup, HLookup, Index and Match Functions, we were able to create the drop down menu that the customer requested. It looked something like this:
2. Explore whether the orders are evenly distributed around cost.
Answer: By conducting Pareto Analysis, we can provide the client with crucial bit of information.
In our client’s case, only 17% of the orders accounted for more than 50% of the revenue. The remaining 83% generated another 50% of the revenue. We can clearly see that the cost per order varies widely across orders. Therefore, our client, armed with this knowledge, can go on to conduct further analysis on those orders and see whether there are any targeting strategies that might suit their business best or decide to completely overhaul their sales strategy, among other approaches.
3. What were the average purchase amounts per region?
4. What the most common form of payment?
5. Do customers tend to shop during work hours (8-5) or while they are at home?
Answer: The best way to address those questions is to use pivot tables. Pivot Tables are the fastest, most precise and most versatile way to summarize key information, particularly in this situation.
Given that our spreadsheet database already had data regarding the sales transactions and it also included the regions associated with each transaction, It became easy to see that once the data is sorted and the arithmetic is conducted that the South was the region with the highest number of transaction.
Similarly, through another pivot table, were were able to discern that our customers overwhelmingly prefer credit to paypal in terms of how many items they bought and how much they paid per order. In fact, on average, customers used credit in more that 63% of the recorded transactions.
(Click to Enlarge)
Interestingly, our analysis revealed that our customers are just as likely to place orders while they are at work or while they’re at home. This information can help our client in future marketing endeavors decide when to display ads and to whom.
The information above is from the Graduate Certificate in Business Analytics: Descriptive Analytics course at Penn State University.