ArK Tech Consulting
How does your data look like?
Before creating a table or chart, you must first obtain data. For a majority of companies, this might come in the form of daily sales reports. Whether your company has raw data or pre-formatted data, you next step is to cleanse the data to make sure it is usable.
​
Column Chart
Similar to the bar chart, the column chart is a great way to compare different segments. Grouping different segments beside one another allows for easy comparison and tracking.
Pie Chart
The pie chart is a great way to show how a business is broken out. This chart can justify how you want to spend your company's resources.
Excel Charting Tools
There are many different charts to choose from in Excel. Whether it's a simple bar graph or pie chart, you want to choose the correct chart that will convey and support your message. Below are a few examples of charts completed for a grocery chain that wanted to track their different business segments.
Below is an image of sample data. The left half contains unformatted raw data which is very basic. In order to make sense of this raw data, you want to associate the data with relevant attributes. These values are shown in the right half and is produced using formulas (Vlookups and String Manipulation). There are many ways to produce attributes: macros, vba or through a database. Depending on the size of your business, you need to choose a solution that will be scalable for the future.
The image to the left shows how a business's sales are doing on a monthly basis. Each year is shown in a different colour and can be filtered based on Category and Subcategory.
In this example, you can see how well this year's sales are versus last year. You can also filter on different sections of the business to see which sections are growing and which ones are in decline.
Taking it another step forward, you might decide you want unit sales alongside dollar sales. The image to the left combines two charts.
1) The total dollar sales by month as the column chart.
2) The total unit sales by month as a line graph.
Combining both charts, you can determine where the peaks and valleys are and what is causing them - whether items are being heavily promoted or not enough.
The example to the right tells two stories:
1) Which business segment is the largest/smallest - knowing this, you may want to spend more resources defending market share on your key products.
2) Which business segment is growing the fastest - these fast growing segments could be a fad or a future trend. Depending on how developed this segment is, you may want to enter this market to capitalize on new customers.