The Wonders of Microsoft Excel’s 3D Formulas

Excel’s 3D Formulas Work Across Worksheets

Excel formulas online training courseWe’ve recently updated our Microsoft Excel Training Course content, because it remains one of the most indispensable tools for small businesses — and we’ve included a new exercise workbook which takes you through all the steps involved in developing a financial forecast for an investment.

We used a granny flat project as the case study in our financial forecasting workbook, but the beauty of Excel is that, once you get the formulas right, they can be replicated for any kind of investment, not just one for building a granny flat.

The wonderful 3D formula

3D formulas are one of the other many wonders of Excel. A 3D formula is basically a reference to the same cell or a range of cells within multiple Excel sheets. They’re a convenient way to reference several worksheets that follow the same pattern, with cells that contain the same type of data.

All functions work with 3D formulas

3D formulas can be used with all Excel functions — SUM, AVERAGE, PRODUCT, etc — which means that, using a 3D formula, you can easily create a financial forecasting sheet for any stage of an investment, and easily reference that data in another financial forecasting sheet. This is invaluable because there are very few projects or investments that can be contained in just one Excel worksheet.

A project like the granny flat case study in our online Excel Training Course, contains many moving parts — there’s the initial construction, then there’s the ongoing maintenance, and the rental income to manage. It would be impractical to keep all of this information contained within the one Excel worksheet.

3D formulas can be modified with time

The best part about using 3D formulas is that you only need to specify the start and end sheets (which for ease-of-use, you could just label ‘start’ and ‘end’) and the formula will reference all the worksheets between the start and end sheets, including those two sheets.

This means that, as your investment or project grows, once you’ve got the 3D formulas set up correctly, you can just add and subtract worksheets as necessary, and the calculations will update automatically.

***

Once you understand how to create and work with 3D formulas, you can use them for any project or investment that you create a financial forecast for — be it for a granny flat project, business investment or anything else that requires you to make a financial decision.

Visit our website for more information on our Microsoft Excel Training Course, which covers 3D formulas.

***

online bookkeeping courses to earn cpd points

EzyLearn Excel, MYOB and Xero online training courses count towards Continuing Professional Development (CPD) for bookkeepers and accountants. We’ve been an accredited training provider of the Institute of Certified Bookkeepers ever since the organisation started in Australia. Find out how CPD points can be of benefit to you.

Use Excel to Manage ALL Your Investments

Once You Master Excel You Can Do Anything!

using excel for financial forecast granny flat2
Excel is one powerful tool. Its formulas can be extrapolated to most any accounting software program.

In our Excel training Course there’s an exercise workbook on granny flat investments, which takes you through all the steps involved in developing a financial forecast for a granny flat. It can also be used to determine the financial forecast of any investment, not just granny flats.

Even though lots of cloud-based software applications have come along in recent years — Xero and MYOB and CRMs like Zoho — which have made it possible for lots of business owners to keep track of their financials and customer sales history without ever needing to open an Excel spreadsheet, Microsoft Excel is still the software used by accountants and analysts in nearly every profession.

Excel is flexible

Microsoft Excel may be difficult to master — indeed, it’s probably one of the most difficult in the entire suite of Microsoft programs — but it’s also the most flexible. The formulas sitting behind nearly every cloud-based accounting software can all be replicated and modified in Excel, where in MYOB or Xero, they can’t.

You can’t enter the details of your granny flat project or other investment into Xero and MYOB and create a financial forecast, even though near similar formulas are being used each time you run a profit and loss statement.

Cloud software is Excel plus PLUS

Before computers and Microsoft Excel came along, accountants used a pen and paper to keep track of their clients’ business financials. And before that, before the numeral system was invented, the abacus was the main accounting tool used by merchants and traders to keep track of their finances.

You probably have no use for an abacus anymore — although in some parts of Eastern Europe and Southeast Asia people still do — because, as an accounting tool, it’s too basic. But the same cannot be said about Excel, which is capable of handling complex formulas and rendering an answer.

Export accounting data into Excel

Because cloud-accounting software is essentially a very user friendly version of an elaborate, however inflexible, spreadsheet, it means you’re able to export your accounting data into Excel. Financial analysts and accountants do this when they need to carry out further analysis on a client’s financial data, and you can too.

Once you know how to use Excel for financial forecasting, you can use the same formulas and modelling for any financial forecast — be it for a granny flat project, business investment, anything that requires you to make a financial decision.

***

Visit our website for more information on our Microsoft Excel Training Course, with its new intermediate-level ‘Granny Flat Case Study’.

***

online bookkeeping courses to earn cpd pointsEzyLearn Excel, MYOB and Xero online training courses count towards Continuing Professional Development (CPD) for bookkeepers and accountants. We’ve been an accredited training provider of the Institute of Certified Bookkeepers ever since the organisation started in Australia. Find out how CPD points can be of benefit to you.