How to Quickly Change Orientation of an Excel Table

You may have a list of items going down a column.

For some reason you want to change the orientation to make it go across a row instead.

How can you do that?

It is quite easy actually.
Highlight all the items in the List and Right-Click Copy them.

Then go to any empty cell and Right-Click 'Paste Special...'
Note: Make sure that you allow for enough space for the row to go across. Otherwise your data may be overwritten.

Tick the Transpose Option.

VOILA! Your new list is ready.

Debtor Journal

There are times when you require to post a correcting journal against a debtor account. A common example would be to write off a small amount due to rounding difference or exchange rate difference. You might have a number of transaction lines against the debtor which can be considered 'settled'. Since you can't issue a receipt against it, the alternative way is to use Debtor Journal Program.

From the program, you can post balancing or correcting figures against the debtor and it will update both Debtor Subledger as well as the General Ledger. This ensures that your GL and Subledger remains in balance.

Debtor Ageing

Have you ever wondered why your Debtor Ageing figure doesn't look right? It is probably because you forgot to run the Debtor Month End Process.

Before you run this, you need to make sure that ALL your Debtor Statement has been saved or printed. This is very important since once you run the month end, you will not be able to reprint a back dated Debtor Statement.

After printing/saving all your Debtor Statement, all you need to do is to specify what is the first date of the next month for the Debtor Month End Process.

You can tick Archive Transactions if you want to get rid of all the fully paid lines in the Debtor Statement.

Once you have run the Month End Process, you will see that your Debtor Ageing Report would display in the correct ageing category.

Never Under Estimate the Importance of Bank Reconciliation

Like it or not, businesses MUST diligently run Bank Reconciliation reports at least on a monthly basis. There is no excuse not to do it. If your business deals a lot with bank transactions, it can be a tedious exercise to go through the bank statement and tick off every single line. However, this painful task pays off since it will give you an idea of where your money have gone to and whether you are collecting enough.

UNITS has a built in feature to assist with Bank Reconciliation. It can be a bit confusing to start off especially if you don't regularly process your Bank Statement. Trust me it is a worthwhile thing to learn and regularly monitor. For those with access to internet banking, you can spare 15 minutes a day to reconcile. This will save you a lot of grief as compared to doing it once a month.

The downside to not monitoring your Bank Reconciliation is too hard to swallow. I know someone who learnt the hard way. By 'assuming' that this is being done correctly, a company has lost a huge sum of money over a period of about 6 months!!! Bank Statements were not reconciled and neither were Debtors and Creditors.

There are many ways to beat a system but it is not easy to manipulate Bank Statements. So please monitor them closely and tie them up with postings in the system.

Excel: Ctrl + [SpaceBar] & Shift + [SpaceBar]

I recently found out about this amazing keyboard trick on Excel.

Select any cell on your worksheet.
Press Ctrl + [SpaceBar]
Excel will highlight the whole column!

Select a different cell on the worksheet.
Press Shift + [SpaceBar]
Excel highlights the whole row!

Press Ctrl+Shift+[SpaceBar] and Excel highlights the whole worksheet!

Neat!

Unable to Save As PDF in UNITS

If you face a problem where you are unable to save any reports as PDF, it is quite likely that your PDF Export Driver is corrupted. Take the following steps to fix it.

1.       In the run box type "%UNITS2%seinstall.exe" "Auto-IT SMARTExport Driver" –u

2.       Go to the Printer and Faxes folder in Control Panel and delete the Auto-IT SMARTExport Driver and Amyumi PDF converter if they are found.

3.       Reboot the PC - IMPORTANT STEP

4.       Delete the following highlighted dlls if they exist in the following folders (check for all these files in each of the folders, the last folder might not exist on the server):

C:\WINDOWS\system32

C:\WINDOWS\system32\spool\drivers\w32x86

C:\WINDOWS\system32\spool\drivers\w32x86\3

C:\Windows\System32\spool\drivers\w32x86\3\amyuni_amyunidocumentconverter400

5.       In the run box type "%UNITS2%seinstall.exe" "Auto-IT SMARTExport Driver"

