Advanced Forms Designer

Advanced Forms Designer

So the other day i was sitting and was asked, “I have multiple bank accounts and don’t want multiple layouts for my Invoices. How can you sort this out?” I immediately went into my work draw, slipped on my Superman….slippers…and got cracking. It was during the PJ Masks movie, with my kids, that it struck me with a fist pump how to do it, and I am about to Save the Day with you…

So step one was to create some user defined fields for the document. In this drop down version of user defined fields, I created the list of banks that was needed.

Advanced Forms Designer Bank UDF

Opening up the forms designer now, i went and inserted the fields where i needed them. now bearing in mind only one of the banks will print, i overlapped these on the actual layout.

Advanced Forms Designer Layout

I then used some coding, sorry I said Advanced Forms designer, not Advanced Advanced. But now comes the true test, i opened an Invoice and tested. First using the option of FNB as my bank. When the document printed this is the result (have included a snip of bank chosen too).

Advanced Forms Designer FNB

But the question now, will it work on Standard bank so here goes…

Advanced Forms Designer Standard Bank

 

Now you think what would happen if you added say Nedbank. Call me and I will do it for you.

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

Sage Evolution – Job Manager

With financial & payroll year-ends many of us will be away on (well deserved) holidays. We will however, still require reports and information about our businesses on a daily/weekly basis. There are a number of ways of doing this in Evolution, depending on the reporting platform you are using for Evolution reporting. If you are using the Evolution standard reporting functionality (as opposed to the BIC or other platforms), you can accomplish this very easily using the Evolution “Job Manager”.

The “Job Manager” is shipped as standard functionality with Evolution Core. It is located in the Maintenance…Common Menu.

Job Open

 

The Job Manager has the following features/functionality:

  • Multiple jobs can be created and multiple reports can be generated within each job.
  • Each job is created with it’s own run schedule which can differ from job to job. Jobs/reports can also be run on an ad-hoc basis.
  • There are a number of delivery methods that can be used for report output – each report can have different/multiple delivery methods – print, save to file, e-mail.
  • Multiple delivery formats are available, the most common being Excel and PDF, but JPG, TXT and RTF are available.
  • Multiple e-mail addresses can be specified for e-mail delivery.
  • Jobs and reports can be individually enabled/disabled.
  • All instances are logged.

Setting up a Report Job

Click on “Add Job”, select “Report”

Add Job

Name the Job

Name Report Job

Tick “enable” to activate the job and click on the “scheduler” button to set your schedule – follow the scheduler wizard.

Job Schedule

 

Adding a Report to a Job

Click on the “Add Report” Button. Select the Evolution report that you would like to schedule. In the screen shot below, I have selected the Accounts Receivable Age Analysis – you will need to lookat the “Report Class” column in order to identify the correct report where reports in different modules have the same name.

Add Report

Select the delivery method and output format

Job Report Delivery method

Click on the “Parameters” tab and click on the “Designer” button. The standard Evolution report parameter dialog will pop up – set your required parameters and select your layout required.

Job Report Parameters

When you click on OK, the report parameters will be saved and displayed.

Job Report Parameter Display

You can now preview the report, amend settings, output, etc.

Click on the “Save” button to save your Job and Reports. Add/Change/Delete/Enable/Disable Jobs and reports as required.

You are now good to go!

Points to Note:

  • The Job Manager must be open on the server/workstation in order for it to run – it does not load as a service.
  • Do not change the parameter values in the grid shown above – use the “Designer” button to change your parameter values.
  • If you are in an Offline Branch Accounting environment, you will not be able to run branch specific report from “Head Office” – branch specific reports will have to be run from the branch specific databases.

 

Happy Reporting…

Garth

 

Our suite of Sage Pastel Accounting related Professional Services include:

Business Consulting

Sage Pastel Partner Payroll Sales & Support

Sage Pastel Partner Payroll SARS Submission assistance

Sage Pastel Partner Payroll Outsourcing

Sage Pastel Accounting Software Sales & Support

Sage Pastel Accounting Software Implementation & Project Management

Sage Pastel Accounting Troubleshooting & Data fixing

Excel Reporting Onsite End User and Advanced Training 3rd Party Application Development

 

E2E

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