The COVID-19 lockdowns that shut up shops and businesses earlier this year also brought cash-flow projection to the fore for many accountants. Financial modelling expert Lance Rubin, CEO of Model Citizn, uses these ‘power-lifting’ techniques in Excel to streamline these calculations.

1. Downsize files to .XLSB

Businesses that sell in high volumes can easily blow out the size of an Excel spreadsheet. If you are working with a large Excel file that is very slow and constantly crashes, Rubin suggests saving it in the .XLSB format.

XLSB – the ‘B’ is for binary – is Excel’s native, proprietary file format. It’s much faster than the .XLSX format, which uses the open source XML format. The Excel binary format creates a much smaller file size which is easier to save and open. One downside is that binary files can only be opened in Excel, not in Google Sheets or another program that handles XML.

2. Use Excel plugins to connect systems

The most powerful hacks have been built into Excel plugins such as DataDear and Modano. These tools, which are popular with corporate accountants, can automate account keeping and financial control in accounting software, including cloud programs such as Xero, MYOB and QuickBooks Online.

“The most powerful hacks have been built into Excel plugins such as DataDear and Modano.”

DataDear syncs data from accounting files into Excel and pushes back changes to the accounting software. You can post journals across multiple entities and overhaul a chart of accounts without needing to click in and out of a screen for each account.

Onboarding clients onto Xero can be done much faster using Excel add-ins because you can enter starting balances and a chart of accounts within the one screen, Rubin says.

Rubin uses Excel as middleware to send data from proprietary systems to accounting software.

“We have a new fintech client that runs their entire lending book on a system that has no API and no connectivity to Xero,” Rubin says. “We connect it through Excel and DataDear and push the financial information into Xero.”

Rubin created a data structure in the Excel file for the client and used formulas to transform the data before pushing it into Xero.

“We are doing an accounting team’s work by automating that process. We are refreshing the file and maintaining standards of sheet design to reduce the risk of error and manual work,” Rubin adds.

3. Add a tracking code

Another use case for these Excel plugins is updating tracking codes on transactions.

Rubin recently added tracking codes to report on the profitability of his consulting work at Model Citizn. He imported six months of invoice data from Xero into Excel to create a list of invoice numbers, along with the name of the client and the type of project for each invoice.

He then added a tracking code that included the client name and project type (training, model building, consulting) and pushed the updated data back into Xero. He could then see the profitability of recent work by project type and location.

4. Copy and paste data from websites

Another hack is useful in home life as well as work. You can copy (or ‘scrape’) text tables from websites and paste the data into Excel – use the text-to-columns function – and create a custom table of events. Rubin used this method to create a spreadsheet of match fixtures personalised for his son’s cricket team (he’s the team manager).

Power Query (inside Excel) can also be used to do some of this heavy lifting if the process is repeated multiple times.

A word of warning – make sure you double-check the results. “If you’re scraping information off a website and it’s not in a tabular format then you need to double-check it,” Rubin advises – a lesson he learned from experience.

He woke one Sunday morning to urgent messages asking why his team had failed to show for a game. “I had missed a space in the text formula, and my spreadsheet thought it was a bye and not a game at a venue,” he explains.

“No-one’s immune from errors. A spreadsheet doesn’t go out in my business unless it has been peer reviewed and error checked. But don’t assume you can’t make a mistake. If parents and children are going to rely on your spreadsheet then double-check it.”

Read more: