everydayExcel Newsletter - 6 Jan 2009

Tracking Sales Forecast with Excel

As person in charge of managing the monthly sales forecast of the company, you are required to regularly request the sales people to enter their revised sales numbers into Excel worksheet. After the sales people have updated their forecast, you have to identify the changes made to the existing forecast and asked the relevant sales person to explain the changes if it turns out to be significant. You find that the current method of comparing every number against the existing forecast too tedious and time-consuming, and wondered if there were any way to make the cell(s) change colour when made changes are made to the forecast.

Our recommendation is to use Conditional Formatting to track number changes. Instead of asking the sales people to highlight the cells when they make changes to the existing cell, Conditional Formatting can make the cell automatically change color when the numbers are updated. Sales people don't have to deliberately highlight the cells, an action which may be easily forgotten. Under the normal uses of Conditional Formatting, the existing forecast must be kept within the same worksheet as the input table. It is not possible to compare the values in the cell against other values found in another worksheet. But with Name Range, this limitation can be overcome. Click on this tracking update page to find out how to do this.

Another solution is to use the readily available Highlight Changes function in Excel to track the changes. It allows you to view the changes made from the previous value. Only the last change will be recorded though. This means that you will not be able to see the original value if someone made two changes to the same cell.

You can find out how to implement both methods in this tracking updates page.

Members can log in to our Microsoft® Excel Online Course page to watch the video.

The Importance of Budgeting

Corporate Budgeting is an activity rigorously executed by most Multi-National Companies (MNCs). It is important to conduct Budgeting exercise to find out how the subsidiaries are performing on quarterly, semi-annually and annually, and know how each of the subsidiaries should focus their efforts in the next period.

Likewise, personal budgeting is also very important. It will help you identify what you are spending on and when the money is spent so that you have full control of your expenses. This is even more critical as the current global financial crisis continue to unfold.

For the past 5 years, I have been preparing the budgets for my family and business and monitoring how we are managing our expenses month after month. The budget helps me review my income and expenses on a month to month basis against the goal I have set at the beginning of each year. This has helped us save more for the 5 years compared to the last 10 years before I started my budgeting habit. Budgeting provides us ideas and directions on our saving plan for each year.

I am working on a budgeting template that will use part of a secret formula that I taught in my Corporate Budgeting course. This template will help you manage your budget with ease when it comes to comparing your actual with your budget. If you are keen to obtain a unrestricted copy of the budgeting template when it is launched, all you have to send an email title "personal budgeting" with the answers (min 20 words) to the following 3 questions to everydayexcel@synergyworks.com.sg:

1. Do you have a personal budgeting plan?

2. Why you think budgeting is important?

3. What is one thing you hope you could do better with your own current budgeting template?

Opening Excel 2007 using Excel 2003

What happens if you are using 2003 and somebody send you a file that was created in 2007? Other than asking them to save the file in a lower version or spend money to upgrade to the new version, there is another option which will cost you nothing. Read our new section on Learning Microsoft Excel 2007

On the Lighter Side