Speed Up Your Excel Workbook

Are your Excel Spreadsheets so big and slow that you could literally go and make a cup of coffee while the workbook updates?  Good news, there is a simple and quick solution to Speed Up Your Excel.

I recently had a request from a client to assist them with improving the speed of their Excel Workbooks.  While working on their data it was taking so long for their workbooks to update that it was seriously affecting their ability to deliver their financial reports.  Running the initial report took nearly 6 hours (they would run it overnight).  Each time new data was entered, it would take a couple of minutes for Excel to recalculate, equaling hours per workbook!  It doesn’t take an expert to see that this is not good for business.

It didn’t take me long to find the problem, there were far too many worksheets in a formula heavy design.  The formula used were old look-ups (e.g: Index & Match combination resolves quicker and uses less system overheads with more flexibility than the traditional VLookup formula) and too many nested IF statement variables.  They weren’t using List referencing, Named Ranges and there were hardly any Pivot Tables (a great way to quickly summarise and analyse data, again, using less resources).

Newer Business Intelligence report writing methodology using newer software functionality greatly assists with the ever increasing volume of business data sources that need analysing and help to Speed Up Your Excel

That being said, you don’t have to rewrite all your reports just because they’re ‘old school’.  Sometimes we just have to make do with what we’ve got.  Luckily for you there is another easy way that one can use to speed up your Excel Workbook even if you really have to make use of a nested formula-heavy design.

Speed Up Your Excel by setting your Excel Workbook formula calculation to manual. 

What that means is that Excel won’t update your formula until you tell it to.  This leaves you free to open your Spreadsheet, edit your information, and, when you’re ready, calculate your formula.

By changing this setting I brought the report run time at my client down from over 6 hours to just over 15 minutes.  They are now able to run the report, make edits, and, update their information multiple times in a day, something they previously couldn’t do.

Here’s how to set your Excel Workbook Formula Calculations to Manual:

Step 1:
Go to the Formulas Tab in your Excel Workbook Ribbon, then select ‘Calculations Options’

Speed Up your Excel Workbook 1

Step 2:
Set to ‘Manual’

Speed Up your Excel Workbook 2

NB: Don’t forget, now that Excel calculates your formula manually, every time you’re finished updating your spreadsheet or want to update your workbook, you will need to come back here and tell Excel to ‘Calculate Now’

Speed Up your Excel Workbook 3

Hopefully this tip helps to Speed Up Your Excel and make your life using Excel a bit easier 🙂

Brian

Error Upgrading SI Metabase

If you have recently upgraded to Sage Partner or Sage Xpress Version 14,  with Sage Intelligence  and you keep receiving the message “Error Upgrading SI Metabase” I have the solution.

Error Upgrading SI Metabase

Error Upgrading SI Metabase

Here is the solution to correct the “Error Upgrading SI Metabase” issue:

It is a registry setting that needs to change.  See below.  The server “Path” needs to change from C:Pastel14 to P:,  or the Mapped drive letter that is being used to map to your Pastel14 folder on the server.

You will find this key in the following location:

Click START – RUN and type in regedit
Browse to: Computer HKEY_LOCAL_MACHINESoftwareWow6432NodeSoftlinePastel Partner 14

Registry Entry

Registry Key Entry

“Right Click” on Path,  and select Modify 

Modify Registry Entry

Modify Registry Entry

In the “Value data:” field,  change the path.  Select the mapped drive that has been used on your workstation.

Modify Value Data

Modify Value Data

 

NB!  If you are not comfortable with editing registry keys, ask your IT Administrator to assist with this.

Until next time..

Steve

 

 

How to use the Business Intelligence Centre (BIC) for Reporting (Part 2) : Branch/Dept Reporting

I came across a company that had multiple store locations within their business and was setup using the Warehouse module for each store location. They had a Head Office and 3 retail sites. They were struggling to extract basic Income Statements per ‘Branch’, and, could therefore not get real time reporting per branch.

A simple solution to this would be to setup your COA (Chart of Accounts) to include the Branch setup, for example:

Main Account: Sales 1000
Sub Account: Head Office = 100
Sub Account: Retail Site 1 = 200
Sub Account: Retail Site 2 = 300
Sub Account: Retail Site 3 = 400
The GL Accounts would therefore look as follows:
Sales HO 1000/100
Sales Retail Site 1 1000/200, etc. and to follow this convention throughout the Income/Expense portion of your COA (eg: COS HO 2000/100, COS Retail Site 1 2000/200, etc)

Once this structure has been configured and your Inventory and Customer Groups integrated into the respective GL accounts, you’re now ready to build your report. Fortunately, BIC already offers a fantastic Financial Pack for exactly this kind of reporting: Management Pack. All we have to do is set a report Parameter to only filter on the respective Branch (Sub-Account) and we’re away.

1 Launch the BIC Report Manager

2 Create a New Folder “Custom Reports”

3 Right click on your current Management Pack Report and Export it to your desktop

