Question

Consider the following scenario: “You are an assistant to the accounting manager for a small company that sells sports equipment online. The manager needs analysis of the sales by-product to be presented at Friday’s managers’ meeting and asks you to gather and summarize the data."

How can you use Excel to complete this assignment?

What information do you need to produce an accurate, useful analysis?

What questions might you ask the accounting manager before starting on the assignment?

Answer 1

A. Following are the basic data required to analyze:

• Real time Sales Metrics and Sales to date (comparing current sales against previous).
• Product Performance, Type & Location.
• Sales Growth and Target (% and in numbers)
• New customers acquired for the given period.
• If you want to give more detailed, then extract Product categories & number, Gross margin of products, customer data etc.
• There are few metrics/KPI (Key performance indicator) to measure the sales apart from basic metrics. "Sell-through rate"(Track sales versus total inventory) is required since you are selling physical goods. "Quote to close" rate shows how effective the deal was closing. It shows the analysis of the "quality of your sales" against "quality of leads". "Sales per representative" it shows how your sales team contributing, and this will be useful to forecast future analysis.

B. The best method to use in excel is "Trend Analysis". One or more multiple spreadsheets you can use as central location to update the key metrics. If you have different sales division, extract the data separately and collate in one sheet. Once the data collected, select the data double click on the excel then select the Graph whichever you want. Green button next to the upper-right corner of the graph. A drop-down menu will appear. Select Trend line option and then select your preferences.

You can show the Trend Analysis report by Sales revenue level, Product level, Customer Retention level, By country/division level etc.

C. You should ask your manager:

• Frequency (Quarterly/Monthly/Half Yearly/ Annually). Based on that, the collection of data differs.
• Specific Product or service or customer trends. Confirm with him, whether the report required for overall equipment or any specific item.