Excel Tips

Excel Shortcuts that Will Speed Up Your Budgeting

Smart businesses learn from their mistakes, wise businesses learn from other businesses’ mistakes. According to Forbes, one of the top reasons why startups fail is due to poor financial management; spending too much or recklessly, not pricing products properly, lack of financing etc. However, hiring financial experts at the infancy stage of a business may also burn a big hole in the pocket. The good news is, many of these functions can be done with good ol’ Microsoft Excel. Not known to many, Microsoft Excel was designed to support financial management tasks like budgeting. This is why the popular software comes with inbuilt financial functions and shortcuts that are very useful for SMBs. You can easily start budgeting with these Excel functions and shortcuts:

Payment Function (PMT)

excel-01

Image credit: Allyworks

Business loans are sometimes necessary to fund costs of a new project or to purchase new equipment. It is important for SMBs to understand the different repayment schemes so it can work out the type of loan and the best way to repay it. As repayments are a form of commitment, it has to be included in the budgets and expense projections. To understand the different scenarios when taking out loans, make use of the Payment Function (PMT).

The formula: PMT (rate, nper, pv [fv], [type])

Rate: Interest rate

Nper: Total number of payments

Pv: Present Value

Fv: Future Value, which we assume to be zero because the loan is being paid off

Type: When payments are due; [0] for end of the month or [1] for beginning of the month

As interest rates are time and market sensitive, a good practice is to note down the date of calculation. Do this with the shortcut CTRL+; to enter the current date.

Pivot Table

excel-02

Image credit: Allyworks

excel-03

Image credit: Allyworks

Looking through hundreds of lines of information can be a big headache. Make use of the Pivot Table function to call out portions of the data. In a Sales worksheet, for example, a Pivot Table can easily pull out sections of information, to monitor outstanding payments from customers or analyse where most sales come from. The Pivot Table function can be found under Insert -> Pivot Table, proceed to select areas of required information. And to quickly select the entire PivotTable in the report, make use of shortcut CTRL+SHIFT+*.

Important note: when working with large numbers of cells, always remember to unhide hidden rows and columns with CTRL + SHIFT + (and CTRL + SHIFT + ), to ensure no information is left out.

Depreciation of assets

excel-04

Image credit: Allyworks

An item within the Balance Sheet known as Depreciation is a method of matching the cost of an asset to the revenue earned from using it, to determine how well a business has done in a given period. As it is difficult to establish specific connections, depreciation is always assumed to take place at a steady rate, over the useful life of the asset. Although this is usually left to the finance experts, with the help of Microsoft Excel’s DB function, SMBs can do-it-yourself. This formula also allows SMBs to calculate depreciation in different time periods, essential for budgeting and financial projections.

 

The Formula: DB (cost, salvage, life, period, ([month])

Cost: Cost of the asset

Salvage: Value of asset at the end of depreciation/ Scrap Value

Life: The number of periods (years) the asset will be used

Period: The period (year) we want to calculate the depreciation for

Month: An optional entry; it assumes 12 months in a year

When working on depreciation of multiple assets, swiftly switch between worksheets for costs information by using CTRL + PgUp to switch from right-to-left and CTRL + PgDn to switch from left-to-right.

Managing your numbers can seem daunting for new businesses, but it is extremely essential. With these handy tricks, you are now empowered to manage your finances with more ease on Microsoft Excel or Microsoft Excel Online. Microsoft Office 365 also allows you to get more work done in less time no matter where you are.

Also Read: How To File Income Tax In Malaysia?

Know anymore amazing tips in mastering Excel? Share with us at ‘Share Your Juices’! Or subscribe to TheBizJuice now to receive more awesome tips to increase your productivity and efficiency!