Menu:

Shortcuts

The below are some shortcuts you may want to explore when manipulating data.

The use of F4 in a cell.

In the below example function F4 was pressed when B4 was highlighted in the cell. This places $ signs around B. What this does is that it fixes the reference point to B4 when you copy and past the formula in B2 to another cell. (Try pressing F4 twice or three times).

In the above example the F4 function helps placing the $ sign quickly around the B, so that I can copy the formula to B3 and pro rata the commission.

Get to know the control + short cuts

For example:

Control + C = copy

Control + V = paste

Control +  - = delete cells or rows.

Accounting Short Cuts

Manipulating Trial Balances

Accountants are usually dealing with trial balances that are downloaded from a system.

This data is then manipulated to created statutory accounts, or reconciliation files, audit files, management accounts, etc.

Consider creating a macro to manipulate this data every month, as this is usually the same routine month by month. You can automate reconciliaton sheets, group accountants to stat accounts via a mapping table, and other methods.

Structuring your Excel models

Think about how you want to structure your model. You may want to keep all input data that is the same on one data input sheet. This helps other users to only use a specific sheet to enter data. For example, storing all exchange rates, on an exchange rate tab.

Separate the data input from the workings from the results with a different tab.

Manipulating month reports

If you are receiving monthly reports that you constantly need to manipulate. Again, think about creating a macro, if the source is usually in the same format. This will save you a lot of time.

Insurance Short Cuts

Data is usually analysed by underwriting categories such as premium, commission, claims, and by classes of business, distribution channels, and area. Consider storing all this data on the same spreadsheet. You can then pivot table the data to allow for greater analysis.

Reinsurance Short Cuts

Trying automating earning patterns, with the 24th method or other methods. Quota share, surplus treaty programs can be analysed with pivot tables, to ensure that allocation percentages are correct. 

 

 

 

 

 

 

 

 

 

Click here to send us your request, and any attachments..