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.