6.       Test the PDF driver in Units/Equip again.

Sometimes, you can get away with just running steps 1 (to uninstall) and 5 (to reinstall). You may want to try these two steps first. If it doesn't work, you should run the whole steps in detail.

Cheque Printing Complete with Amount in Words

How many of us sometimes wish that we can print the words on a cheque instead of handwriting them. It would look more professional and not to mention a lot neater! This feature is normally available to some special bank clients with a piece of software and a special cheque book which can be fed onto a dot-matrix printer.

Most of us don't have the privilege to this piece of software and surely not the special cheque books but hey! I should be able to do that using Excel and Words right? We thought that it would be quite a straightforward thing to do until you actually try them for yourself.

There are a number of obstacles you need to overcome. The first being trying to come up with the right cheque size and more importantly trying to fit the details onto the right places. After a lot of blood and sweat, I manage to create a template where it would print nicely onto a standard piece of Malaysian Cheque.

The other obstacle is trying to figure out whether you can auto convert a number into words. Of course you can always manually spell ***One Thousand Two Hundred Thirty Four and Fifty Six Sen Only*** but wouldn't it be a lot cooler if you need to just input the number 1234.56 and it will automatically convert that into words!

After some research, I manage to get it to work using MS Excel.
Basically all you need to do is to populate an Excel Spreadsheet with the respective details such as the date, payee and amount. It will then be used to print onto the cheque.

As a bonus, wouldn't it be great if you can save the list of cheques you have printed instead of overwriting them. I'm sure that some auditor or accountant somewhere would appreciate this transaction list in order to reconcile with the GL.

All you need to do is to mailmerge this Excel with the Word Template. You can then pick and choose which record number to print out and keep adding to the list for as long as you want.

You can download these files from here, extract the files onto your own hard drive and try for your self. Start populating your list in Excel and use the Word document to print the cheques. You may need to tweak the document data source the first time you use this since it may still be pointing to my local drive.

The other thing for those using latest version of Excel, you may need to allow macros to run. Don't worry the macro in this Excel file is safe and comes from Microsoft themselves. It is required to autoconvert number into words.

If you are not familiar with mail merge or can't get the file to work, please let me know. I will try to assist you as much as I can.

Good luck and have fun with this!!

My First Love

Starting from today, I have decided to include another category to share in this blog.
It will be about my first love.....

I have been using this product for a long long time and to a certain extent, some say that I can't compute 1 + 1 without Excel. That is how dependent I am on this baby.

On the other extreme end, I have worked with Macros, Visual Basic and all sorts of commands to come up with solutions to be more efficient in my previous working life.

It can be very complicated at times but if you know which right buttons to push, you will have it under your spell and will follow all your commands quite willingly.

I love challenges and more so, solving the challenges using Excel. As long as the challenge can be put on a spreadsheet, I could possibly find you a solution!

Duplicate Contact Prompt

Since Patch 206033, there is a new feature in the Contact Maintenance to prompt user that you are potentially keying in an entry which could already be in the system. Whenever the user keys in the data in Surname or Company field, all the entries which carries the same value will pop up. You have an option to select it if it already exist or press cancel if it is a genuine new entry.

This is a very useful feature to minimise duplicate contacts in the system.

Service Recall Campaign

Workshop Managers, I am quite sure that you receive Bulletins or information from the manufacturer should there be any important improvements required for a particular vehicle or model. In UNITS, there is a function called Recall Campaign which helps you to alert the Service Advisor at the point of opening the Repair Order.

This prompt will pop up if the vehicle selected has an applicable campaign.

You can then choose the applicable campaign (if there are more than one applicable).

In order for this to work, you need to set it up via Recall Campaign program in Service>Maintenance.
Refer to your Bulletin from the manufacturer and specify all the applicable VIN numbers and date range.

Note that this works best if the VIN numbers are correctly inputted against the Vehicle. Any slight difference will result in the system not recognising the VIN.

