Are You Fully Aware Of The True Flexibility Of Excel Formulas? (Number 2)
Susan was bending over Fred’s desk the day after she had learned all about relative and absolute formulas, explaining to him exactly how to operate the Fixed Asset Depreciation spreadsheet she had amended the previous afternoon and into the evening after dinner at home. She also showed Fred how he could copy a lot of the details he had already entered into the original spreadsheet, so he could avoid re-typing the names of individual assets and other information he had worked on the previous day.
Susan was just about finished with Fred, confident that he knew what to do, when Stella approached from the direction of her office.
‘How did you get on with your spreadsheet?’ Stella asked.
‘Are you sure I can leave you now?’ Sue asked Fred. ‘Do not hesitate to come over and see me if you have any questions.’
‘Thanks, but I think I will be all right now,’ Fred answered. ‘It all looks straight forward enough’.
‘Okay, good,’ Sue continued. ‘Stella, could I ask you about something?’
‘Sure, it will be easier if we go back to your desk.’
Sitting in front of her computer, which had a copy of her revised spreadsheet on the screen, Susan started to speak to Stella, who was sitting beside her on a chair she had taken from a nearby empty desk. ‘Working out the formulas with the Dollar signs in the right places was easy enough, thanks for that, but I have realised that I have another problem,’ Sue said.
‘And what is that?’ Stella inquired.
‘Well you know that there are two ways of calculating Depreciation, the Straight Line Method, and the Reducing Balance Method,’ Sue said.
‘I’ve known that since you were going to school in short heeled shiny black shoes and short white socks,’ Stella laughed.
Susan continued. ‘Well I know that the client Fred is working on uses the Straight Line Method, and that is the way I have worked out the formulas, calculating the Depreciation on the original cost of each Asset. However what happens if another of his clients, or a client of someone else in the department, uses the Reducing Balance Method, which calculates the Depreciation on the Written Down Value of each asset? Do I need two separate spreadsheets, with slightly different formulas?’
‘Some of our clients, not many, but a few, use both methods, different ones for different classes of asset. Having only two versions of your spreadsheet would not solve that problem,’ Stella remarked with a smile.
‘Well that only makes the situation even more complicated. How can I design a universal Fixed Asset spreadsheet when there are so many variables,’ Susan said with a puzzled look on her face.
‘Let me see if I can perform some magic,’ Stella said with a smile as she turned Susan’s screen towards her slightly so that they could both easily see it. Pulling the keyboard and the mouse towards her, Stella continued. ‘I see that you have put the Depreciation Rate variables in cells in rows by themselves under each category heading. That is okay, but they do not stand out there very well. It is better if variables have a column to themselves. Column ‘A’ is the best choice.’
With quick, keystrokes and clicks of the mouse, Stella created a new column ‘A’, which moved everything else one column to the right, made it slightly narrower than standard and cut and pasted the cells containing the variable depreciation rates into the new column ‘A” one row below where they were originally. She then put a yellow background in the new cells containing the variables and surrounded them with a thick black border, before she deleted all the rows which had contained the original cells for the Depreciation Rates.
‘I could have done all that, but at a quarter of the speed,’ Susan remarked.
‘You will get quicker with time,’ answered Stella. ‘However the most important thing to learn from that procedure is that I cut and pasted the cells which will hold the variables, rather than copying and pasting them, and then deleting the originals. Doing it the way I did means that all of the formulas that referred to the original cells will now, automatically, refer to the new ones, so you don’t need to go back and change the formulas.’
‘Cool,’ said Sue.
Stella returned to the keyboard and mouse. She clicked on the letter ‘A’ at the top of the new column and clicked the centre and underline icons. This would mean that everything entered in column ‘A’ would be centred and underlined. Two rows above the first yellow cell she entered the letters “Dep.” and immediately above the first yellow cell she entered the word “Rate”. She then copied these two cells to the cells above all of the yellow cells associated with each category of asset.
‘I am not finished yet, give me another minute or two,’ she said to Susan.
In the cell below the first yellow cell she entered the word ‘Method’ and below that she entered ‘SL/RB’. Under that she created another yellow cell with a black border.
‘I could have copied the original yellow cell there, but you will have formatted that as a percentage, so I would have had to change the formatting to text, so it was quicker just to do it the way I did. Now all I need to do is copy those last three cells to the other categories and we are ready for the fun to start,’ Stella remarked. She was enjoying showing off her skills instead of working in a lonely office all day.
‘You mean the fun hasn’t started yet,’ Susan gasped. ‘I would have thought that we had already had enough fun for one day.’
Stella finished the copying task before asking Susan, ‘Sue do you know how many different kinds of formulas there are in Excel?’
‘I do not know but there is a lot,’ Susan replied. ‘Our instructor showed us the list, although I cannot remember how he accessed it. However he said that accountants only needed a few and that he was only going to cover ‘=sum()’.
‘Well,’ replied Stella. ‘There are over 500. I do not understand the vast majority of them, and your instructor was right. We only need about a dozen. The one I am going to teach you now, the one that will solve your current problem is ‘=if’.’
‘Okay, I am listening,’ said Sue. ‘I am enjoying this, and I want to get as good as you are. What does ‘=if’ achieve?’
Stella fished a pen from her pocket, grabbed a notepad that was on the desk, and wrote ‘=if(equation,true,false)’.
‘That is it,’ Stella said. ‘You give Excel a condition, normally in the form of an equation, followed by a comma, then you tell Excel what to do if the condition is true, or the equation is correct, followed by a second comma, followed by what Excel must do if the condition is untrue, in other words if the equation is incorrect.’
‘Sounds complicated,’ Susan said. ‘How will it help to solve my problem?’
‘Have a look at the spreadsheet,’ Stella continued. ‘We’ll put some dummy figures in the first asset of the first category, and write them down as well.’
Stella entered some numbers into the spreadsheet and then turned her attention to the pad of paper, where she wrote down the following:
* Depreciation Rate – (A$4) = 10%
* Method – (A$6) = SL
* Original Cost – (C4) = $100
* Opening Accumulated Depreciation (D4) = $10
* Opening Written Down Value – (E4) – $90
* Depreciation formula in column ‘F’ starting at ‘F4′ =IF(A$6=”SL”,C4*A$4,E4*A$4)
‘That is it, Sue,’ Stella said after she had finished. ‘It is as easy as that. The user puts either ‘SL’ for Straight Line or ‘RB’ for Reducing Balance into the coloured method cell for each category. In this case it is cell ‘A6′. If ‘SL’ is entered into ‘A6′ Excel calculates the Depreciation in column ‘F’ by using the formula after the first comma, which is the original Asset Cost multiplied by the Depreciation Rate. If cell ‘A6′ contains ‘RB’, or anything else, for that matter, Excel calculates the Depreciation in column ‘F’ by using the formula after the second comma, which is the Opening Written Down Value multiplied by the Depreciation Rate’.
Stella quickly typed the formula she had just written down on paper into cell ‘F4′ and $10 ppeared as the answer. ‘See Excel has correctly worked out the depreciation as $10, being 10% of $100,’ Stella stated. ‘And if we scroll across to the columns further to the right, we will see that the Closing Accumulated Depreciation is stated as $20 and the Closing Written Down Value is $80.’
Stella then scrolled back to cell ‘A6′ and changed the ‘SL’ to read ‘RB’.
Stella continued. ‘Now cell ‘A6′ does not contain ‘SL’, Excel uses the formula after the second comma which is the Opening Written Down Value times the Depreciation Rate. See, cell ‘F4′ now reads $9, which is 10% of $90, the Closing Accumulated Depreciation is now $19 and the Closing Written Down Value is $81.’
‘Hey, that is brilliant,’ Sue said. ‘The user has full control of how Excel calculates Depreciation for each class of asset. And it’s easy enough that even I understand it.’
‘One day I will show you how you can use the ‘=if()’ formula to design a cash book that resembles an old fashioned cash book with different columns for different categories of expenditure,’ Stella continued. ‘However now I will leave you to copy the formula down column ‘F’ to the last row you have allowed for assets in the first category. You will notice that I have put the Dollar signs in the correct places to ensure that all the formulas relate to cells ‘A4′ and ‘A6′. Then you will have to copy one
formula to the first row in the second category, edit it so that it refers to the correct cells for the Depreciation Rate and Method for the second category, and so on. Can you manage that on your own?’
‘Yes, no probs, Stella. Thanks for what you have taught me today.’
‘No probs Sue, that is what I am here for,’ replied Stella as she got up to head for the kitchen for a well-deserved cup of coffee.
Phil Ramage CA (Scot) CA (Aust)
For more information and, if needed, assistance, go to:
www.philipramage.com
Phil also has a very informative and active blog at
href="http://www.phil-ramage.com">www.phil-ramage.com
(C) 2012 Philip Ramage All rights reserved
Are You Fully Aware Of The True Flexibility Of Excel Formulas? (Number 1)
Stella, the manager of a section within one of the major accounting firms happened to be passing young Fred’s desk on her way to the coffee machine. Fred was the most junior member of her team.
‘Stella, excuse me but how do you edit the contents of a cell in Excel,’ Fred asked just loud enough so that she could hear.
‘Well there are two main ways,’ Stella said as she wondered round Fred’s desk so she could see his computer screen. ‘With your cursor pointing to the cell you can press the F2 key, or you can edit the text by clicking in the area that covers most of the row just below the icons at the top of the screen. Why do you want to know?’
‘This pro-forma fixed asset spreadsheet I have is depreciating the category of assets I am working on at 20% but Pete my supervisor wants these ones depreciated at 15%. So I have to go throgh all of the formulas and change ‘.2′ to ‘.15,” Fred replied. ‘Who designed the pro-forma spreadsheet?’ Stella asked.
‘Susan designed it just after she came back from her three day Excel course, the one I am due to attend next year,’ Fred replied. Susan was sitting at her desk only a few metres away.
‘Sue, can you come over here for a minute, please,’ Stella asked, looking in Sue’s direction.
‘How can I help?’, Sue asked as she approached Fred’s desk.
Stella continued. ‘Sue, at your course, did the instructor not tell you to provide specific cells for users to put things that could vary? Things like depreciation, gross and net profit percentages, and many more variables that could crop up in spreadsheets designed for various different purposes. If you do that, and include the address of the variable in formulas where necessary, the user only needs to change the contents of one cell and all relevant formulas will automatically be adjusted, thus saving the user a lot of work. In addition a user with no detailed knowledge of Excel will be able to tailor the spreadsheet to suit their individual requirements.’
‘Yes, I remember him telling us that,’ Sue replied.
‘Well, why haven’t you done that here, in the case of the depreciation calculations?’
‘When I copied the formula down the page, the address of the cell containing the depreciation rate changed and so, after the first one, the formula did not work. I reckoned that it would be easier for the user to change the rate in the first formula and for him or her to copy the new formula down the page. The formula would work all the time then.’
‘And what if the user did not know how to edit the first formula or how to copy it down the screen?’ Stella asked. ‘Fred was planning to change every individual formula.’
‘I guess I did not consider that possibility,’ Sue replied with a sheepish grin.
Stella continued. ‘Did the instructor mention ‘relative references’ versus ‘absolute’ and ‘mixed references’?’
‘I think so, but I did not really understand that bit.’ Sue replied with a grimace.
‘Fred, give me a sheet of paper,’ Stella said, forgetting all about the coffee she had been on her way to get. Fred did as he was told and Stella produced a pen from somewhere before scribbling down four cell addresses. They were ‘A1′, ‘$A1′, ‘A$1′, and ‘$A$1. Beside ‘A1′ she wrote the word ‘Relative’, beside ‘$A1′ she wrote ‘Mixed’, she did the same with ‘A$1′, and beside ‘$A$1′ she wrote ‘Absolute’.
‘Right,’ she continued. ‘The first address, ‘A1′ is the simple column and row address that beginners are taught in their first lesson. If, say cell ‘C3′ refers to that in its simplest form. ‘=A1′, whatever is in cell ‘A1′ will appear in cell ‘C3′. If the formula in cell ‘C3′ is copied to cell ‘D3′ the formula that appears in cell ‘D3′ will change to ‘=B1′, and whatever is in cell ‘B1′ will appear in cell ‘D3′. If the copying process is continued ‘E3′ will equal ‘C1′, ‘F3′ will equal ‘D1′ and so on. Similarly if cell ‘C3′ is copied downwards, ‘C4′ will equal ‘A2′, ‘C5 will equal ‘A3′ and so on. This basic formula in cell ‘C3′, ‘=A1″ is called a ‘relative reference’.’
‘However you can stop both the row and the column changing by putting a Dollar sign in front of either the column letter or the row number. This can be achieved both by directly typing in the Dollar sign or signs, or by toggling between the four possibilities by pressing ‘F4′. Both ‘=$A1′ and ‘=A$1′ are called ‘mixed references’.’
Stella carried on. ‘If ‘=$A1′ in cell ‘C3′ is copied to the right, all of the cells it is copied into will read ‘=$A1′ and whatever is in ‘A1′ will appear in all these cells. However the row number has not been held, and so, like in the example of the ‘relative formula’, if the contents of cell ‘C1′is copied downwards,’C4′ will equal ‘A2′, ‘C5′ will equal ‘A3′ and so on. Similarly if ‘=A$1′ in cell ‘C3′ is copied to the right, the cells it is copied into will read ‘=B$1′, ‘=C$1′ and so on, just like in the example of the ‘relative reference’.
However, this time, the row number has been held, and so, if the contents of cell ‘C1′ is copied downwards,’C4′ will read ‘=A$1′, ‘C5′ will also read ‘=A$1′ and so on, and whatever is in ‘A1′ will appear in these cells.’
‘Now it must by obvious what is achieved by the ‘absolute reference’,’ Stella carried on. ‘Can either of you guess?’
”=$A$1′ will appear in all cells ‘C3′ is copied into whether it be to the right or downwards,’ Sue suggested with a grin.
‘Good girl,’ said Stella. ‘And don’t forget this also extends to copying to the left and copying upwards,’ she added. ‘Now Fred do you have any other work you can do for the rest of the afternoon?’
‘Yes, plenty,’ Fred replied.
‘Well leave your spreadsheet until tomorrow. Sue, I want you to re-do your fixed asset spreadsheet, if necessary taking it home, so that tomorrow, Fred can fill in the details without adjusting any of your formulas; understood?’
‘Yes,’ the two trainees said in unison.
Stella left them to get organised. Goodness I really need that cup of coffee now, she thought to herself, as she headed towards the kitchen.
Phil Ramage CA (Scot) CA (Aust)
For more information and, if needed, assistance, go to:
www.philipramage.com
Phil also has a very informative and active blog at
href="http://www.phil-ramage.com">www.phil-ramage.com
(C) 2011 Philip Ramage All rights reserved
Get A Head Start With Access 2010 Templates
Access 2010 offers a wide range of pre- built database templates. There are twelve installed locally on your computer with Access 2010 and there are twenty six more available to download from Microsoft under various subject headings. This article looks at how to create two of these, one for local use and the other for web use.
To view the templates, launch Access 2010, choose the File tab and select New to display the Available Templates in the right hand panel. This panel has its own toolbar to help you navigate between templates. The Home button takes you to the templates summary screen where you can see in the top row different kinds of templates and in the lower rows the templates available online for download.
To show local templates on your computer, click on Sample Templates in the top row. This will show you twelve templates. Seven of these can be created locally or on a network and all allow multi user access at the same time.
Five of these local templates are web templates for use only with Sharepoint 2010 server. Sharepoint is used in larger organisations to allow Access 2010 web databases to be uploaded to a Sharepoint web hosting service. By setting user permissions you can allow online multi user access at the same time. If you don’t use Sharepoint you cannot upload these web templates, but you can build them locally as a learning exercise.
How to use an Access 2010 local database template
The Northwind template is very popular because it is a fully built database complete with example data. It’s also very popular in many training courses because it also contains working examples of many Access 2010 features. We’ll show you how to build a database from this template.
First click once on the Northwind template to select it. Then choose a filename and a location to save the file. To do this click in the filename box on the far right of the screen and change the filename if you wish, but leave the extension as .ACCDB. Then click on the yellow browse icon to the right of the filename box, navigate to a folder where you want to save the file and click OK. Back in the Templates screen click on the Create button on the far right to create the database. Note that in Access 2010 we create and save the database before we start using it.
Once the Northwind database has been created, Access 2010 opens it. Because of new security restrictions in Access 2010, some database functions which could potentially contain a virus or malicious code are blocked when the file is first opened. If you are sure the file is safe to use, which our new example is, click on the Enable Content button at the top of the screen.
A Northwind login screen appears – click on the Login button to open the database. Then on the left hand side on the upper part of the Navigation pane click on the right facing double arrow to fully open the Navigation Pane.
You can stop Access 2010 blocking databases by saving the created database files to a trusted folder, for example on your desktop. Then in the Access 2010 Trusted Zone settings add this folder. Once this is done, Access 2010 will open any database from this folder without blocking any features.
To return to the Available Templates screen just close your current database. Note that normally Access 2010 can only open one database at a time.
How to create an Access 2010 web database template
We’ll build the Assets web database as an example. Once built, the database would be updated to a Sharepoint, but we’ll only cover building the database in this article. To begin ensure you’re in the Home screen of Available Templates, then choose Sample Templates. Now select Assets web database, and on the right change the filename if you wish, and set the save location again, then click on Create to build the database.
The database is created and opens with a Login screen, showing that the database has no users. All Access 2010 web databases need users, so we need to create a test user to open the database. Click the Create User link on the bottom of the Login panel and enter any name, for example Homer Simpson. Once added you can select Homer Simpson and login to the database.
The database opens with a Getting Started page which contains one or more tutorial videos and links to online help pages. To show the videos you will need to download and install Microsoft’s free Silverlight utility (similar to Flash player). The online help pages and training videos are excellent sources of guidance in building Access 2010 databases in general, not just web databases.
The web databases have pre-built tables, forms and reports but no data, and no table relationship links. These links are added once the database is uploaded to Sharepoint.
Summary
Access 2010 has a wealth of database templates to choose from, either locally installed along with Access or available online. You can create regular Access databases or web based databases (which are used with Sharepoint server) following easy to follow prompts on the Available Templates screen. To learn much more about Access 2010 and explore its many features why not consider attending a training course. This can help you gain many key skills in a short time.
Author is a freelance copywriter. For more information on database development in london, please visit http://www.microsofttraining.net
Document Management Improves Customer Service
A quality document management company provides software and hardware solutions that fulfill record management needs for a variety of industries including government, healthcare, financial, manufacturing and more. Document management companies provide these industries numerous benefits including streamlined efficiencies, decreased costs and perhaps most importantly improved customer service.
Providing memorable, high-quality customer service is extremely important to a business. Quality customer service equates to customer retention and without exceptional customer service your customers will not return to your business. Furthermore, average customer service will not necessarily lose you client but it will not help you build and customer base and poor customer service will ultimately drive customers away.
High quality customer service is directly related to client satisfaction. If your client is not satisfied with your customer service they are likely to look elsewhere. In fact, recent customer service analysis indicates that it is important to please your customer, not simply satisfy them if you wish to retain their business.
Understanding the importance of quality customer service is important to building quality client relationships and growing your business. Your business needs customers to operate and if you provide quality customer services you are likely to retain your current customer and attract new ones. The remainder of this article will discuss how document management can help you improve your business’ customer service.
Businesses of all sizes can use document management to automate manual paper-based tasks. Automated processes will allow your business to expedite customer orders and allow you to meet or exceed expectations. Document management also allows business to increase efficiencies by allowing them to distribute information to their clients via e-mail or an online portal, which reduces your time to market.
Document management also allows you to have quotes, orders and invoices at your fingertips in one central system which gives you the ability to respond to clients faster and with more accurate information. Using scanning and document management/workflows also provides employees and clients instant access to current and historical information that is often spread across multiple databases or paper files
Having all your documents in one central system also allows your business to eliminate the “Let me look that up and get back to you” message customer service agents often tell clients. Which means they can spend more time focusing on customer service and less time searching for documents.
Quality document management also shows your clients they are working with a leading edge business partner. Document management allows your employees to use workflows to update clients on order statuses, invoicing etc.; your respond to clients quicker and ultimately decreases your companies processing time. Additionally, you can create timely, vibrant and high quality electronic brochures and quotations, which have a much higher impact than faxed documents.
Businesses of all sizes and in all industries can benefit from quality record management solutions. Contact your local document management company for more information about how document management will allow your business to automate manual paper-based tasks and helps expedite customer orders and ultimately improve customer service.
Kevin D’Arcy is VP of Sales and Marketing for MES Hybrid Document Systems, Ontario’s leading document scanning and document management supplier. For more information about document management, visit our website.
Ensure They Get Your Product: Address Cleansing
Address cleansing software is crucial to any organization, regardless of what goods or solutions are offered to users. Data entered into such software programs is accurate and that there are no errors and that there’s an actual street address which matches from what was inserted.
Many times, in haste, data is entered and the name of the road, city or state is misspelled. In these kinds of instances, with inaccuracies, there are problems getting invoices, charges or sending the ordered goods to a shopper. This type of software programs catches mistakes from the entry point to ensure there aren’t any difficulties in communication with the consumer.
Every single small business needs some sort of plan for address cleansing. If a small business does not make use of software checking quite often they must have workers to double check the job. However, this wastes time for any personnel, in addition to the employer, and there is still the possibility of mistakes.
Corporations should benefit from this sort of software system to not just save you money and time on personnel expenses, but in mailing costs as well. Even if postal mail isn’t delivered to the customer, the company continues to be out the money. Any time customers have no idea that there bills are due, these people pay late. Erroneous material becomes the middle of numerous varied difficulties.
Any time it comes to supplying merchandise to shoppers, address cleansing is the only means to ensure that the correct merchandise will show at the correct doorstep. Using computer software which checks for accuracy and reliability as the information is entered into the system, there can be very little chance of mis-deliveries or even items never showing up.
Whenever problems with delivery do happen, unfortunately, the particular expense is placed on the shoulders of the business. In the event that more than a single delivery is sent out to the same home address, there might possibly be difficulties finding the first package and retrieving it. Obtaining quality data ensures that customers are satisfied and trust a firm to deliver exactly as promised.
Address cleansing computer software can be added to any system in a timely method. It can also be applied to websites so any time buyers key in their own data, some sort of accuracy and reliability check happens. Simply because of the volume of cost savings in both time and cash, firms would end up being wise to add the software as quickly as possible. As quickly as everything is in place, there ought to be a swift decrease in returned mail, and a lot more correct deliveries.
In addition, just about any business setting up this particular software program will certainly notice a high incline in the effectiveness of every office. Because everybody perceives the very same information, and it is accurate, time is without a doubt saved and everybody is more speedily on the same page. Even though there is a expense to this kind of acquisition, the price savings over time is seriously worth the commitment. Whenever consumers are happy and info is right, the business can easily run far more efficiently and nearly everybody is content.
Address verification software gives you the peace of mind that you are taking care of your address data. Address cleansing should be a part of every businesses processes, to ensure that data is clean and error free.