Excel for Accounting
Using a spreadsheet package like Microsoft Excel to manage your bookkeeping/accounting records has a lot of benefit when compared to having to scribble down on a Notebook manually.
Quite a lot advantages derived from this method include: Faster inputing, flexible record keeping, ability to store larger amount of data, security and neater record keeping. Imagine the number of errors that needs to be cancelled, erased etc. Something easily done on a spreadsheet.
The challenge for many however is the learning curve and the little sophistication needed to maintain a spreadsheet software [eg Microsoft Excel]. In this tutorial we will be showing you how to create your own Accounting Book and derive important records like your Sales, Income, Expenses, Cost of Sales and Profits.
You will need to have at least a beginner knowledge of Ms Excel in-order to make the best use of this tutorial, and if you don’t, still tag along. The article has been written to be super simple and provide easy learning points even for a novice. Almost every step is depicted in images for better understanding.
The article also provides important steps for those with no knowledge of accounting. The aim of this article is to build a simple Accounting file that can present Income, Expenses and Profit for a sample retail shop.
Creating your Chart of Accounts
Assuming you have an Excel workbook opened. The first step is to create our chart of accounts. Our chart of account will simply be categorized into two. Income or Sales and Expenses.
Create a blank sheet or rename an existing one to Account List.
Next, we input our desired account headings under each category. Our sample retail business is into selling of Stationaries, books, pens etc.
One thing to note is that our Expense category has Purchase of the same items depicted under income. In accounting, this items will keep records of our Cost of Sales. More explanation on this later.
Now we need to define each category by name to help us further when making use of the fields.
We will have to give name to each category list. And the step of doing that is selecting eg the Expense list, right click and define name.
Give it a name eg Expense and repeat same for the Income list as depicted below
Creating the Record Books
Now that is done, lets add two more sheets. Income and Expenses. As expected, the Income sheet will accept all Sales and Inflow records, while the Expense sheet is designated for all expenditures and outflows.
Next step: Input headers within each sheets for easy description. See the example below:
This is how our Income book looks like, akin to your Accounts notebook right. Don’t worry if your Excel doesn’t display as beautiful as mine. I simply converted to Table. Will explain the usefulness of this late.
Well you know what next, repeat same for the Expense sheet. Afterwards let’s populate. Just one more step I need to show you.
Now assuming our Retail Store started business in January 2018, we will input our transactions in the fields provided. But there is an important column – Account. We need this to source from the Chart of Accounts created earlier. Why we don’t want just any type of data to be entered into this column You will realise the importance of this as we progress.
So we are going to define the Account column to derive its data from the chart of accounts – Income section and likewise for the Expense section.
Click on the first row immediately after the Account header, go to Data Tab, Data Tools, Data Validation.
Great, Select List in the Allow field, type the equals sign = and input the defined name for the desired range. Remember we defined names for our Account Chart. You also have the option of selecting the range of the list if you so wish. Please untick the Ignore Blank checkbox.
Do not click OK yet. We want to ensure the field is limited only to items in the List. So click the Error Alert Tab.
Tick the Show error checkbox
Title and Message – Enter whatever you feel is appropriate
Now you can click OK. Our list should be populated into the Account Column. Remember to drag down the Cell edge to apply the same list validation to other cells in the column. You will realise that the Account Column can only accept items within the Drop-down list.
Your Turn: Repeat the same process in the Expense sheet. Don’t forget to use the name “=Expense”
A Sample Transaction Record
I have populated both sheets with dummy trading data. See below:
Note the Quantity Column – More like additional information pertaining to Items for trading which will be very useful later.
Creating a Profit and Loss Summary
Let’s learn some simple accounting here. A typical trader would want to compare total Income to all Expenditure and simply prescribe any excess as profit or loss if deficit. However for those who don’t know – Our actual profit must take cognizance of items that were purchased and not yet sold. Invariably what it means is that we can only recognize profit on actual items sold by excluding expenditure on un-sold items still in stock.
Peeps, we will be deriving a Profit and Loss table – Not really a perfect one but close. Not to also forget that or comparison for both categories within an applicable period.
Manually we could extract our Income and Expenses, Sum and subtract from each other to derive our profit. And we can also automate that process too.
To do that, there are so many options in Excel, it all depends on which method you are comfortable with. In this tutorial, we will be using Pivot Tables.
I guess that is a bonus – Learning Pivot if you have never tried it before.
Using a Pivot Table
Step One – Convert your Data to Tables. Go to your Income sheet. Select the entire Columns i.e From Column A[Date] to E[Quantity]
Under Home > Tables > Format as Tables > and select any fancied table of your choice.
Apparently you will notice I have done this earlier.
Next define and name the table. Go to Design Tab > Table Name and give the Table a name e.g Income_Table
Your Turn: Do the same for the Expense sheet and name the table Expense_Table
Now we can build the Pivot Table for our Income.
First create a new worksheet, you can name it Dashboard. Go to Insert > Pivot Table and enter the parameters below:
Table/Range – Income_Table
Select Existing Worksheet [This is the new worksheet you just created]
After clicking Ok, your screen should look like this.
Please pay attention to your right [PivotTable Fields] For our Income, we simply want to extract data as follows
Values: Amount, Quantity
Simply drag the stated headings in the Field Box into the corresponding box.
Your screen should look like this:
Now we have our total sales. If your Value fields is showing Count of “”. Please change it to Sum of “” To do so: Access the Value field settings of the field by clicking on the dropdown beside it.
And change the Summarise Value Field by to Sum
Derive Cost of Sales
Like I mentioned earlier: trading expenses that are related to items being sold will be calculated separately from other expenses so as not to calculate un-sold items though paid for as part of our expenditure.
We call these items Cost of Sales(COS) [Direct cost of items being sold]
To derive our COS, we will multiply the quantity sold by the purchase cost per unit. Calculating our Purchase Cost/Unit should be quite easy. Either you already know it or we could work it out using Pivot tables as usual. Just so you know, your purchase cost/unit is the sum total of your purchases divided by total quantity.
First let’s get our Cost per unit
Place your Cursor on cell to the right of your existing Pivot Income Table. And let’s repeat the Pivot steps for our Expense table this time around. See the screenshot below:
Now for this section, we will also return the Account, Amount, Quantity and Date
The result of which will look this
Using Excel formular, we will calculate the cost per unit. Note that to calculate, I strongly recommend you use the Cell Reference and not click each cell to derive your figure.
In my example, I inputted (=H4/G4) and then drag down to complete the rest.
Now that we have our Cost/Unit, next step is to calculate our COS.
COS= CPU[Cost per Unit] multiplied by Sales Quantity
Another Pivot table using our Income_Table. Extract the Account list only. Reason being that we want to extract the figures based on our sales figure.
Before we calculate, one more thing please. Lets sort and filter the Sales and COS items to ensure they are properly aligned.
To sort, click on the Dropdown Option box on the right hand of the Field header.
Income: Un-filter the blanks and sort by A to Z
COS: Un-filter the other expense accounts, blanks and sort by A to Z
Now when I say un-filter, I meant Untick.
Our Income section and Cost of Sales should be arranged alike now. We can now proceed to calculate. Fairly easy I guess.
Simply return the Quantity for each item from the Sales Table and multiply by the Purchase Cost/unit derived earlier to get the Amount. Using Cell Reference as usual and drag down.
Now we have our Income, Cost of Sales, let’s go ahead and retrieve the other expenses.
By now you should be familiar with Pivot tables, so once again. Insert a Pivot table below the COS section for Expense_Table. Filter out the Purchase Items in already attended to. And return Accounts list, Amount and Date.
Gracias!!! So let’s see our actual profit.
Gross Profit: Total Sales less Cost of Sales
Net Profit: Gross Profit less Other Expenses
Boom Sha!!! Sha!!!
One more thing we need to note: Remember I said you compare your Income to your expense for a particular period. Good, assuming you intend ascertain your profit for the month of January. All we need to do is to filter for the relevant date using the Filter Date option for each section.
Also, because we have different days in a month. To select January as your report period for instance – use this format 01/2018, February would then be 02/2018. Hope you got the trick
It also means you need to filter in three places
- Cost per Unit [If you need to]
- Other Expenses.
We actually could adopt a better approach to this but that would involve combining our Pivot Tables and creating relationship between them. But that is a learning topic for another day. I want to make this topic as simple as possible.
This tutorial is also available as an Ebook for free. Just drop your email in the subscription box and a copy alongside the source Excel file will be mailed to you for free.
Excel could be very powerful and flexible. However, there are serious limitations to its functions and usability. There are other factors to consider and so many complex records that you would need a more robust program to handle.
The best approach is to make use of a fully-fledged accounting software and at an affordable price too. BookKeeper comes highly recommended. With advanced functionalities to handle Financial records, Payroll, Customer and Supplier Modules, Balance Sheet, Bank Reconciliation, Cash Management, Foreign Currency capabilities, multi-user access and much more.
Bookkeeper also comes as a cloud solution so you don’t have to worry about hardware, software, technical equipments or installation hassles. It viewable even on your mobile device. With Bookkeeper, you have anywhere, anytime secure access. And your reports are easily accessible at a click of a button, no back and forth.
Comments, suggestions, criticism are welcomed. I tweet via the handle @ZionOyemade