HOW TO USE EXCEL

The good presentation in accounting report is to show the simple formula and clear numerical conclusion, from left to right, showing all numbers and calculation fields to reach the conclusion. It is very helpful to divide sections in the dataset, calculation and overview of the situation, using different colours and fonts. Please make sure numerical formula such as commas and distinctions of currencies showing symbols ¥, £ and $ are appropriate.

The file name of your excel should be informative of what it is, you can have multiple names, such as project name (Shibuya Restaurant), what sort of analysis (Depreciation of kitchen equipment), period (Financial Year 31 December 2024), version (Second updates), in shorthand. This could be named as “Shibuya Kitchen Dep Dec24 v2.xlsx”.

You should use various shortcuts keys to save time. The following are very useful in excel work.

Ctrl+C Copy

Ctrl+V Paste

Ctrl+Y Redo

Ctrl+Z Undo

Vlookup: This functionality to extract particular data from the dataset is useful as the dataset can be utilised in many angles. The formula is constructed in the following: =VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, Approximate match (TRUE) or Exact match (FALSE)).

Pivot: This functionality allows to enter different criteria, compared to the VLookup function. The dialogue box is prompted.

Macro: Macro is a set of actions to automate tasks to save time and repetitive processes. The computer language used is VBA (Visual Basic for Applications).

Filters: The dataset can be filtered in different criteria. When you need to extract both of the debit/credit sides of transactions to check the posting, you should filter the dataset with unique transaction number of the journal or entries. If mis-postings are found, enter the words to represent the set of transactions such as suppliers or details to correct the other errors.