TIP: You should also setup a Jobcode which matches the Recall Campaign Code so that the job can automatically be loaded onto the Repair Order if selected. This makes it quicker to prepare the RO. The Job is loaded with all the information you set in Jobcode Maintenance and it is automatically marked as Warranty Type Job.

Vehicle Sale Reversal

If you have made a mistake when invoicing a vehicle, there is a utility called Stock Unit Sales Reversal Process. In other words, you can reverse/cancel the invoice by raising a Credit Note against it.

If the menu does not exist, you may have to add it via Menu Maintenance.

Specify the invoice number that you want to reverse.
Tick Purchase Flag Confirmation Box.
Press REVERSE SALES.

Follow the instructions and you should be able to print a Credit Note. All the GL Postings related to this invoice will be reversed.

NOTE
This should work quite smoothly if you haven't processed anything against the stock unit after you invoice. If you go to Stock Unit Maintenance for the stock, look at the Cost Transaction Tab. There shouldn't be any Ticked Boxes unser 'After Sale Indicator' Column. This column indicates whether there are any transactions done AFTER you Invoice the stock. In this case After Sale = After Invoice (not to be confused with Aftersales).

If you have any After Sale transactions against this stock, you will have to reverse those first before you are allowed to cancel the invoice.

For example, if you have processed a PO against the stock after invoicing, you need to cancel the PO before you are able to reverse. Another example (which is more common) is if you have issued a payment/cheque against the stock after it has been invoiced, you'll need to cancel the cheque first.

If all these doesn't work, there is actually a menu parameter which allows you to reverse sales with an After Sale transaction.
The parameter is {AllowWithAfterSaleTrans=Y}

If you take this approach, you may need to manually post journals to completely reverse any outstanding balances.

Contact me if you have difficulty to reverse any invoices.

Financial Year End Reset

For companies whose Financial Year ends on December 31st, you should have run the Financial Year End utility by now.

Commonly you want to wait for full audit is done and get the auditors to sign off your accounts before 'closing the year end'. With UNITS, you have full authority on your accounts and should comfortably  run this utility. All you need to do is make sure that you have specified the right company code and that the next year end date is correct. Press RUN and you are good to go. It doesn't take more than a few minutes (sometimes seconds).

What this program does is that it closes each GL account for the previous Financial Year. Depending on the account type, if it should carry forward balances, it will post a carry forward balance in the new Financial Year. For P&L types they will be posted against Retained Earnings. You should have a smooth carry over across the Financial Years. 

Should there be any Audit Adjustments required after you have run this utility, you can simply post GL Journals Type 'Last Year'. The system is smart enough to post into the previous year and at the same time post the correcting entry either on Retained Earnings or opening balances for the new Financial Year. 

So if you have entered into a new Financial Year, you should run this utility NOW!

Longer Character Fields in CONTACT Table

Some of you will be pleased that fields Name, Surname and Company in CONTACT Table now allow more characters.


•  Contact Company Name – extended to Char 150 (from 60)
•  Contact Name – extended to Char 150 (from 30)
•  Contact Surname – extended to Char 150 (from 30)

In addition to that, the following are also extended:
•  Payee Name – extended to Char 150
•  Payer Name – extended to Char 150

Malaysian Service Tax Rate Change

This posting is applicable to Malaysia only.

The Service Tax in Malaysia use to be 5%. With the recent increase to 6%, users can update the system to reflect accordingly by changing the figure in SysConfig Maintenance (normally in Utilities Menu).

All you need to do is to scroll down to Service Tax Percent and update the Value in Decimal to 6.0000.

Press SAVE and restart your system for the change to take effect.

Service Invoice Reversal

Previously if you make a mistake on a Service Invoice, it is quite a hassle to make a correction. You would have to go through a few steps in order to raise a Credit Note against it. It is even trickier if you have components of Parts, Labour and Others on the Service Invoice.

In Build2060, a new program is introduced to make it easier for you to raise the Credit Note. It is called Service Invoice Reversal. You will have to add this program to the respective user's menu.

All you have to do is to specify the Invoice number that you want to reverse and press REVERSE. All the respective postings will be reversed accordingly. You can then reinvoice with the right details.

