Accounting in Excel, for Free!

DOWNLOAD Ver0.3.35: (sharepoint)


Warning 1:
Requires Excel 2019 or greater.

Warning 2:
You need to understand Debits and Credits to use this software.

Not an accountant? That’s ok! This software and my YouTube videos will teach you. =)
https://youtu.be/Rf2FcUyafss


 

Full double entry
accounting software in Excel

I have developed an accounting software app in native Excel. No VB; no macros. 

And I will be giving it away FOR FREE

 I hope this might be helpful to micro businesses or community groups.  

Right now I have only released the education version. It's designed to be small enough for students to understand the entire accounting process. The system assumes an Australian sole trader, not registered for GST.  The commercial edition will allow for companies, partnerships, income tax, sales tax, and many other variables. 

Free. For everyone. Forever.

The student version is not designed to be robust enough for serious business, but might be enough to run a micro business or community group (e.g. a church or soccer club).

Hi I’m Phillip!  An accounting academic who wrote a spreadsheet that allows you to do double entry accounting in Excel!

Hi I’m Phillip! An accounting academic who wrote a spreadsheet that allows you to do double entry accounting in Excel!


Understand Accounting

The system follows an intuitive colour-coded system (see the PDF)

  Assets are blue for liquidity

  Liabilities are "in the red" as credit is the lifeblood of business

  Equity are "in the black"

  Revenues are green for growth (think $100 bills)

  Expenses are orange like rust or autumn leaves. 

5 colour system.png

How to Use the Software

Step 1: On the Set Up Tab, change :

  • the Business Name, 

  • Owners Name,

  • ABN (optional) and

  • Start and End dates of operation. 

 Step 2: Set up your Chart of Accounts in the Chart of Accts tab.  
You can NOT add new accounts if there is not space (limitations of Excel without VBA).  
If you want more accounts, tell me. Business version should have +30 expense accounts.

Step 3: Enter your opening balances in the “Open TB” tab (Trial Balance). 

Step 4: Journal entries go in the Gen Jnl tab. 
Journal references are simply whole numbers ("GJ" is automatically added). 
Dates MUST be within the accounting period on the Set Up tab. 
Accounts can be selected using the drop down box.
(Shortcut key   Alt  +   ↓ 
Post refs are entered automatically.
Enter Dr/Cr value. Formula are ok!  =)  

Hint: Avoid pasting over cells.
You can paste over the formatting and this can break functionality. 
Note that Paste-as-values will always work! 

 
2021-02-12_16-19-57.png
 

Step 5: Gen Ldgr tab is 100% automated. 
You can't edit it, but you can copy-paste from this tab to either MS Word or a new Excel spreadsheet. 

 Step 6:  The Trial Balance tab is also fully automated. 
You can copy-paste from this tab too!

Step 7: Reports are on Income Stmt and Bal Sheet. 
You can edit the accounts and the values that appear on the balance sheet and Income Statement. 

You can not add or delete rows. You can't change the overall format (again, an Excel limitation) 


Business Plan: How I make money

This system is mostly an advertisement for my YouTube channel.

It's also an advertisement to universities as I have made a version that will automatically generate individual questions for each student, and then automatically mark each student submission. I can mark more than a thousand assignments per hour using this system. Obviously, this requires I write custom programs, but still saves many thousands of dollars every semester. 

 

Squash all bugs!

Squash all bugs!

 

Change Log:

Version Alpha 0.3.35 - Current version

  • 1,000 Journal entry lines.

  • Expanded lines in each ledger.

  • Added GST & ABNs (uses GST clearing account rather than separate GST Paid and GST Collected).

  • Used colour to make it clear what areas are user editable and which are locked off.

  • Posts journal narrations to the ledgers.

  • General Journal: new column shows account type and if it’s increasing or decreasing.

Version Alpha 0.3.32 For YouTube - Current stable version

Version Alpha 0.3.31 - Public Facebook release

Known Bugs:

  • On Mac Excel complains the file is corrupt and will attempt to repair the file. The repaired file has damage to the number formatting $0,000.00, but otherwise works. The same error doesn’t occur on iOS, Web or PC.

  • Exel 2009 and prior has a #NAME error in the ledgers. Error noted in the introductory email sent out to testers.

  • Date in the heading of the General Journal is set to the start date and not the period end date.

 

Feature Wish List

A list of all the requested features :

  • NEXT VERSION: Journal narration should be posted to the GL. Currently accounts used but not the narration.

    NEXT VERSION: Make it more obvious which cells can be edited and which are fixed.

  • Add and delete row (or at least hide and unhide rows) ← requires VBA.

  • Bank CSV import feature + code up → automatically journaled and posted.

  • The ability to issue basic invoices

  • Stock cards (inventory list / menu + price lists)

  • A way to deal with perpetual versus periodic inventory.



Low importance items to be updated:

  • BAS automation

  • Wages and PAYG calculation
    Note: this ISN’T SBE approved software and so if the firm has wages, you should already be using real paid software.

  • Fixed asset register + automated depreciation calculation

  • Low value pool calculation

  • For small audits: materiality calculator and some testing workpaper



Feedback received from test users:

  • Format cells under Account name with Cell border

    • Not sure if this is 100% necessary

  • GL it is better to select account from list, then automatically show GJ for selected account with limit of 1000 rows rather than 40 rows

    • The Bank ledger is limited to only 40 rows because I'm not using VBA. Without VBA I can't dynamically add rows without breaking some of the formula in the background. For the commercial version of this software I'm going to go back to the VBA and I'll save the file as XLSB file. This a limitation of working in Excel.

  • IS should use sumif & include income tax?

    • Yeah, that's probably a good idea. The only downside to this is the fact that corporate tax is usually quite different to the corporate profit. Thinking back to my own companies I run, we do separate calculations for tax which then are journalised in the financial accounting software after the fact. This means we always just use a tax payable method as we are a private company and don't have to muck about with DTAs/DTLs as we aren't a reporting entity.

  • IS & BS should be redesigned and included comparable/opening balances from TB
    I really like this idea!!! =)

User 3 said:

1. I can't seem to add a Ref on the Gen Jnl Sheet. Everytime I type one it, it would throw a data validation error.

2. I would add a different shade of grey/ colour for entry fields. I feel like it took time exploring what can be edited (and the excel notice can get annoying - but that's an excel thing).

3. Are the P&L / balance sheet pages unlocked for a reason? I think the values could flow through from the other sheets.

4. I think the Narration should appear on the GL. You have the accounts used but not the narration.

Some potential advanced features:

  • 1. Bank account tab where you can import transactions & code them to the pre-defined accounts.

  • 2. Ability to expand the fields and add more accounts (I'm sure you've already thought about this)

Change log for version currently in development

  • GJ Reference now give useful feedback for the data validation

  • GST account added to chart of accounts

  • user to set the account type from the chart of accounts page. This will set the Dr/Cr rules which is important for depreciating assets and their respective accumulated depreciation. See the image below.