4 Then Right click on your “Custom Reports” folder and select Import Report

5 Be sure to select “Pastel Evolution Auto Connect” as your target connection setting on the import screen (at the bottom) and be sure to give your report a Name that describes what we’re now doing (eg: Management Pack by Branch)

6 Select ‘No’ for the Container questions (this forces the creation of new containers, the reason for this is so that any edits we do on the new report, won’t affect the old one)

7 Once imported, double click on your new report, double click on “Union Sub Reports” and right click on the Trial Balance sub report and select “Go to Sub Report”

8 In the report View pane on the right hand side, select the Parameters Tab and Select ‘Add’

9 Select the GLSubAccount field, select ‘OK’

10 Select the ‘Equal To’ option, select ‘OK’

You can now run your report, select your Sub Account (Branch) and use the Management Pack to give you all of its Income Statement options by Branch, how cool is that!

This has of course got many other applications, it can be used for internal department reporting as well.

Post a comment or drop me an email if you want to chat about this.

Happy reporting 🙂

Brian

Our suite of Pastel Accounting related Professional Services include:
Business Consulting
Pastel Payroll Sales & Support
Pastel Payroll SARS Submission assistance
Pastel Payroll Outsourcing
Pastel Accounting Software Sales & Support
Pastel Accounting Software Implementation & Project Management
Pastel Accounting Troubleshooting & Datafixing
Excel Reporting
Training
3rd Party Application Development

E2E
End 2 End Business Solutions

One of my favourite quotes:

“Nothing will ever be attempted if all possible objections must first be overcome”
Samuel Johnson

How to use the Business Intelligence Centre (BIC) for Reporting (Part 1)

A thief was caught after stealing some paintings from the Louvre in Paris, when his getaway van ran out of fuel. Given bail at his first hearing, a reporter asked him on the steps of the courthouse how he forgot such a vital part of his plan.
“Simple,” said the thief, “I had no Monet for Degas to make the Van Gogh.”  (Ack CB)

I really enjoy this little story as it demonstrates the natural requirement for proper preparation or planning.  Something that is such a fundamental function which is so often not implemented on a daily basis, resulting in additional costs for corrective actions and much retrospective umm’ng and ahhh’ng .  So, how can we make this planning process fit into our daily lives, and, how can we get that information so that we can avoid as many ‘I’ll informed’ decisions as possible?  I’m glad you asked (well, you’re thinking it aren’t you… J ).  Softline have long known the importance of these decisions and the necessary tools to make them.  They’re so committed to providing their clients (you guys) with these tools, that they’ve purchased Alchemex, the company responsible for the creation of the Business Intelligence Centre module, and brought them ‘in house’.

Over the next couple of weeks, or maybe even longer (if there is a demand), I’m going to be writing a sequence of Blogs on ‘How to use the Business Intelligence Centre (BIC) for Reporting’ within your business, along with some tips on how to make it really work for you.

Today’s Blog will be about the Dashboard report.  It’s a standard report that ships with the BIC module, and, at the click of a button will give you a quick summary overview of your business, including:

1                     Your Profit & Loss at a glance (including Budget, Variance, &, YTD with variance)

  1. This gives you a quick look at whether or not your bottom line is in the red, or, the black.  And, you can see whether or not you’re on your projected YTD figures.

2                     Your Top 5 Expenses (including Budget, Variance, &, YTD with variance)

  1. Knowing your Top 5 Expenses with their Budgets and Variances gives you the opportunity to quickly spot anomalies in your companies spending.  The quicker any expense anomalies are spotted, the sooner they can be attended too.

3                     Your top 5 Customers & Customer Categories

  1. Knowing who your top performers are, or, which Category of client is generating your most income, is an important part of understanding your business.

4                     Your top 5 selling Inventory items & Item Categories

5                     Your bottom 5 selling inventory items & Item Groups

(click image to enlarge)

(the figures displayed in this report are from a demo company)

The information that is supplied by this report at a glance, will assist you with quickly identifying Key Performance Indicators (KPI’s) and identify area’s of concern, so that you can make the necessary decisions based on realtime information.

As with most reports, this Dashboard report can of course be customised to include other KPI’s within your organisation.

In my next Blog, I’ll cover the Management Pack (Income Statement & Balance Sheet) options.  I’ll even give you a couple of tips to customise it by Branch/Department 🙂

Happy reporting….

Brian

Our suite of Pastel Accounting related Professional Services include:
Business Consulting
Pastel Payroll Sales & Support
Pastel Payroll SARS Submission assistance
Pastel Payroll Outsourcing
Pastel Accounting Software Sales & Support
Pastel Accounting Software Implementation & Project Management
Pastel Accounting Troubleshooting & Datafixing
Excel Reporting
Training
3rd Party Application Development

E2E
End 2 End Business Solutions

One of my favourite quotes:

“Nothing will ever be attempted if all possible objections must first be overcome”
Samuel Johnson