Just as an added control/security, the reversal can only be done by an authorised person and furthermore, it requires authorisation from the person who has access to Credit Override Administration program.

Specify the Invoice Number, tick Authorise and press SAVE. The invoice is ready for reversal. Easy!

Rebuild GL Master File

Regular housekeeping is good for your health!

One of the utility that is recommended for you to run is Rebuild GL Master File from the General Ledger> Utilities menu. (Note that this should only be run by the Accountant or an authorised person).

Select a 'Year End Date' and click SAVE. You will see the progress bar showing you the rebuild status. Run for all the available Year End Date. 

Running this utility ensures that your GL reports are up-to-date and in balance so that you don't unnecessary heart-attack when you discover that your figures don't tally. You can run this any time of the day, any day in the year.

This exercise should not take more than 5 minutes and it is good practice to run this on a regular basis.

Options> Options

If you have users who leave their desks often for a long time, they may be at risk of someone using the system while they are still logged on. To minimise this risk, you can set so that the system auto disconnects within the specified period of time.

To do this, you can go to the Options> Options menu.

From the General Tab, you can specify the number of minutes that you allow the system to be idle before it disconnects itself. There are also a series of other settings that you can do from this tab.

While you are there, you can also go to the Background Tab. Here you can choose your favourite image to be the background of your system.

Have fun personalising your system!

Stock Unit Maintenance Status Logic

In Stock Unit Maintenance program, there is a Stock Status area which commonly shows 4 types of statuses.
On Quote, Pre Sold, Sold and Swapped Out. If you notice, these statuses are not on VHSTOCK table but rather a set of criteria which produces the statuses.

Here are the logics:

1. On Quote

It requires the stock number to be present in VHQUOTEUNIT table. (Provided that it hasn't been sold or status changed to order).

2. Pre Sold 

This stock number needs to be on VHQUOTEUNIT table AND there is a date in 'Original Order Date' field in VHQUOTE table against the same Quote number.

3. Sold

The 'Salesdate' and 'Sales Inv' fields in VHSTOCK are populated with the respective info.

4. Swapped Out

There is a value in the 'Swap Out Ref' field in the VHSTOCK table.

p/s If you don't access the Database Tables, this posting might not make any sense to you. Ask your System Admin for details.

GL Reconciliation Report

One of the key reports to look at, especially to make sure whether your GL and Subledger is balanced. It is recommended that this report is viewed every start of business day so that you can take immediate action should you find any imbalances.

Previously the report will just show imbalances (if any). Now, a new feature is added to enable you to see a more detailed list of transactions which could have caused the imbalance. All you need to do is to press the [...] button next to the imbalance amount. 

Creditor Journal Entry

Imagine a case where you have processed a Creditor Invoice for an amount. When the time comes for payment, you noticed that the amount entered is not valid. Could be to due to a wrong amount entry or maybe you have negotiated for a discount for whatever reason.

Previously, you will have to either put through another Invoice Entry to adjust for the variance or perhaps you cancel the entry and post another one for the correction.

Now in Build 2060 you have another option on how to make the correction. You can use a new program called Creditor Journal Entry. This program is similar to Debtor Journal Entry where you can make an adjustment posting to an existing transaction.

To make use of this, you will have to add the program to your menu via Menu Maintenance.

System Calendar

Here comes another new year and to those whose Financial Year ends on 31st December, it is advisable to check your System Calendar Settings. Go to Utilities>Syscalendar Maintenance.

Make sure that you have the appropriate Financial Year End setup to reflect the new Financial Year.

TIP: To save future woes, you can setup for the following 2 or 3 years in advance.

Welcome to the Blog

  

This blog is created out of my love for Excel and UNITS. I will be posting some interesting findings which should benefit all users. If you have things to share, send them to so that I can upload for you. Alternatively, you can use the 'comment' box to share your views.

Visit the pages frequently to check for new postings or you can register as a follower so that you will be informed each time a new post has been uploaded.

Happy blogging and Happy New Year 2011!

View latest entry here.

p/s UNITS is a product by Auto IT. If you are not a user, the entries will make no sense to you.