Study tips: Spreadsheets for accounting

Spreadsheets play a major part in an accountant’s working life.

Having the relevant skills and techniques required will make complicated calculations much easier, not only to understand but to manipulate too.

Charts and graphs are commonly used in excel and are often required to be produced in the workplace. The example below shows how we are going to manipulate a simple graph and pie chart based on a common business scenario.

The scenario will cover:

  • Gross profit calculation
  • Gross profit margin as a percentage calculation
  • Column chart
  • Chart title
  • X & Y axis labels
  • Horizontal axis labels
  • Legend entry
  • Chart colours

We may be required to link several items from the list in one spreadsheet, relying on the calculation of data to produce a chart or graph. We can apply additional knowledge that we have obtained in our work or study life to be able to produce what is being asked.

Let’s look at an example with the assumption that we have been asked to produce a basic chart.

Producing a basic spreadsheet chart

Scenario: Emily runs a family pet shop business. She sells a variety of small animal accessories. The items she sells the most is a small dog accessories.

You work as an accounts assistant for Emily helping her with the day-to-day accounts function. You have been asked to produce a ‘stacked column’ chart showing which product has the highest sales revenue. You already have the following information from figures you have collated over the last 6 months:

To produce a chart showing the total sales revenue and gross profit margin of each item, additional columns need to be created. These should contain formulas to calculate the relevant information in preparation for producing the charts. This is not always obvious and is where technical understanding comes into play.

This is just a very basic example. In the future, it may need further manipulation, such as to incorporate other expenses (e.g. overheads) to work out net profit figures.

Below is a total sales revenue column, total costs column, a gross profit column and a gross profit margin as a % column.

Emily may need to see how these figures have been calculated so it is helpful to be able to show all the formulas that have been used. In order to produce the Gross profit, the Total expenses are deducted from the Total sales revenue e.g. Coats £15,558.27 – £10,654.35 = £4,903.92.

To display the Gross profit margin as a percentage, the Gross profit is first divided by the Total sales revenue e.g. Coats £4,903.92 / £15,558.27 = 31.52%. The sum will not automatically turn into a percentage so to do this we need to select the % option as shows:

Now the data is ready.

Care is needed when selecting the correct chart – in this case a stacked column chart. Note that although we’ve been asked for a stacked column chart, in reality we’re simply displaying a basic column chart as there’s only one set of data in each stack or column.

Building your chart

Firstly, select the data required and then select the chart type which can be found in the ‘Insert’ tab.

Click OK and this will produce a basic chart. Being careful with the chart selection will show Emily that care has been taken when producing the required information.

The above chart has been created using just the total sales revenue column in the data. It is meaningless at this stage. Presenting Emily with this chart in this format will give her the information she’s asked for, but in the future, she’ll struggle to see what the information relates to.

If Emily wanted to use the chart for any other purpose – such as to show investors, or even review her charging structure – she would struggle to present a professional image. It needs additional labels and a title for the chart to have any relevance.

Making your chart more professional

If we add in a chart title, x and y axis and legend labels, the chart becomes a lot clearer and easier to understand.

The viewer can now fully appreciate the content.

Adding colour to spreadsheet charts

It’s also possible to change the colours displayed. Right clicking on the chart columns will display the formatting options – just select fill and choose a colour best suited to your chart.

In summary

Comparing the first chart and the final example shown above, it’s clear that professional competence and due care has been demonstrated. Emily can now use this chart to inform her business decisions.

Read more on excel and studying from AAT;

Shelley Garrington is an AAT tutor for ICS.

Brought to you by
Brought yo you by

Related articles