This preview shows page 1. Sign up to view the full content.
Unformatted text preview: Solving Accounting Principles
Problems Using Excel for Windows
to accompany Accounting Principles
Ninth Edition
Rex A Schildhouse, LCDR, U.S. Navy, Retired, M.B.A.
San Diego Community College District, Miramar Campus, San Diego, CA
Jerry J. Weygandt, PhD, CPA, Arthur Andersen Alumni Professor of Accounting,
University of Wisconsin, Madison, Wisconsin
Donald E. Kieso, PhD, CPA, KPMG Peat Marwick Emeritus Professor of Accountancy,
Northern Illinois University, DeKalb, IL
Terry D. Warfield, PhD, CPA, PricewaterhouseCoopers Research Scholar,
University of Wisconsin, Madison, Wisconsin Chapter 4 BASIC EXCEL DATA
Chapter Outline
Basic Data Entry
Sum Formula
Basic Formulas
Look To Formula
Mathematical Order Of Operations Nested Parentheses
Recently Used Files
Undo And Redo
Add Ins Basic Data Entry
The best way to input data into Excel is in its most basic form and then let Excel format it through default
or manually imposed formatting. Suppose you are asked to enter the value “123,456.789” into Excel. You
may get “123.456.7890”, “123,456.789”, “123,456.79”, “123,456.8”, “123,457” or something else as
Excel complies with its constraints of formatting and column width. This is shown in cell A1 of the
“Inputs” tab of the Basic Data and Formulas reference file workbook. However, if you input
“123456.789” without the comma, and then select the cell after input and click on the comma icon on the
tool bar, shown in the screen print as one of the common formatting task, Excel will insert commas
automatically. Once the commas are
inserted, you can click on the
decimal display icons, one to
increase the significant digits
displayed and one to decrease the
significant digits displayed, to attain
the correct number of decimals.
Common formatting tasks
Each click changes the decimal
places by one place. Note: Changing
the significant decimal places
displayed does not change the significant digits held by Excel for computational purposes.
Suppose that you had entered the “123,456.789” into cell E1 on the Input tab. In this cell there
appears to be “####”. This is an indication by Excel that the numeric value in this cell exceeds the width
or displayable area of the cell. If you click into this cell and make it the active cell, you will see that it
contains the full value as keyed in. To resolve this presentation issue you can manually resize the column
by placing your cursor over the small vertical line between the “E” column and the “F” column header
markings. Your cursor will become a doubleheaded arrow with a vertical line through it such as:
Once the arrow appears you can hold your left mouse button down and drag the
column into a greater width. This should permit the full display of the contained value.
You can try this on the “E” column of the Input tab of the Basic Data and Formulas
workbook. You can also auto format the width of the column to the width of the widest data within the
column by placing your cursor over the vertical line between the “E” and the “F” column and doubleclicking the left mouse button when it becomes a doubleheaded arrow. This may not give you the full
Section 2, Page 23 Page 24 Solving Accounting Principles Problems Using Excel for Windows
three significant digits as cell formatting may overrule the presentation. However, as discussed earlier in
this item, you can change the decimals displayed and then auto format the width again.
Suppose that the input was $123,456.78. One recommendation is that you input the value as
123456.78 and then click the “$” formatting button on the taskbar. This will place a dollar sign in the
front of the value and place two decimal places into the format. This process will also normally format the
width of the column to display all significant digits. This applied formatting is called “Accounting” and
places the dollar sign in front of, and away from, the first digit, the one in this case. This formatting can
be applied through the path Format > Cells and then selecting the “Numbers” tab and selecting
“Accounting”, the fourth selection on the list. Through this dialog box you can override the default of two
decimal places and change the currency sign to “None” or other common currencies. Additional
formatting capabilities are explained in the formatting cells section elsewhere in the text.
Note: The formatting within the Excel templates is already accomplished. Most of the values are
formatted with the “Currency” selection. The presence of dollar signs is on or off as appropriate and the
establishment of decimal places is correctly set.
NOTE: IT IS RECOMMENDED THAT YOU NOT REFORMAT THE EXCEL TEMPLATES OR
CHANGE THE COLUMN WIDTHS. THESE HAVE BEEN SET FOR PAGE, PRESENTATION,
AND PRINTOUT CONSIDERATIONS. Sum Formula
The “Sum” formula of Excel is an easily understood tool. When the entry of data into a cell starts as
“=SUM(” Excel is looking for a math function to follow, not just “add” as “sum” implies. You can enter
basic data as “=SUM(2+3+4)” and Excel will calculate the answer as 9. You can also reference cells such
as the formula in cell J41 of the Demo file. This cell contains the formula “=SUM(I39J40)”. This
formula subtracts the value in cell J40 ($40,000) from the value in cell I39 ($50,000) to calculate the
amount of capital paidin in excess of par value for common stock for the entry, $10,000. You must
determine the appropriate formula to place into the cell and the appropriate references. The transaction
states that the owner initiated business by buying 40,000 shares of $1 par common stock with $50,000
cash. The formula in cell I39 is a “Lookto” formula “=D14”, addressed in the “Look to” section of this
book. It simply references the cell D14 which contains the cash contributed, $50,000. In cell J40 the
formula determines the amount to be credited to common stock by multiplying the number of shares, a
value contained in cell D12, by the par value of the shares, a value contained in cell D13. The result of
this mathematical operation is $40,000. Since you determined and placed the amount of cash contributed
to the company in cell I39 through the “Look to” formula and you determined the value of the capital
stock issued in the transaction in cell J40. The amount going to Capital paidin in excess of par value for
common stock is the result of subtracting the common stock value from the cash. This formula is
“=SUM(I39J40)” and results in $10,000. This is what the formula in cell J41 does. There are other ways
that formulas can be used to determine these values but subtracting the first of two credit values from the
only debit value of the journal entry assures you that the debits will equal the credits within the journal
entry. This does not guarantee you that the values are correct. That is part of the challenge of the exercise
or problem.
With the “=SUM(” you must contain the formula with parenthesis marking the beginning and end of
the formula run. You can also use parenthesis to contain and isolate the math operations precedent within
Excel. Excel will do operations of multiplication and division before operations of addition and
subtraction – “My Dear Aunt Sarah” (Multiply – Divide – Add – Subtract) as a memory clue. Therefore
the formula =sum(3*4+1) will result in 13. Excel reads and acts in the order of 3 multiplied by 4 and then
1 is added to the result of the multiplication operation. If you had wanted to multiple the sum of 4+1 by 3
the formula could be written as “=SUM(3*(4+1))”, “=SUM(4+1)*3”, or “=SUM((4+1)*3).” The result of
these formulas will be 15. The use of multiple or “nested” parentheses is common and accepted within Chapter 4, Page 25
Excel as it is in any other discipline of basic math operations. There is a maximum of 7 “nestings” within
an Excel formula but you can reference one formula in another formula making the process rather large.
If the formula is simple you can shorten the sum formula to =3+4+5 and the result will be 12. In this
case, Excel can understand the basic formula and the use of “SUM” to start the formula is unnecessary.
Excel will also mix constants with cell references within the formula. For example, in cell I71 of the
Demo File is a formula that calculates the interest payable on the bank note. It takes the principle of the
note, $250,000, a value contained in cell D19, and multiplies it by the annual interest rate contained in
cell D20, a value of 10%. Since the bank note was only out for a 6month period, the additional factor of
6/12 is included in the formula to recognize a 6month period of a fiscal year. This complete formula is
“=D19*D20*(6/12)”. With this presentation of 6 months, it is reinforced why the multiplier is there
within the formula. This technique will also preclude entering a 4month period as 0.25 vise 0.33 as can
occasionally happen.
Excel starts all addition, subtraction, multiplication, and division formulas with “=”. This leadin is
merely a trigger for Excel. If the sum function is used within an imbedded formula, the equal sign is not
used. Imbedded formulas are shown in the general ledger balance columns of the Demo File. Examine
cell J126 to see an embedded formula. Embedded formulas are addressed in the Embedded Formulas
section of the book. Notice that there are no “=” (equal signs) within the formula. This placement would
cause an error within Excel. Basic Formulas
The basic Excel formula is the summing of two numbers such as “2 + 2”. Within the “Basic Formulas”
tab of the Basic Data and Formulas workbook on the data disk, the cell A1 contains a statement that
identifies what is in cell B1. The basic formula written in cell B1 is actually “=2+2”. The “=” (equal sign)
tells Excel that an executable function is following and Excel is expected to do something. The “operator”
of “+” tells Excel to add the preceding and succeeding values together. The presentation within cell B1 is
not “=2+2” but is “4”, the result of the operation. If Excel had been asked to add 2 and the letter “a”
together without Excel knowing the value of “a”, as shown in cell B3 the result will be “#NAME?”. Excel
was told to perform the executable function of addition through the “=” and the “+” however, one of the
values, the “a”, has no numerical value and therefore Excel cannot complete the function. In this event
Excel provides you with the “#NAME?” annunciator. This is one of the ways Excel tells you that it is
unable to complete what it thinks you asked for.
There are some variations of the simple or basic formula “=2+2”. For example, if you entered the
formula “=sum(2+2),” Excel would provide the answer of “4” as shown in
cell B5. The term “Sum”, which indicates that the operation asked for is an
addition, subtraction, division, or multiplication operation. The “Sum” may
be entered in upper or lower case, or in mixed case and Excel will convert it
to upper case. An input such as “=SuM(2+2) will result in “=SUM(2+2)”.
You can also enter “+2+2” or “=+(2+2)” and Excel will display the answer
of “4” as shown in cell B7. Excel, as stated, is rigid in its input formatting but, if it can figure it out, Excel
will either modify the input, offer a formatting modification, or complete the operation. These inputs are
shown in this screen print.
Other math operations such as subtraction can be written as “=42”, “=+42”, and “=SUM(42) as
shown in cells F2 through F4. Multiplication can be written as “=4*2”, “=+4*2”, and “=SUM(4*2) as
shown in cells J2 through J4. Notice that Excel removes the plus sign between equal sign and the first
value of the formula.
Excel uses the “/” as the indicator or command for division operations. To divide the value of 4 by 2
the formula within Excel would be =sum(4/2), =4/2, or =+4/2. The “+” (plus sign) will not interfere with
the division operation and placed there as a matter of habit by many individuals. Excel will remove it Page 26 Solving Accounting Principles Problems Using Excel for Windows
upon completion of data processing. Excel will handle references within division just like other math
operations. You can divide the contents of cell A1 by the contents of cell A2 through the formula
=A1/A2. You can also mix references and hard values in the division formula such as =A1/2. Look To Formula
The process of writing a formula such as “=2+2” is referred to as entering “hard numbers” into a formula.
This process is rather restrictive but it can accomplish almost everything a professional has to do within
Excel. The process of entering hard numbers increases the amount of keystrokes and increases the
probability of errors. Even though Excel is rather rigid in its input criteria, if you understand the
capabilities of Excel, you will find that the methodology of getting numbers or values into Excel is almost
unlimited. One of those methodologies is the “Look to” formula. This formula is actually a command to
Excel to accept the value inserted in another cell at this point of the process. In cell A9 of the Basic
Formulas tab of the Basic Formulas data file the formula “=A1” was entered. The value displayed in cell
A9 is the same as the value in cell A1 through this “Look to” formula. In cell A13 the “Look to” formula
reads “=A11” and results in the text “Test text” from cell A11 being readable in cell A13.
The “Look to” formula can be utilized wherever the values are available in cells as specific values. In
cell A15 there is a sentence that contains the value of $600.00. This single value cannot be “looked to”
since it is within a text string – a sentence and cannot be read as a single value. However, the sentence
presentation in A17 is slightly different – the text starts, then stops so that the value can be inserted into
its own cell then the text continues in the next cell. This allows the $600.00 value to be “read” by a “Look
to” formula as shown in cell A20 where the $600.00 value is multiplied by two. This is how the data files
are structured. Mathematical Order Of Operation
Excel has a very specific order of operations in mathematical functions. The order of operation will be
within contained parenthesis in the order from the deepest interior set of parenthesis first to the most
exterior set of parenthesis. Within the parenthesis Excel solves mathematical operations in the order of
Multiplication ~ Division ~ Addition ~ Subtraction This can be remembered by “My Dear Aunt Sarah”
(Multiply – Divide – Add – Subtract). In this example =SUM(2*(2+4*(51)2)*4) the correct answer is
“128.” The order of events is (51) or 4, then this four is multiplied by the preceding four resulting in
sixteen. Then two is added resulting in eighteen and two is subtracted resulting in sixteen. Then the
sixteen is multiplied by the preceded two resulting in thirtytwo and this thirtytwo is multiplied by the
succeeding four resulting in one hundred twenty eight. Had this problem been solved from left to right the
answer would have been sixtyfour. When more than one set of parenthesis is utilized within a formula
they are referred to as “Nested.” Nested Parentheses
Nested Parentheses is the use of more than one set of parentheses within a formula. These are required in
the complex formula structure of Excel and help establish the order of precedence for data manipulation.
For example, the formula for the interest payment of an interest bearing note payable is “=IPMT(Interest
rate for the periods specified, the payment number for which you want the interest payment for, the total
numbers of payments for the note payable, the principle amount of the note payable, the future value of
the note payable, and a statement of whether the payment is made at the beginning or the end of the
interest period). To determine the interest paid in the second payment of the third year (the 26th payment)
for a loan at 8% annual interest with interest compounded monthly where payments are made at the end
of each month over a life of 30 years with a zero future value – no balloon payment, the formula could be
“=IPMT(8%/12,(12*2)+2,12*30,150000,0,0).” This structure requires Excel to divide the annual interest
rate of 8% by 12 months to apply the monthly compounding factor, then the second payment of the third
year is twelve months times two years plus another two months, the next set provides twelve months per
year for thirty years, then a principle value of $150,000 – Note: Do not use dollar signs or commas within Chapter 4, Page 27
this entry, it will cause Excel to fault the formula, there is not balloon payment so the value of zero (0)
must be entered, and the last zero tells Excel that the payment is being made at the end of the month. Had
this last value been a one (1) Excel would have calculated the interest payment as if the payment was
being made at the beginning of the payment and interest accruing period. The result of this formula, a
negative $981.81 is shown in cell B9 of the Basic Data and Formulas workbook on the Basic Formulas
tab. The reason for the negative value is since the principle was a positive value indicating cash flows in,
then the interest payment would be a cash flow out – a negative value. Had the principle been negative
indicating cash flows out, then the interest payment would be positive indicating cash flows in. This will
be addressed in the Cash Flow Within Formulas section elsewhere in the text.
However, the issue is nested parentheses. The parentheses surrounding the twelve months times the
two years assures you that Excel will multiply the months in a year by the number of years before adding
the months of the third year. As addressed earlier, Excel works on an order of precedence for mathematics
of multiplication and division operations before any addition and subtraction operations. So Excel should
do the multiplication of the months and years before adding the third year months but the use of
parentheses assures that it will.
The formula “=SUM(2*(2+4*(51)2)*4)” utilized in the Mathematical Order of Operation section
earlier can only be accomplished through nested parentheses. This example is
shown in cell D23 on the Inputs tab of the Basic Data and Formulas workbook. One
of the very helpful events that occurs within Excel while utilizing nested
parentheses is the coloration of parentheses that Excel provides when closing them.
If you were to enter the formula =SUM(((1)+(2)+(3)+4)+(2+3)+6+8+7) as entered
in cell C11 of the Basic Formulas tab of the Basic Data and Formulas workbook
you will notice that Excel adds coloration to matching parentheses to provide
guidance as to where the opening parenthesis is for the closing parenthesis you just
added. Recently Used File List
As discussed earlier, one of the things Excel will do is present the predetermined
number of previously used files if the “File” title is clicked on the menu bar. The
number of files presented is controlled through the “Tools > Options” path which
brings up the “Options” dialog box. On the “Options” dialog box, click on the
“General” tab and you can change the number of files shown by increasing or
decreasing the “Recently used file list” number. If you do not desire this function,
you can deselect it by removing the checkmark to the left
of the “Recently used file list” title line. These items are Recently used files
shown on the screen print provided. This is a very useful
function. A number from 3 and 10 seems to be most
reasonable but it can be set to your taste. Undo And Redo
With the versatility and power of Excel and the Microsoft Office products, errors are bound to occur due
to the capability to manipulate data. When going to copy a cell and using keyboard commands of CtrlC,
the keystrokes of CtrlV may be struck instead, resulting in
a paste operation rather than a copy operation. To correct
this event utilize the curling to the left blue arrow, the
“Undo” arrow, on the tool bar. Clicking directly on it will
Excel Undo and Redo arrows
reverse the last action taken. This arrow can be clicked
numerous times to reverse a series of previous events.
There is a dropdown menu selection available with Page 28 Solving Accounting Principles Problems Using Excel for Windows
“Undo” through the small arrow pointing down just to the right of the symbol. With the selection of one
of the events depicted you can reverse not the last event but an event that occurred 3, 4 or 5 events ago. In
older versions of Excel, the “Save” function will preclude undo’s from the period prior to the last “Save”
operation.
“Redo” will reinstate an “Undo” action. This icon is a blue sweeping arrow to the right, a mirror
image of the “Undo” arrow. It also has a dropdown menu associated with it to select prior events. Add Ins
Excel has many functions and formulas that are not loaded by default to
save memory. This is a simple procedure that will ensure that all of the
functions and capabilities of Excel addressed in this book are available to
you on your system. Follow the path Tools > Add ins from the menu bar of
Excel. The popup menu, which appears here, will give you numerous
options. Select by ensuring that a checkmark is in the following selections,
as shown – Analysis ToolPak, Analysis ToolPak VBA, Conditional Sum Wizard, Internet Assistant VBA
(Optional but will increase the ability of “Help”), Lookup Wizard, and Solver Addin. Once these are
selected, click on “OK” to complete the process. The actual size of Excel will grow very slightly and it
might take momentarily longer for Excel to load but neither are appreciable values. Once loaded into
Excel, their capabilities will be available to every workbook and worksheet. Chapter 5 COPY, CUT, PASTE, CLEAR, AND
DELETE
Chapter Outline
Copying And Pasting
Copying A Formula
Absolute Reference Cut Command
Clear And Cut
Delete And Delete Copying And Pasting
Excel allows you to copy and paste both cells and data from within cells. Both of these functions can be
used in accomplishing the tasks of the templates. The first function to be addressed is copying a cell. This
function is accomplished by activating the cell you would like to copy. To do this, select the cell in any
way you desire. The options are put your mouse cursor over it and click into it, use the arrow keys on the
keyboard to move your current selected cell over to the desired cell, strike the tab key to advance the
active cell, or strike the enter key to advance the active cell. When striking the enter key, the cursor will
advance or move as controlled by the options set under the “Edit” tab of the Options dialog box. This tab
can be found by following the path Tools > Options > Edit from the menu bar. The active cell can be
identified by the heavy or accented outline such as cell 6 in the presentation here:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 A range may be selected clicking in a corner cell of the square or rectangle and then by sweeping
from that corner of the square or rectangle of cells to the opposite corner of the square or rectangle with
the cursor while the left mouse button is being held down. It does not matter in which direction you
sweep to select the range. In this example the range selected will be highlighted such as cells 25, 26 and
27 as shown here:
21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 Section 2, Page 29 Page 30 Solving Accounting Principles Problems Using Excel for Windows
Once a cell or range of cells is selected, you can copy it by clicking on the two overlaying sheets of
paper on the tool bar, by right clicking the cell or cells and selecting the “Copy” command from the popup menu, or by following the path Edit > Copy. You can also utilize the keyboard command of “CtrlC”.
When copied, the cell (or range of cells) is carrying its(their) formatting details with it(them). This feature
is very convenient since you would normally want copied data to be presented in the same manner as the
original presentation. The data is now in your clipboard and ready for pasting. Move your cursor to the
desired pasting location and click on the clipboard icon on the tool bar, right click on the cell and select
the “Paste” command from the popup menu, or follow the path Edit > Paste. You can also use the
keystrokes “CtrlV” to paste the copied data.
If you selected more than one cell, the group or range of cells copied will be pasted in the same
orientation as those from the upper, left most cell of the selection. When pasting, if you have selected to
paste over a cell or range of cells containing data, Excel may not warn you that you are pasting over data.
In this event, the existing data will be overridden by the paste operation. However, all may not be lost –
you may be able to use the “Undo” command, addressed in the Undo and Redo section of this book, to
recover the data. When working with Microsoft Office XP or 2002 products such as Excel XP or Excel
2002, there may be more than one item on the clipboard at a time. To view the various items on the
clipboard follow the path Edit > Office Clipboard and the items on the clipboard will be displayed. The
clipboard function will hold up to 24 objects from any of the Microsoft Office products registered to the
system so you can open up Microsoft Word, copy multiple items out of a document, open up Excel, and
paste those items into Excel in any order you desire. The clipboard will retain its contents through save
operations and, as stated, it displays all of the information from all of the registered products allowing it
to be used as an import/export tool for multiple items.
A feature associated with “Copy” is the “Insert” function. The function is found under the path Insert
> Copied Cells or by right clicking on a cell after the “Copy” command has been utilized. If a column,
row, range, or individual cell has been copied, this feature will insert the copied cell or cells in relation to
its shape and the target cell as instructed. If the positioning is not clear to Excel a dialog box may appear
that asks if you want the copied cells inserted to “push” the existing cells to the right or down. Formulas
associated with the copied cells will be relational unless they were absolute referenced in the source. (See
Absolute Reference section elsewhere in the text on this matter.) If the copy command is initiated and not
completed with a paste command, simply striking the “Esc” (Escape) key a couple of times will cancel
the operation. Copying A Formula
Excel has several ways of copying formulas and functions. Most of them are considered “relational” in
their operation. This effect is explained and shown in detail in the “Absolute Reference” section. The
quickest and easiest way to copy a formula within Excel is to click into the cell containing the formula
and use any of the numerous ways such as CtrlC to copy the
cell. Then move into the target cell and paste the formula
there via any of the numerous ways such as CtrlV to paste
the value into the cell. However, this process is relational as
explained in the “Absolute References” section. To COPY
Formula Entry
(not cut) the formula and not have it read relational to its
Window
target cell requires a little bit of technique unless the formula
is absolute referenced. Open the “Copying A Formula” data file which is a copy of the “Absolute
Reference” data file. Click into cell C1 which contains a formula summing cells A1 through A3 to make
C1 the active cell and have its formula displayed in the formula entry window. In the formula entry
window at the top of the screen, click into the formula entry window to the right of any data or text
displayed and then sweep from right to left with the left mouse button down, highlighting the entire
formula entry window. When the entire formula in the formula window is highlighted, use any of the
copy commands of Excel such as CtrlC to copy the data. Move to the target cell, C5, click into it to make Chapter 5, Page 31
it the active cell. Notice that Excel has placed the formula “=C5” into the formula window. This is not
want you want and Excel is trying to maintain a reference that is inappropriate for your use. To “break”
this reference, press and release the Esc (Escape) key several times to “release” the cell relationship.
Ensure that cell C5 is the active cell, you may have to click back into it. Now paste the formula into the
cell with any of the paste commands of Excel such as CtrlV and press and release the “Enter” key or
move out of the cell in any manner you desire. The formula is now in the target cell with its original
references, not relationally moved. In this process, you copied the contents of the cell, not the cell, to a
new location.
This technique will also allow you to copy a formula out of one cell and paste it into another cell as
part of a new, larger formula. Remember that the “=” (Equal sign) is not used within embedded formulas.
If you are building embedded formulas, as addressed in the Embedded Formulas section, you can proof
the formula segment in one cell, use this concept to copy that segment, and then paste it into the
embedded formula cell. For example, contained within cell G1 is a formula summing cells F1 through F3.
Copy the formula from cell C1 using the technique discussed above. Now click into cell G1. Remember
to press the Esc (Escape) key to “release” the original cell reference and insure that cell G1 is the active
cell. Click into the formula entry window to the right of the formula text contained in cell G1. Then use
any of the “Paste” commands such as CtrlV to paste the formula from cell C1 into cell G1. When this is
accomplished, click into the formula and change the “=” (equal) sign in the middle of the formula to a +
(plus) sign to have the formula read “=F1+F2+F3+A1+A2+A3” vice “=F1+F2+F3=A1+A2+A3”.
Remember that “=” (Equal signs) within formulas will cause an Excel error or fault message. Press and
release the “Enter” key to complete the process. The formula now sums cells A1 through A3 and F1
through F3 with minimal typing.
Using this technique you can copy a portion of a formula and paste it back onto itself to build larger
formulas and functions. This was done extensively on the “Concatenate” formula data file. It reduces
keystrokes and it assures that you have an operational formula segment being placed into an operational
formula segment reducing troubleshooting. Absolute Reference
Excel has a function called “Absolute Reference.” This function will maintain cell reference by column,
row, or both when the formula or function is copied or moved. To understand the function, you need to
appreciate how Excel copies & pastes or moves things, which are two different operations. On the
“Absolute Reference” data file there is a small range of number and some formulas to show how Excel
handles “Absolute Reference”, “Copy and Paste”, and “Cut and Paste” or “Move.” In cell C1 the formula
is totally relational. The Excel formula in cell C1 reads =A1+A2+A3, what Excel is really saying is look
two columns to the left (formula in column C, first value in column A,) then, do not change rows, (both
formula and first row reference is row 1), and add that value to the value two columns to the left and one
row below the formula row, and add that sum to the value found in the cell two columns to the left, and
two rows down. This is “Relational Referencing.” To prove this concept out, click into cell C1 to make it
the active cell and then use the keystrokes “CtrlC”, right click the cell and select the copy option off the
popup menu, or follow the path Edit > Copy to copy the formula (contents) of the cell. Now move your
cursor over to cell F1 and click into cell F1, activating that cell. Now right click the cell and select “Paste”
from the popup menu, follow the path Edit > Paste, or utilize the keystrokes CtrlV to paste the formula
into cell F1. When you complete the paste operation, the value returned by the formula is #VALUE
because you are telling Excel to mathematically add the three text strings contained in cells D1 through
D3 together and that is obviously an error of procedure. This is because you moved the formula in cell C1
3 columns to the right and now Excel is looking at cells D1 through D3 – the same relationship that cells
A1 through A3 held to cell C1. They are 2 columns to the left and on the row of the formula, one row
below the formula, and two rows below the formula. Page 32 Solving Accounting Principles Problems Using Excel for Windows
Click back into the cell C1 and copy the formula again. This time paste the formula into cell F5. This
time the formula returns the value of 0 or zero since it is now looking at cells D5 through D7 and there is
nothing contained within those cells. The relationship of cells D5 through D7 are the same to Cell F5 as
the relationship of cells A1 through A3 are to cell C1 and cells D1 through D3 are to F1 – two columns to
the left and on the formula row, one row below, and two rows down. These are “Relational References.”
To overcome relational reference factors Excel allows you to place a special character – the dollar
sign ($) in front of the column identifier, the row identifier, or both the column and row identifier
changing “Relational Reference” to “Absolute Reference.” In the “Absolute Reference” data file click
into cell C2, this formula is absolute reference to the column only and is relational to the row. The
formula is =$A1+$A2+$A3. Copy the formula and paste it into cell F2 and watch the results. This time
the formula returns a valid answer of 6. Examine the formula in cell F2 and you will see that the column
reference has remained at A and the rows have remained at 1 through 3 since your original formula was
on row 2 and the pasted formula is on row 2. Click back into cell C2 and copy the formula. This time
paste the formula into cell F6. This time the formula becomes “=$A5+$A6+$A7”. It maintained is
column reference because of the dollar signs but the rows shifted. In cell C2 the formula looks one row
up, then on its row, then one row down. Now on row 6 it is looking one row up, to row 5, looking at its
row, row 6, and one row down to row 7 and summing the values – no value so no total from within those
cells. The formula maintained absolute column and relational row references.
In cell C3 the formula is written with relational column references and absolute row references. This
is done by writing the formula as =A$1+A$2+A$3. Experiment with copying and pasting this formula
around the worksheet. The row references will always be 1, 2, and 3 but the column references will
change by your relationship between column C and where you paste it. Try pasting it into column A or B
and watch the results. Since the reference was to two to the left of column C, when pasted into column A
or B the relational column is to the left of column A, a place in cyber space, the formula does not have
access to. When pasted into column C in the lower cells the response is valid since the rows are absolute
references and the columns are relational.
By writing the formula as =$A$1+$A$2+$A$3 both the column and row are absolute reference and
not relational. Copy this formula as shown in cell C4 to other locations on the worksheet and watch the
results and resulting formula. Try pasting the formula into column A or B and watch the results. Since
both the column and row references are absolute and not relational, the results are valid regardless of
where pasted.
You can mix absolute and relational references as shown and you can absolute reference one value
and relational reference the next. For example, there is a mini tax table on the “Absolute Reference” data
file. The formula was placed into cell C10 was written to absolute reference the tax rate cell, cell C8, and
then relational reference the purchase amount in column A starting at row 10. The formula was then
dragged down the column. In the drag process, it maintained the absolute and relational references and
produced the tax amount.
Hint: Suppose you write a long formula and now have to make it absolute reference. You can use
“Find and Replace” – CtrlH  to do the work but be aware of what it may do against you. Using this
capability, ALWAYS SELECT A RANGE TO CONTROL IT – if only one cell is selected “Find and
Replace” can run the entire worksheet without control. Look at the formulas on the “Absolute Reference”
data file in cells E10 through E19. If copied to a new location, the calculations will fail or be incorrect as
they are totally relational. Select the range E10 through E19 on the “Absolute Reference” data file. Then
use the keystrokes CtrlH or follow the path Edit > Find and Replace to bring up the “Find and Replace”
dialog box. In the “Find What” window enter the letter “C” without quotation marks. In the “Replace
With” window enter “$C$” without the quotation marks. Then select or click on “Find Next” (for the
most protection possible) and Excel will highlight the formula it is about to edit. You can see this in the
formula entry window. Click “Replace” and Excel advance automatically after the replacement option. Chapter 5, Page 33
Continue to review and replace the “C’s with “$C$”s and Excel will complete its operation on the range
or “fault” out and give you an advisory when 10 replacements have been accomplished. If you had
selected “Replace All”, Excel would have accomplished all 10 replacements on the selected area without
you having the ability to bypass or decline a replacement. Remember to select a range with this function
or every letter “C” (in this case) will be subject to replacement with “$C$”. Excel will generally not
“damage” a formula with “Find and Replace” but to rely on Excel for data management and protection is
bad technique. Now you can “Copy and Paste” the formula and it will be absolute referenced. Cut Command
The “Cut” command is accessible from the keystrokes CtrlX, the path Edit > Cut, right click on the cell
or range of cells once they are highlighted and select it from the popup menu,
or click on the scissors icon on the tool bar. To “Cut” a cell or a range of
cells, highlight the cell or range of cells. If the cells are not adjacent to each
other you will have to do more than one cut operation to get them as “Cut”
will not accept nonadjacent cells. When you cut a cell or range of cells from an Excel worksheet they will
not disappear immediately from the worksheet. The cut cells will remain encircled by a dancing daisy
chain, sometimes referred to as “Marching ants”, until you paste them into their new location. When you
use the “Cut” command to put the cells in the new location you may not receive any warning about over
printing existing data. If you want to place the data in more than one location you will have to highlight it
again, easy since it will remain highlighted after the “Paste” operation, then copy it and place it in the new
location. Cut is a onetime operation, you can paste several copies of a cell or range of cells from a
“Copy” operation  but not from a “Cut” operation.
When you cut a cell containing a formula with the “Cut” command and paste it into a new location
the formula retains its original references even if it was relational.
A feature associated with “Cut” is the “Insert” function. The function is found under the path Insert >
Cut Cells or by right clicking on a cell after the “Cut” command has been utilized. If a column, row,
range, or individual cell has been cut, this feature will insert it in relation to its shape and the target cell as
instructed. If the positioning is not clear to Excel, a dialog box may appear that asks if you want the cut
cells inserted by pushing the existing cells to the right or down. Formulas associated with the cut cells
will be updated to the new location of their reference data. “Cut” will not remove a cell, range, column, or
row from the worksheet, only the data in or within those cells and, this will not happen until the data is
pasted somewhere else so do not use this command to remove or delete data from the worksheet. The
“Delete” function will accomplish that. Also, if the “Cut” command is initiated and not completed with a
“Paste” or “Insert” command, simply pressing the Esc (Escape) key a couple of times will cancel the
operation. Clear And Cut
To Excel “Clear” and “Cut” are two different commands. The “Clear” command under the path Edit >
Clear or on the popup menu you are presented with when you right click a cell or range of cells as “Clear
Contents” deletes the information from the cell or range of cells without posting it to the clipboard for
later use. If you want to recover the information, it may be available through the “Undo” arrows or
command.
The “Cut” command is available through the path Edit > Cut, on the popup menu you are presented
with when you right click a cell or range of cells, or by using the Scissors icon on the tool bar when a cell
or range of cells is active or highlighted. The item or items in the cut cell or cells is not moved until you
select a target cell or range of cells and paste the cut cells in. “Cut” is a one time pasting event. If you
want to paste the cell or range of cells in several locations, “Cut” is not the tool, use “Copy.” However,
once pasted from a “Cut” process, the new area is currently highlighted so you can copy it without
reselecting it. Formulas moved by “Cut” retain their original references, even if relational, formulas Page 34 Solving Accounting Principles Problems Using Excel for Windows
copied and pasted are moved relationally unless the reference values are absolute references in the
original cell. Delete And Delete
There are actually two “Deletes” within Excel. The first is the keyboard “Delete” key. This key will
simply remove data from the worksheet without removing cells or their formatting. Any formula
referencing the deleted cells may or may not continue to work correctly. This depends on how the formula
works and where the value of the deleted cell was in the formula. If the deleted value is an element in a
summing formula, that formula should continue to function properly since 1+0 is 1, a defined and
acceptable process. If the formula added the values contained within three cells together and then divided
that sum by the value in a fourth cell and one of the numerator values is deleted the formula should
continue to work. If the denominator was deleted the formula will generate an error or fault since division
by zero and null values is undefined.
The second “Delete” is from the path Edit > Delete or the popup menu found by right clicking a cell,
range of cells, a row or range of rows, or a column or range of columns. When this option is used the
selected cell or cells will actually be removed from the worksheet. This command will frequently result in
a popup dialog box asking what movement you would like to occur as the cells are deleted – would you
like the remaining cells to fill in the void you are creating by moving to the left or by moving up.
These deletes are two totally different functions – the “Delete” key clears data but does not clear
formatting or remove cells. The “Delete” selection on the popup menu or the path Edit > Delete removes
cells from the worksheet and causes other worksheet cells to be repositioned. All of these actions are
reversible until the “Save” function in invoked. The “Save” function resets the “Undo” capability.
The data file “Delete And Delete” has been structured so that you can delete the contents of cells and
delete various rows and columns to see what will happen to sum and division formulas. The “Sum”
formula generated values will change as ranges deleted (cleared) or deleted (removed). If row 9 is left
untouched the division operations in row 25 will continue. However, deleting row 9 will remove the sole
element of the denominator of the division formula within row 25 and this will cause a fault or error in the
formulas of row 25. The division operations on row 28 utilize a sum of the range of rows from 12 through
14 as a denominator factor. Deleting a numerator row should not affect the formula’s operation nor
should deleting any one or two rows of 12 through 14 since a value is still in the denominator. However,
deleting all of rows 12 through 14 will cause an error.
Look at the formulas before and after the operations and you will notice that Excel updates the
formulas as you restructure the worksheet. Chapter 6 LOCATIONS AND LOCATING
Chapter Outline
Go To
Find Find And Replace
Name Ranges Go To
With Excel worksheets as large as they are you frequently traverse to various areas via the scroll bars, the
“Tab” key, the “Enter” key, or the arrow keys. Excel has a “Go to” function accessed by the keystrokes
CtrlG (Go to), through the path Edit > Go to, or the “F5” function key. The popup dialog box asks
where to? You can enter a cell address such as A4 and click “OK” and you are there. If you have named
ranges, as exists on the “Named Ranges” workbook, when you invoke the CtrlG (Go to) command, you
will be presented with those areas in the dialog box for quick and easy selection. The ability to use “Go
To” to move to a cell labeled “NetIncome”, “AdvertisingExpense”, or “GrossSales, (named ranges cannot
have spaces) is another reason to name a cell or a range of cells within Excel. Obviously, using
abbreviations would make the issue easier.
You can use the “Named Ranges” data file to try this capability. Find
With Excel worksheets being as large as they can be and the display screen as small as it is, it is often
hard to find a specific item quickly. Particularly if a prankster has hidden it by changing its font to white
on a white background filled cell. There are four hidden text strings on the “Find” workbook on the data
file. Two cells have the value “Hi!!!!” and two cells have the value “You found it!!!!” in them. By using
the keystrokes CtrlF for “Find” or following the path Edit > Find you will be given the Excel “Find”
dialog box. In the “Find What” window of the dialog box enter “Hi” – without the quotation marks. You
do not have to put any “!” (exclamation points) but if you do, do not put more than four. When “Find
Next” or “Find All” is clicked, Excel will initiate the search and find one of the two strings. The cell that
Excel locates will contain no visible text since the text coloring and the cell background are both white.
However, in the formula window you will read “Hi!!!!”.
At this point, close the dialog box. When the dialog box is closed, look at the borders of the cell just
below the cell that contains the “Hi!!!!”. Something is causing the right side of that cell not to display
properly. By clicking into the cell you will find the first of the “You found it!!!!” text strings. Click into a
cell 3~5 cells above and to the left these two cells and then highlight the cells down and to the right for
about 15 rows and 15 columns. You will see the text strings appear in the blue highlight as white text.
Hint: Looking for something hidden and do not know where it is? Try highlighting the cells. Excel
will tell you about how much of the worksheet has been used by the scroll bars. The more the scroll bars
can move, the larger the worksheet data range. In the data file there appears to be no reason for such
“long” scroll bars. Section 2, Page 35 Page 36 Solving Accounting Principles Problems Using Excel for Windows
Tip: If a range of cells is highlighted when the “Find” function is activated, it will only search those
cells. Excel will frequently ask if you want to search the
rest of the worksheet but there are instances where you do
not get that advisory because you closed the function prior
to the function completing the find on the selected cells.
You can also specify conditions or parameters on the
“Find” function through the “Options” button near the
bottom right of the dialog box. The options include
searching the entire workbook from that worksheet and
instructing Excel to search in a vertical – by columns, or
horizontal – by rows routing. You can also specify if the search is case sensitive or if you are only
interested in cells that contain all and only the input you have instructed. If you select “Case Sensitive”
and “Match Entire Cell Contents”, the “Find What” of “Apple” will not return “apple”, “apples” or
“Apples.” Through the “Format” options you can find italics text only, left or right justified text and other
formatting conditions. Remove any text from the “Find What” window for this as it will find only that
text with that format if entered and you want to find the format, not text. On the “Find” worksheet there
are some formatted cells. Try a search for cells formatted to “Times New Roman.” They may or may not
contain text. Find And Replace
Excel has a “Find And Replace” function that is considered an added option of the “Find” function. This
function is activated by the keystrokes CtrlH, by following the path Edit > Find and Replace, or by
selecting “Options” on the “Find” function accessed through CtrlF. The “Find and Replace” function is
powerful and dangerous as well as extremely useful.
Tip: One of the safest ways to control “Find and Replace” is to limit its range by selecting a column,
a row, or a range of cells rather than letting it roam the worksheet. The second safest thing (not in any
order) is to utilize the “Find Next” button and then determine if you want the item found replaced or not.
If you do, click the “Replace” button and Excel will replace that one item and then find the next item and
ask for approval before replacing that item. If you do not want that one item replaced, click the “Find
Next” button and Excel will move on without replacing it.
The “Find and Replace” worksheet is available for a minor exercise. Open up the “Find and Replace”
data file and click into cells C5 and D5. This range selection will contain Excel’s find and replace powers
to two cells for the demonstration. Use the keystrokes CtrlH to bring up the “Find and Replace” dialog
box. Since you want the formula in cell D5 to be absolute referenced so you can copy it to other locations
without changing its relational reference, tell Excel to find the letter A in the “Find What” window. Upper
or lower case does not matter. In the “Replace With” window tell Excel to replace it with “$A$”. This
will establish absolute reference for both column and row as the resulting formula will be
“=SUM($A$1:$A$5)+SUM($A$7:$A$11)+SUM($A$13:$A$17) from =SUM(A1:A5)+SUM(A7:A11)
+SUM(A13:A17)”.
However, when “Replace All” is clicked cell C5 changes from ABCabc to $A$BC$A$bc. Had only
one cell been selected on the worksheet Excel would have replaced every letter A on the worksheet, upper
or lower case, with $A$ as fast as your processor can process the request. Excel will correct the situation
by clicking on the “Undo” arrow on the tool bar. – unless a “Save” operation has been initiated. Since the
goal is to make the formula in cell D5 absolute reference from relational and we do not want any other
cells to be affected, and if only one cell is selected, Excel will find and replace on the entire work sheet
the key is to utilize “Find Next” rather than “Replace All.” With “Find Next”, Excel will identify the next
letter found and “ask” what you want done – “Replace”, “Replace All”, “Find Next”, or “Close.” By
selecting “Find Next” and then “Replace”, Excel is contained appropriately. Chapter 6, Page 37
Excel’s “Find and Replace”, through “Options”, will also find formats and specific conditions. Click
into cell D5 and bring up the “Find and Replace” dialog
box. Click the “Options” button on the dialog box and click
to insert checkmarks into “Match Case” and “Match Entire
Cell Contents.” Then click on “Replace All.” Excel will
replace only one item on the entire worksheet – cell D13 if
your “A” was upper case and cell D14 if your “a” was
lower case. Remove these two checkmarks for the next
exercise.
As a general rule, the exercises and templates are
formatted to Arial and 10 point font. However, one cell is formatted to Times New Roman and 12point
size font. The goal is to change that unique formatting to the conventional formatting through “Find and
Replace.” Clear the entries in the “Find What” and “Replace With” windows since these are “limiters”
and the goal is to replace all of the font with this unique formatting with Arial and 10 point formatting.
Click into “Format” on the “Find What” line and set the font to “Times New Roman” and “12” point size.
Then click “OK.” Click on the “Replace With” line’s “Format” button. Set the font to “Arial” and the
points size to “10” and click “OK.” Then click “Replace All.” Excel should find one occurrence to
replace. Major power here is that you do not have to limit the “Find and Replace” function by input
values in the “Find What” and “Replace With” windows. You can search for borders, fills, font colors,
and other options without regard as to where it is or how it is utilized.
Also available is the ability to control the direction of processing – by rows or columns. With “Find”
you can control whether Excel will look in formulas and comment boxes. With “Find and Replace” Excel
will process all elements meeting its criteria so self control is important.
Tip: Use your imagination to control “Find and Replace”, or the undo arrow, or your results may not
be what is expected. Excel’s “Find and Replace” is powerful and quite useful.
Clue: One of the options in “Find and Replace”, like in “Find”, is to do a find and replace on the
entire workbook. While powerful, workbooks may contain many more relationships than visible on the
worksheet. This function is very powerful – and dangerous. Name Ranges
Excel will allow you to name a range as shown on the Named Ranges data file. Open the Named Ranges
data file for this example. For Excel a named range is a single
cell or multiple cells identified through the naming process
with a unique name. Naming a cell or range of cells has some
controls and restrictions such as no spaces and no use of what
Excel Cell
Excel classifies as a special character such as !, @, or $ type
Identity
symbols  usually the symbols above the numbers on the
Window
keyboard. To name a cell or range of adjacent cells, highlight
the individual cell, or for a range click into the upper left,
upper right, lower left, or lower right cell and sweep the range. If the range of cells are not adjacent cells,
click into the first cell and highlight it or highlight the first range, then release the mouse button, press and
hold the Ctrl (Control) key down and click into the second cell or highlight the second range, release the
mouse button, continue to hold the Ctrl (Control) key down and continue to highlight the cells or ranges
with the mouse releasing only the mouse button after each cell or range of cells. Then follow the path
Insert > Name > Define and enter your chosen name in the popup dialog box. You can see, and verify,
the defined cells in the “Refers to” window at the bottom of the box. You can also enter the name directly
into Excel by clicking into the “Cell Identity” window on the formula bar and entering the name. By
clicking into this box you will also see the other named ranges for the worksheet. Page 38 Solving Accounting Principles Problems Using Excel for Windows
In the Named Ranges data file the ranges have been named for their fill colors. The ranges of Red,
Yellow, Blue, White, and Green are all adjacent cells for their ranges. The ranges Purple and White are
nonadjacent cells. Excel will include only the values identified by the range in the summation and math
functions utilized by formulas calling for the range Purple or White. That is, when range Purple is cited in
a formula, the values contained in the White cells are not taken into account even though they are
amongst the Purple cells, they are not members of the Purple range. The cell B25 contains a formula
summing all of the cells within the range of A18 through A25. This value is 76. However, only the cells
A18, A20, A22, and A24 are considered and identified as participants within the range name Purple. The
sum of Purple is only 36 as shown in cell B24. The formula to sum the range Purple is =SUM(Purple). In
this situation, Excel is not case sensitive with SUM or Purple.
To the right of the cells are formulas showing different ways of summing the values of the cells. As
you can see, you can address the range of Red several ways – “A1+A2+A3+A4”, “A1:A4”, or “Red.”
You can see how Excel will add the values of Red and Yellow together and how it will add the rest of the
ranges together as well as you look down columns C and D of the worksheet.
Tip: Within Named Ranges a cell can be a member of two or more ranges. This does not bring in the
values of other cells in the separate ranges. Chapter 7 CUSTOMIZING EXCEL
Chapter Outline
Excel Defaults
View Tab Through Options
Calculation Tab Through Options
Changing the Default File Directory Edit Through Options
Save As To Change File Locations And Names
Excel Workbook / File Naming Recommendations Excel Defaults
The “defaults” of Excel specify and control how Excel is going
to respond in situations where the response is not specified by
the user. For example, clicking on the “Save” icon on the
shortcut bar, the 3 ½” disk icon, will result in the current
workbook being saved back to the original location if it has been
saved before. If it has not been saved before, Excel will look at
the defaults and accept that the directory of the workbook
should be utilized as controlled through the path Tools >
Options > General. On this tab there is a window labeled
“Default file location.” By changing this window’s values you
can change the default location. Through a standard installation
of Microsoft Office this location may be “Documents and Settings.” This same location is utilized by
Word and other programs as their default save location. However, due to the large number of Excel files
generated, I have chosen to change the default location to a new directory called “Excel Files” on the
local “C” drive. The average user may not need to change the default values of Excel but knowing how to
“read” them will enable the user to follow the path Tools > Options > General to see where Excel is
saving their data.
Another default established through the path Tools > Options > General is the font style and size.
There are many fonts installed into the Office Suite through the default loading process. Almost all of
these are available to Excel by changing the value of the “Standard font” selection. By changing the font
and the size to that font to the values most commonly used, any new workbooks opened will utilize those
default values to establish the style and size of the font.
Through the path Tools > Options > View the presence of gridlines on the display screen is
controlled. These gridlines are provide an orientation to the columns and rows and this value is usually
left as selected as indicated by the checkmark in the selection box. However, if Excel is utilized as a
presentation tool, this selection can be changed to remove the gridlines. The gridlines on the display
screen are not the gridlines appearing on the printout. The printout gridlines are controlled through the
path File > Page setup > Sheet. By selecting “Gridlines” under the “Print” category as indicated by a
checkmark in the selection block, there will be gridlines on the printout. As with the display gridlines, this
selection is exclusive to the printout. By default and standards to generate a “clean” printout, most Excel
printouts do not utilize gridlines.
Section 2, Page 39 Page 40 Solving Accounting Principles Problems Using Excel for Windows
If Excel consistently does something that you want changed, chances are it is controlled by a
“default.” To change a default within a file, you can check for that particular control element under
several locations including File > Page Setup and its numerous tabs. Once the change is made, then save
the workbook to preserve the default setting. To save the new default setting as a global change, you need
to save the workbook as the default template to be opened by Excel each time upon opening. This can be
done with the instructions and guidance of “Help” from the menu bar and “Customize How Excel Starts”
as a query. Through this procedure you will be saving the workbook as a “Template” – a special file that
Excel will utilize to open any new documents. These changes will not affect workbooks already created. View Tab Through Options
By following the path “Tools > Options” and selecting the “View” tab you can set many of the
presentation issues within Excel by selecting – having a checkmark next to it, or deselecting it – by not
having a checkmark next to it or by selecting a value through a dropdown .window.
Many of the specific options within the “View” tab are explained in their individual sections
elsewhere. However, a quick scan of these options will allow you turn the gridlines on and off for screen
presentation, display zero values, display horizontal and vertical scroll bars and reveal or hide comment
indicators. Calculation Tab Through Options
By following the path “Tools > Options” and
clicking on the “Calculation” tab you gain access to
controlling whether Excel will do a recalculation
with every entry completion as marked by striking
the “Enter” keys or leaving the current cell. Suppose
you are working in a large workbook with many
formulas. With each data entry affecting a formula
the workbook recalculates the formulas. During this
time you are waiting for the processor to give you
keyboard access and update your video presentation.
You can preclude this by deselecting, removing the
checkmark, from the “Automatic” option and
selecting “Manual” in the “Calculation” section of
the tab. If you are working in a workbook and have
entered an amount of data that you want to have calculated, you can strike the “F9” key in the “Function
row” on the keyboard and all calculations will be updated without changing this selection. You can also
follow the long path and reenter this tab through the path “Tools > Options” and then selecting the
“Calculation” tab and clicking on the “Calc Now (F9)” button on the right.
The last option of this particular selection, and a normal default if you are operating in the “Manual”
calculation mode, is to recalculate upon shutdown. This may extend the time to save and close but it
updates the data for the next opening. If you elect to deselect this event you may open a workbook with
data that is presented but not taken into account with the formula throughout the workbook. This can be a
dangerous proposition and position.
The “Precision as displayed” selection on the tab means that if you set the worksheet to display to two
significant digits – 0.00 – and enter 0.004999 the value will be rounded to 0.00 not only in presentation
by in data entry. Multiplying this cell by any value will result in 0.00 since Excel converts the value to the
displayed value of 0.00 as a hard value. Caution should be used and this rounding should be understood
before this option is selected. Chapter 7, Page 41 Changing the Default File Directory
The default Excel directory is usually “My Documents.” This can be changed if you desire segregation of
data or want to reduce the burden of going to a
separate, special directory. To change the default
directory, follow the path Tools > Options from the
tool bar, then select the “General” tab. As shown in
this screen print, the default file location has been
changed to “C:\Excel Files.” With this setting,
when Excel opens, it will access this directory
when any of the open actions such as clicking on
the “Open File” icon on the tool bar, following the
path “File > Open”, or using the keystrokes “CtrlO” (Control – O for “Open”). Any default save
action will save the file here and make it be easy to
locate as it is a “root directory” location – right on
the “C:\” drive, not within another directory.
Before making this change or any change of
this type, you need to create the
target directory. You can do this
through the Windows Explorer
by selecting a local hard drive
such as the “C:\” drive and then
follow the path “File > New >
Folder” or through the Excel
“Open” dialog box. On the Excel
“Open” dialog box the “Create
New Directory” icon on the tool bar is shown in this
screen print. This directory will be created in
whatever the active directory is as shown in the
“Look in” window. If you want to create a directory
within the “C:\” directory, make sure that the “Local
Disk (C:)” directory is shown in the “Look in”
window near the top at the dialog box.
There is a second default directory Excel utilizes
frequently. On the “Options” dialog box under the
“Save” tab you tell Excel where to put the
“AutoRecovery” file and how often to save it. If you
change this directory, make sure you create the
target directory before you make any changes to this
directory. When Excel performs an “AutoRecovery”
operation, it will appear to be nonresponsive for the
several seconds that it is performing the function.
There may be a 3 ½” disk icon and “scaling bar” on
the lower tool bar indicating the operation. Edit Through Options
There are a couple of very important items on the
“Edit” tab found through the path “Tools >
Options.” The first is the “Move selection after Create New Folder Page 42 Solving Accounting Principles Problems Using Excel for Windows
Enter – Direction” selection. This setting controls the direction that you want the active cell and cursor to
move upon striking the “Enter” keys. The options are Up, Down, Left, and Right.
You may utilize the “Fixed decimal places” option to preset the significant digits displayed. If this option
is not selected and in a generally formatted cell the value 0.123456789 is entered, Excel will display
0.123456789 in that cell. If this value is inserted in a cell within a workbook with “Fixed decimal places”
set to “2”, entering this value will result in a display of “0.12”. However, if a formula multiples this cell’s
value by ten you will get 1.23. However, if the “Calculation > Precision as displayed” option is set to two,
entering 0.123456789 will result in 0.12 being displayed and held as the “hard value” within Excel.
Multiplying this cell by ten will result in a value of 1.20 being displayed and held within Excel as the
“Calculation > Precision as displayed” influenced the values upon entry. Save As To Change File Locations And Names
During the “Save as” operation
you can change the location of
the file. It does not matter how
you get to the “Save” or “Save
as” dialog box. To change the
location of the file upon saving,
dropdown
the
available
locations by clicking on the
dropdown arrow to the right of
the “Save in” window at the top
of the dialog box. If the target
directory is located on the
“Local Disk C:\” drive shown
here, you can change the
location of the saved file to
another location. Where file will be saved
The name of the file If you use the “quick save” icon – the 3 ½” disk icon on the tool bar, you will not be given the save or
the save as dialog box unless it is the first time the workbook is being saved and it has a default name
such as “Book1” or “Book2.” Once a file has been saved, CtrlS, the path File > Save, or clicking on the 3
½” disk icon on the tool bar will result in the workbook being saved to the same location and as the same
file name as it was last opened under or saved as. To change the location of the file or to change the file
name follow the path File > Save As as explained here. If you use the Save As dialog route to save the file
in a new location or under a new name, the old or original file is not removed or deleted. It is there for
later use. Excel Workbook / File Naming Recommendations
There are many ways to name an Excel Workbook file. In this text, if a specific workbook file is used by
a subject area such as the “Vlookup” section, the file name referenced for that section is “vlookup” and
Excel will understand that it is an Excel file through the extension of “xls.” If the Excel workbook file is
for a chapter such as Chapter 3, that reference file may be named “chptr3.” Depending on how your
specific default and viewing preferences are set in Windows Explorer you will see the file as “vlookup”,
“vlookup.xls”, “chptr3”, or “chptr3.xls.” These titles tell you what the subject matter or relational
reference of the workbook is. Had these files been named “File01” and “File02” you would have to look
through every workbook file to find the material referenced for the vlookup function. In most cases
capitalization is not an issue so “Chptr3” and “chptr3” would be the same. If you are working across a
network with a file server such as you might find in a computer lab or business environment,
capitalization may be an issue due to your operating system. Chapter 7, Page 43
Suppose that you are working on an inventory workbook for finished goods and you chose to call the
file “fg inventory” which Excel would make “fg inventory.xls”. This file may contain all activities within
the subject area and would be required for a long period of time as both an active file and as a reference
file. If the file is continuously opened and saved as “fg inventory” and that file is lost or corrupted by
some action such as a hardware failure, software failure, or virus, the file and all of its data may be lost.
However, if you back this file up through a systems back up function on a recurring basis, you should be
protected to some degree. If you add a date to this file name such as “fg inventory 010108” and after
working with it for some period of time such as a week, and use the save as function to save it as “fg
inventory 010808” you have built your own backup into the system. If “fg inventory 010808” is not
available for some reason you may be able to revert to “fg inventory 010108” for usage. Upon opening
the “fg inventory 010108” file, you should immediately use the save as function to save it as your new
working file such as “fg inventory 011208” to protect your base file of “fg inventory 010108”. You
must understand that you have lost data entered only into the “fg inventory 010808” file and will have to
reenter that data but one week’s worth of data is less than all of the data in history of the file.
Suppose that you are required to submit the “fg inventory 010108” file to an instructor or to your
boss in an electronic format such as Excel. You could save the basic file as “fg inventory 010108” and
then use the save as function to save it as “fg inventory 010108 rex” so that upon receipt the instructor
or boss can immediately identity the file as to subject and ownership. If you had to make a revision on the
same day and resubmit it, try something like “fg inventory 010103 rex rev a” to indicate the revision
status and preclude overwriting the original file.
In naming files there are a few constraints. A file name may contain more than one period such as “fg
inventory.010108.xls” but this may confuse several operating systems and users. It is best to avoid
multiple periods. The forward or backwards slashes, “/” or “\”, should not be used as this indicated
different levels of directories to an operating system. Some networking systems and operating systems are
restricted to lower case letters or to not more than 8 characters in a file title. Excel will assist you in this
matter if it detects what are referred to as “special use characters” in a file title. Due to these types of
restriction dates should be put into file names as “010108”, “010108”, or as “Jan 01 08”, not “01/01/08”
or .”01.01.08”. Chapter 8 PRESENTATION
Chapter Outline
Comments
Column And Row Size
Charting
Pivot Tables
Protection Read Only Files And Templates
Drawing On Worksheets
Macros
Macros On Objects Comments
Frequently the presentation of purely numerical data is insufficient for the effective
communication of financial data within a worksheet. At other times you want to
document where the data came from or what the data represents. Excel, as shown, will
accept text entry into the cells easily and effectively and you have vast formatting
capabilities on that text and numerical data. Additionally,
Excel presents you with another very effective text
presentation mode referred to as “Comments.” Comments
are “Popup” blocks associated with a particular cell. They
New Comment,
can contain a wide variety of information and the text
Previous
within the block can be formatted with many of the
Comment,
commands and features found elsewhere in Excel. The
“Comments” data file on the data disk contains examples of Next Comment
what can be done with comments. These examples can be and are
extreme for presentation purposes. Comments have icons on the
“Reviewing” tool bar or menu listing. The “Reviewing” tool bar can be
brought up by right clicking on the menu bars near the top of the screen and selecting
“Reviewing” from the popup menu.
To attach a comment to a cell follow the path Insert > Comment, right click on the target cell and
select “Insert Comment”, or click on the “New Comment” icon on the tool bar – an envelope with an
“aura” or “rays” from the upper left corner. The “Comment Box” will popup attached to the cell.
Contained inside the comment box may be the owner’s name of the Excel program by default. This name
can be left or removed. To remove the ownership name from the comment box simply highlight the text
and delete it with the “Delete” key. Enter the text and data as you desire. The comment box can be resized
to show only part or all of its comments. It can also be repositioned as to the location that it appears in by
dragging it. When the data entry is complete simply click on the worksheet outside the comment box and
the comment box will disappear. Any cell with a comment attached will bear a red triangle in the upper
right corner. You can place your cursor over a comment triangle and the comment will popup and remain
in view until you reposition the cursor. The Reviewing tool bar need not be active or available to read
comments. Section 2, Page 44 Chapter 8, Page 45
If you desire to edit the comment follow the path Insert > Edit Comment, right click on the cell and
select the “Edit Comment” option, or click on the “Edit Comment” icon on the tool bar – an envelope
with a pencil writing on it. In the edit mode, you can still resize or reposition the comment box or
reformat the contents. There is an icon to display all comments and to hide all comments with one key
click. There is also a “Delete Comment” icon on the tool bar. Delete comment is also available through
right clicking the cell and selecting that option.
The text within the comments boxes can be formatted within the font formatting capabilities of Excel.
Simply highlight the text, all or some, and apply the desired formatting to the selected text.
Tip: Spell check will scan comment box contents as well as the rest of the worksheet. Column And Row Size
DO NOT DO THIS ON THE EXERCISE OR PROBLEM TEMPLATES. Many of the templates have
long text strings in them with specific justification and this will cause rapid growth of the worksheet.
The size of the columns and rows can be adjusted within Excel in several manners. The first is to “grab”
the right border of a column identifier or the bottom border of a row identifier with the left mouse button
depressed when it becomes a double headed arrow and drag it (or them), or
collapse it (or them), to the size you desire by moving the mouse. If you highlight
more than one column or row at a time, whether adjacent or not, they will all
resize to the same dimension at the same time. Remember that you can select or
highlight nonadjacent areas by holding the control key down while clicking the
columns or rows with the mouse.
An alternative is to “Autosize” the column or row by placing your cursor over the right border of a
column identifier or the bottom border of a row identifier and double clicking it with the left mouse
button when the cursor becomes a double headed arrow, as shown. This changes the column or row’s size
quickly to the largest item in the column or row. Another method of sizing a column or row is to highlight
the column or row and follow the path Format > Column or Format > Row, as appropriate, and select
what action you want. With the “Width” or “Height” option you will be asked to manually enter a value.
For a column, the default width is approximately the width of 0123456789, 10.0, in default font. For a
row the default height is 12.75. If you resize a column or row to a width wider than the screen or a height
greater than the screen, you can resize it through the path Format > Column or Format > Row and select
“Width” or “Height” and set to a value of approximately 10 for the column width or 12.75 for the row to
return it to near default value.
If you have a column or row highlighted you will also find width and height setting options on the
popup menu if you right click the highlighted column or row.
There are no hidden columns or rows in the exercise and problem templates but there are merged
cells. Merged cells are covered elsewhere in the text. Charting
Excel will assist you in making charts through the Chart Wizard. This wizard is accessed through the
charting icon on the tool bar, shown here, or through the path Insert > Chart.
There are functional examples of charts on the “Charting” workbook of the
data files. One of the most important concerns in charting is picking an
appropriate chart type for your data. In the examples in the “Charting” data file
the information supplied is “Sales item”, “Cost of Sales”, and “Sales
Revenues” (per sale item). This information is charted as examples in several
ways. The use of the two charts titled “Cost of Sales” and “Sales Revenues”,
both pie charts, may be appropriate for the conveyance of the specific information of Cost of Sales or Page 46 Solving Accounting Principles Problems Using Excel for Windows
Sales Revenues  clear, and somewhat attractive. However, when the goal is to show the relationship
between costs of goods sold and sales revenues of the individual items the goal is not accomplished by
these two pie charts as they are relationally sized amongst their contained data. Both charts would be 360
degrees if one contained $1 and the other contained $1,000,000 so the relationship BETWEEN the charts
is not clear. To portray the information required, additional charts must be constructed and a pie chart
format may be inappropriate. By utilizing the Column (Vertical bars) chart, shown on the worksheet in
“Bad Columns” (off to the right), the cost of sales and sales revenues relationship is clear and distinct in a
single chart but, by default, the data is incorrect. When left to the defaults, “Charting” actually summed
the cost of sales and sales revenues into a nonsense number. It is corrected by computing the “Sales
Revenues less Cost of Sales = Gross Profit.” So another column or field was constructed to be referenced
– the “Gross Profit” column. While the charts utilize “Sales revenues” for a title, “Gross Profit” is utilized
for numerical data. You can see this by right clicking on a blank area of the chart and then selecting “Data
Source” from the popup menu. By clicking on the “Series” items of the dialog box you can inspect the
way titles and data matrices can be related. The relationship of sales revenues – total height, to cost of
goods sold, is now clear.
On the “Charting” worksheet is an Area chart and a Surface chart. The area chart infers that there is a
flow over a span of time or a link in the chain of events that may not correct. So, while the chart looks
nice, what does it convey without explanation? For this example data, the Bar chart may be a better visual
presentation.
Tip: To obtain the best guidance as to which chart type to use to convey what information, scan your
textbooks as well as professional publications conveying the same type of information and look at the
chart or graph styles used by the professionals.
The quickest way to build a chart within Excel is to highlight the data range before selecting the Chart
Wizard. For the data file, this is from cell A1 through D6. You can use the data on the Working Chart
Area worksheet of the data file for this exercise. The incorporation of the totals line may add an
additional, unwanted, field that may have to be removed later. Now click on the Chart Wizard icon. The
Chart Wizard will walk you through the process first by selecting a chart type. Excel has many chart types
and by utilizing the “Press and hold to view sample” button at the bottom of the Wizard box you will see
your data in the chart mode selected – one reason why your data was highlighted before selecting the
Chart Wizard icon. Since charts are easily made, edited, and removed, try any chart you desire – except a
valid type for the data. A correction mode will be shown later. With the (incorrect) chart type picked,
click on the “Next” button and the Chart Wizard will ask you to confirm the data range and series. Check
both tabs. If satisfied, click on the “Next” button, if not modify the data as desired or back up to the
previous screen utilizing the “Back” button. The data portrayed in the chart does not affect the source
data. When the “Next” button is clicked, the Chart Wizard will ask for chart titles, axis titles and other
items for the chart. Some of these items may not be available because of the chart type selected, some
may be filled in with information the Chart Wizard “learned” from the data. Enter what you desire and the
Wizard will show you a working model of your chart as you build it. Under the “Data Table” tab of the
dialog box, if presented, you can select an option to show the source data with the chart. A very nice
feature for some data presentations. When satisfied, click the “Next” button and ensure that the chart will
be placed on the current sheet, the default selection by the Wizard and click the “Finish” button. The
Wizard will finish your chart.
If your chart is against an edge you can drag the chart away from the border of the worksheet. If your
chart does not seem large enough – not all of the data is visible, you can change its size by clicking on the
chart and getting “Frame ears” or “handles” to appear on the exterior borders of the chart, then grab one
of these frames or handles with the mouse and drag the chart into a larger (or smaller) size. Be aware that
the chart consists of many objects and clicking “into” the chart and getting “Frame ears” or “handles”
inside the exterior frame means you have grabbed an object in the chart, not the overall chart. Chapter 8, Page 47
Your chart is finished but since you selected an inappropriate chart presentation for your data, your
chart does not clearly portray the information. No problem. Like many things in Excel, your chart is a
dynamic, live, object. Right click into the chart and select “Chart Type” from the popup menu. You are
back into the Chart Wizard and can select a more appropriate chart type and preview it again if desired.
By right clicking the chart you can gain access to many of the chart functions, features and capabilities.
This includes being able to format fonts. You can also add, remove, and reposition labels. Try clicking on
a label, once “Frame ears” or “handles” appear, strike the delete key and the label goes away.
The chart can be copied and pasted elsewhere. The chart is “live” – if the source data changes, the
chart changes. Be sure to save your work. Pivot Tables
A pivot table is the presentation of data with multiple classifications such as district, salesman, and
quantity of various items sold into a logical matrix. The Pivot Tables data file contains a data matrix for
the construction of a pivot table. One of the requirements of a pivot table is that you must have two layers
of classification on the left side, at least one layer of classification on the top, and data at the intersections
of the classifications. This data need not be presorted or arranged, the Pivot Table Wizard will handle that
for you. For the example column A is region – North, South, East, or West, column B contains the
salesman’s name, row 1 contains the items sold, and the data for each region is contained in the matrix
defined by the cells C2 through I32.
As with the Chart Wizard, it is handier to highlight the information before you start the Pivot Table
Wizard. So, highlight the range from A1 through I32. This will incorporate labels, titles, and data. Then
follow the path Tools > Pivot Table and Pivot Table Chart Reports…. The Wizard’s dialog box will
appear asking you what you would like to accomplish and where is the data. Ensure that you are using
Excel data in the upper selection and that a Pivot Table is selected in the lower portion of the dialog box,
then click “Next.” The Pivot Table Wizard should present you with a confirmation that you are using the
range A1 through I32 for the data, confirm this and click “Next.” The next dialog box asks “Where do
you want the table to appear?”, select on a new worksheet and click Finish. Excel presents you with a
blank table that is used to build the table itself. From the Pivot table field list drag the “Region” item to
the top of the pivot table where it says “Drop column fields here”, then drag the “Salesman” item to the
area where it says “Drop row fields here”, then drag each of the sales items into the center grid where it
says “Drop data items here.” As you drop your items on the table Excel will start working. As you drop
each item, the item will change go bold print in the list to indicate that it has been used.
The resulting table will show you who is selling what items where as well as the totals sold for the
item and the totals sold in each region. Through the use of the filters, activated through the drop down
arrows on the titles, you can control what is displayed. Pivot tables are memory intensive events for the
computer. If you are limited as to the number for fields or it takes more than a couple of seconds to
generate this table that can be expected. Page 48 Solving Accounting Principles Problems Using Excel for Windows Protection
In the “Protection” data file on the data disk search for the text strings “Hi!!!!” and “You found it!!!!” and
you will find them in cells R555 and R556 respectively. However, there is one more occurrence of each
on the worksheet. The “Protection” worksheet has had the cells round cells Y571 and Y572 formatted to
“Locked” and “Hidden” through the “Format Cells” dialog box’s “Protection” tab, shown here, and then
the worksheet was protected through the path Tools > Protection > Protect Sheet. There is no password so
you may remove the protection if you desire. Even clicking into
the cells will not reveal their contained text in the formula input
window of Excel, but that text will be visible in the cells when
the cells are highlighted. This text has been additionally hidden
with white text formatting to make it harder to find, had it been
black or another color text, it would have been visible without
highlighting.
Why have a “Protection” capability within Excel? You have
assembled a large worksheet of inventory data consisting of thousands of lines of data and are about to
distribute it for a meeting to be held in three days after the participants have had time to review it.
“Protection” will preclude them from modifying their received file providing you a common reference
document in the meeting. Or, because you are building templates and standardized worksheets and
workbooks for the organization, you can structure the worksheet or workbook as you like and then format
the areas you do not want modified leaving the user input areas open and available. Starting at the cell
A34 and continuing down and right, part of the “Demo File” is pasted. The entire range of the demo file
was protected, (for speed). Then the protection was removed from cells with the yellow highlighting by
clicking into one cell and holding the Ctrl (Control) key down and clicking into the rest of the cells one
by one. Once all the highlighted cells were selected, one cell was right clicked and through the “Format
Cells” option and the “Protection” tab, the “Locked” and “Hidden” formatting was removed. Then the
worksheet was protected – without a password through Tools > Protection > Protect Sheet. As an
exercise, enter your name in the “Name:” entry area identified by the yellow filled highlighting. Not a
problem. Try to change the “Instructor:” title to “Professor:”. Excel presents you with an advisory that the
worksheet is protected and that capability is not available. This maintains standardization of the
worksheet. The rest of the demo problem is protected so that data can only be placed in the yellow
highlighted cells. Restricting your ability to edit or change the data.
Note: None of the exercise and problem templates are protected so you have maximum flexibility
within the data files. So exercise due care when working with these data files.
“Protection” requires several actions to be effective. First, the general default of Excel is that every
cell, if protection is invoked on the worksheet, will be protected. A good, safe, default but seldom a valid
statement. Since you want to insure the configuration of all of your cells, select the entire worksheet
through the “Select all” button above the first row number and to the left of column A indicator or use the
keystrokes CtrlA. Next, right click the worksheet to get the popup menu and select “Format Cells” or
follow the path Format > Cells to get the “Format Cells” dialog box, and
select the “Protection” tab. Then, with the assumption that you want to
protect most of the worksheet, or remove the checkmark from “Locked”
allowing all the cells not later identified to be utilized by the user to be
unprotected then click “OK.” Now, if you selected “Locked” while the
worksheet was selected, click into the cells to select the cells you want the
user to have access to and, with access to the “Format Cells” dialog box,
remove the checkmark from “Locked.” You can access and format more
than one cell at a time by holding the Ctrl (Control) key down while
selecting cells by clicking on them. Again clicking “OK” at the completion
of each selection process. If you unlocked all of the cells and then select the Chapter 8, Page 49
few cells you want to protect, you would place a check mark in the “Locked” window after selecting the
cells you wanted locked. Then you would click “OK” to complete the selection process.
After all the cells have been configured as “Locked”, or not locked by removing the checkmark in
locked, follow the path Tools > Protection > Protect Sheet. The resulting popup dialog box will ask what
privileges you wish to make available to the user and if you want to utilize password protection. If you do
not provide a password, the simple process of Tools > Protection > Unprotect Sheet will remove the
protection. This was the level provided on any protected worksheets on the data disk. If a password is
utilized, make sure you can recover the password later as recovery of the worksheet may be very difficult
without it. Clicking “OK” will complete the process and activate the protection. The workbook now needs
to be saved to record this configuration into the file on the computer media. This does not make the
workbook a template or make the workbook a read only document.
Tip: By accepting the defaults of allowing a user to select locked and unlocked cells you can copy a
protected worksheet and paste it into a new document recovering part of your “frozen” work if you lose
the password. Read Only Files And Templates
The data file titled “Data Form” with the extension of “XLT” is a special type of Excel file called a
“Template.” This type of file has a special property associated with
it in that, when opened within the later versions of Excel, it will
create a standard Excel file titled “Data Form1” when opened the
first time. When opened the second time it will create “Data
New Workbook Icon
Form2.” These files can be saved by their default names or can be
saved with a new name and in a new location by using the File >
Save As path to alter the title and / or path. In some versions of Excel you will receive an advisory screen
popup that states this is a template and has “read only” properties. In these versions of Excel simply
acknowledge the advisory popup and follow the path File > Save As to save the file as a new name and,
if desired, to a new location.
This feature of read only capability was built into Excel to allow you to build one standard reference
and structure file, a template, and be advised that you do not want the core or base file to be populated
with data since you will using that structure again with new data. Templates are frequently used for files
that are used time and time again with one structure and new data, such as time sheets, travel / expense
reports, and job logs.
Note: The use of “Exercise and Problems Templates” as provided with this book simply means a
standard data file that everyone in the class, course, or program will be using. The data files
accompanying this book are not read only and can easily be written over. Drawing On Worksheets
The first and
foremost item to
remember in this
Text Box Icon
section is that most, if not all, of these items are “on top” of the
worksheet. They are not in cells or attached to cells. You can enter data beneath them and around them
without a problem. To bring up the Excel “Drawing” tool bar right click on any menu bar and select
“Drawing” from the options. On the “Drawing” tool bar, Excel gives you many preformatted and
adjustable capabilities as well as some degree of free hand modifications. The “Drawing” tool bar is
shown here with the standard or default options. The first item to be addressed is the ability to draw
arrows. To draw an arrow, click on the arrow icon and then click on the point where you want the arrow
to appear to be coming from. If the point of origin is not visible on the screen after you activate the arrow, Page 50 Solving Accounting Principles Problems Using Excel for Windows
use the scroll bars to navigate the worksheet or hit “Esc” (Escape) two or more times to cancel the “Draw
Arrow” command while you reposition the screen. Place your cursor over the point of origin and click
and hold down the left mouse button. Now move the mouse cursor to the destination or target, if the target
is not visible on the screen move your mouse cursor towards the general direction and Excel will scroll
towards it. When you have the mouse cursor at what will be the arrowhead in position, release the left
mouse button and the arrow will form. If the “weight” or thickness of the line is other than what you
want, while the arrow is still “active” as indicated by empty circles at each end, click into the icon with
the numerous different thickness lines on the “Drawing” tool bar and pick your choice. If the texture of
the arrow is less than desirable, while the arrow is still active, click into the “Textures” icon, usually next
to the “Weight” icon, and pick your choice. You can also right click the object and get a
popup menu which will give you access to the “Format AutoShape” dialog box. If the
arrow is not selected, simply click on it when your mouse cursor is placed over it or
immediately around it and the cursor becomes a fourheaded arrow icon.
Once the arrow is drawn, place your cursor back on top of it and move the cursor
slightly until you get a four headed arrow for a cursor. At the point that the cursor
changes, left click the mouse to “grab” the arrow then right click it and a popup menu
will appear. From this menu select “Edit Points.” With this option active, you can place
your cursor on the arrow at any point and bend it around. To flex the arrow, move your
cursor over the arrow and when it becomes a “crosshairs” type presentation, left mouse
button down and drag it around. The point of origin and the destination will remain the
same but the path will change. If the corners are abrupt, place your cursor on a corner,
right click the arrow to get the popup menu and select “Smooth Points.” At this point on
your arrow the corners should be rounded. If you place your cursor over a corner point,
shown only while the arrow is in “Edit” mode, you will see a “line handle” appear,
grabbing and moving this line handle changes the aspects of the corner. Whether you
have an arrow or line selected, they work the same; you can change the weight, pattern,
and color through the icons on the “Drawing” bar.
There is a text box on the tool bar, it is a white sheet of paper with a letter “A” in the upper left corner
and lines making it appear like a newspaper. Click and release on this icon and then click into the
worksheet and keep holding the left mouse button down. While you are holding the left mouse button
down, drag the cursor away from the point of origin to create a text box. At any time, you can drop the
text box drag by releasing the left mouse button and start to enter text into the box. If the box was created
to wrong size, click near the borders to get the handles or ears active, then grab a handle or ear and drag it
to the correct size. To move the box, select the box, then move your cursor near the edge until it becomes
a four arrow headed object, while the cursor is a four headed arrow, left mouse button down and drag it to
the new location. Just like dragging other objects such as arrows and lines. Spell check will check the
spelling inside text boxes. You can fill the box by selecting it and then selecting a “Fill” color. You can
change its borders by selecting the box and selecting a line weight and texture from the tool bar while the
box is the selected object. Many of the screen prints in this book were done with text boxes and arrows
from the drawing tool bar. Additional formatting options are available by right clicking the text box and
then selecting “Format Text Box” from the popup menu that appears.
There are preformatted rectangles and ovals on the drawing tool bar. Simply click on the icon then
click into the worksheet and start to drag them around until you get the shape you want. You can drag
them to a new location by moving your cursor near an edge looking for the four headed arrow before left
clicking down, just like arrows, lines, and text boxes.
Hint: If you hold the shift key down on the keyboard while creating a rectangle it will remain a
square as you resize it. If you hold the shift key down while creating an oval, it will remain a circle while
you resize it. Holding the shift key down while drawing lines and arrows will give you straight lines. Chapter 8, Page 51
To delete an object, select it by clicking on it when the mouse curser becomes a four headed arrow
and hit the “Delete” key. Using the cut command will allow you to paste it somewhere else – once. Using
the copy command will allow you to paste it in numerous locations. It can be repositioned by dragging it
once placed.
Hint: If you want the object moved from its original location but placed in numerous other locations
“cut” it from its original location. The paste it into its next location. While Cut to Paste is a onetime
event, the object is still selected by default. Now you can simply copy it with CtrlC, Edit > Copy, right
clicking it and selecting “Copy” or any of the other copy methods and then move to the next points to
paste it repetitively without going back to the very first (original) location to delete it.
An important issue with drawings, images, clip art, and pictures is their positioning within the
presentation’s other objects. On the “Drawing” data file is a color JPEG image of a pencil. Click on the
pencil to select it. Then right click the pencil. From the popup menu select the “Order” option. This
establishes where the image will be in relation to other objects. From the options select “Bring to Front”
and the pencil will no longer be hidden behind the other objects. Many of your objects within Excel and
Word have this option available to them.
On the tool bar and through the path Insert > Picture you can insert pictures and clip art onto the
worksheet. This is a great feature to document fixed assets or merchandise for inventory type issues but
the file sizes get large very quickly. WordArt is also available for use through the drawing tool bar as well
as the path Insert > Picture > WordArt. Inserted here is a scanned image of a pencil. It is saved as a JPEG
file and inserted into the text through the same process as it was inserted on the Excel Drawing data file.
Do
not
miss
the
opportunity to play with
the “Insert Diagram” or
the “Organizational Chart”
tool.
Hint: These tools can be very effective when used well. It is recommended that you review
professional magazines, newspapers, and textbooks to get examples of good presentation techniques.
None of the examples in “Drawing” data file would be used in a professional format, they are provided as
examples of the power (and the abuse of power) available in Excel. Macros
Macros are small programs that you can build out of keystroke modeling – you record them by doing
what you normally do for later, repetitive, use. When needed you activate the macro and have it do the
task for you. To record a macro ensure you know the keystrokes and commands you desire to utilize in
the macro. If you record an error in a macro you may have to edit the macro to remove the error or
rerecord it. If not corrected, the macro will commit that error every time it is run. However, if the
correction is made in the recorded keystrokes, it just consumes time and memory. You will build a simple
macro for demonstration purposes that formats the cell to bold, italics, and underline in a single event.
Place your curser into cell A10 of the Macros data file to make that cell the active cell. Bring up the
macro dialog box by following the path Tools > Macro > Record New Macro. You will be asked for the
name of the new macro and where you would like it stored. The name should not have spaces or special
characters in it. Excel will tell you if the title is unacceptable. The demonstration macro, which is on the
Macros data file is named BoldItalicsUnderline so try BIU for your macro. In the “Store Macro In”
window select “This Workbook.” This will contain the macro to the data file allowing it to move with this
Excel workbook. The dialog box asks if you want to assign a control key to the macro – enter the
character “m” as a lower case letter without striking any other key. Click on the OK and Excel will
commence recording the macro. Page 52 Solving Accounting Principles Problems Using Excel for Windows
Tip: To see if a control key is assigned a function in Excel,
click into Excel and try the key sequence. If Excel gives you a
dull thud response, the key command was not recognized, or it
is unavailable at the moment.
Once Excel starts recording the macro, everything you do
will be recorded. Click into cell A2 then click the “Bold” icon,
the “Italics” icon, and the “Underline” icon on the tool bar.
Then click on the stop recording button on the macro dialog
box the appeared on the screen. This is a square button as
shown. When you click the “Stop Recording” button your
macro is stored and available for use. If you do not have a stop
button showing you can utilize the path Tools > Macro > Stop
Recording. Macro Dialog Box Macro Stop Button
Now click into cell A3 and run the macro through the
keystrokes Ctrlm or through the path Tools > Macro > Macros
and click on the “BIU” select and click run. The error with the macro is that it goes to cell A2 and puts
bold, italics, and underline on cell A2. This is because the first thing you told the macro to do was go to
cell A2. You could have avoided this by being in the target cell at the time you recorded it – this was
intentional. Now we will edit the macro to preclude it from moving to cell A2 each time. Follow the path
Tools > Macro > Macros, single click BIU, then select “Edit” from the button choices. The macro tools
and the Microsoft Visual Basic window will open and you will see the macro command strings. One of
the command strings reads “Range("A2").Select.” Highlight and delete this one line then close the
window. You have now edited (corrected) the macro. Click into cell A3 and invoke the macro with the
keystrokes Ctrlm and cell A3 should become bold, italics, and underlined.
Macros are powerful tools. They can be absolutes – go to cell A2 – as we originally recorded BIU. Or
they can be focused on the target cell or the active cell as BIU was edited to. Macros will format
worksheets, enter data, run spell check, and almost any other repetitive task you have.
You can save the macros you build to the workbook or in a personal macro notebook. If the macro is
saved to the personal macro notebook, it may not be available on the workbook if the workbook is
distributed or transmitted.
Clue: When a workbook contains a macro, there may be a warning about its presence. Unless you
know the nature and intent of the macro it may be best to decline the macro’s enablement. You may be
able to review the macro’s properties while disabled through the edit function on the macro dialog box.
Tip: You can assign a macro to an object. This is addressed elsewhere in “Macros And Objects.”
Hint: Macros are usually relational. To ensure that the macro goes where you want it to as the first
step try CtrlG and select “A1” as the destination. This will ensure that the macro will go to the
appropriate cells since the starting point is always cell A1. Macros On Objects
Once an object is drawn in Excel it is possible to attach a macro to it. By doing this, clicking on the
object will activate and run the macro. The “Macros On Objects” worksheet in the “Macros On Objects”
data file has a pencil as an object on it, and a macro is attached to it. Place your mouse cursor over the
pencil and the cursor will become a hand. At that point click the left mouse button to activate the macro.
You can reset the macro by running another macro which is attached to the “Reset pencil macro” text
box.
To accomplish this task, click into the “Macros On Objects Playground” worksheet. As an exercise,
draw or place an object on the worksheet. Then record a macro by following the path Tools > Macros > Chapter 8, Page 53
Record Macro as discussed in the “Macros” section. Assign a name such as “ObjectText” to the macro.
No shortcut key is necessary. Once the macro starts recording, utilize the Ctrl – G sequence of keystrokes
and instruct the “Go To” dialog box to go to cell A1. Now click into a cell below your object and type in a
short text string such as “This object works just fine.” and press and release the “Enter” key to complete
the text entry. Click back into the chosen cell to make it the active cell. Then highlight the cells about 5
columns across and two or three rows down. While still within the macro, follow the path Format > Cells.
Within the “Format Cells” dialog box, on the “Font” tab, select “Script” for a font, then adjust its points
or size to “18”. On the “Alignment” tab, select “Merge Cells”, “Wrap Text” and then, set both horizontal
and vertical placement factors to “Center” and click “OK.” Now click on the macro recorder stop button
or follow the path Tools > Macro > Stop Recording.
The macro is recorded. You can edit it as explained in the “Macros” section if needed. Right click on
the object you built or inserted. From the options select “Assign Macros” and the select your macro from
the list shown and click “OK.” Then click on your object to see if it works.
The pencil has a macro assigned to it. You can right click the pencil and see which macro is assigned.
You can access the macros through the path Tools > Macro > Macros. By selecting the macro assigned to
the object, you can read it through the “Edit” button. You can click on the pencil to run its macro. To reset
the pencil macro, click on the pencil’s macro reset button. Lower in the “Macros On Objects” worksheet
there is a “test” which shows to powers of macros and the convenience of objects as buttons. There is a
reset button for the test.
Hint: Macros are usually relational. To ensure that the macro goes where you want it to as the first
step try CtrlG and select “A1” as the destination. This will ensure that the macro will go to the
appropriate cells since the starting point is always cell A1. Chapter 9 PRINTING ISSUES
Chapter Outline
Page Setup
Page Break Preview
Print Area Print Preview
Printing Page Setup
The printed presentation of Excel data is dependent
on page setup and page break positioning. The access
to the Page Setup dialog box can be accomplished
through the path File > Page Setup. The dialog box
the pops up appears here.
There are four tabs on this dialog box. The
“Page” tab controls portrait or landscape view and the
important function of “Fit to.” With the “Fit to”
capability you determine the pages that your Excel
worksheet will print by width and height. If you print
a file only to find that one column of eight is
contained on a second sheet, consider telling Excel to
print to 1 sheet wide. You can always over guess the
pages needed without penalty. For example, if you
have four columns of 1” wide each and 40 rows approximating 0.25” in height each, you can instruct
Excel to fit it to 10 pages wide and 20 pages tall and Excel will print the document on one single sheet
since it is approximately 4 inches by 10 inches in size. If you set the “Adjust to” selection to a value such
as 500%, Excel will obey that value and print the results. Taking your 4 columns and 50 rows and setting
“Adjust to” to 500%, Excel will consume many sheets obeying your orders as the printed columns will be
approximately 5” wide each (1” column X 500%) resulting in total columns width of approximately 20
inches in six and half inch width print area paper. The 40 rows are approximately one quarter of an inch
each by default. When Excel multiples this value by 500% as instructed the sheet becomes approximately
(0.25 inches X 40 rows X 500%) 50” long on a default print area approximately 9” long. The length of
approximately 50” will be printed in a print area approximately 9” long resulting in about 6 sheets in
length. This single print out will consume approximately 24 sheets of paper – as instructed. Excel will
obey the last choice selected between the “Adjust to” and “Fit to” options. Only one can be invoked at a
time. “Print Preview” and then “Page Break Preview” can be accessed from this tab as well. Print preview
can be accessed through the “Print Preview” icon.
The “Margins” tab controls the upper, lower, left and right margins as well as placement upon the
page. By selecting the center horizontally and vertically, the data can be centered on the page for a very
nice presentation, if appropriate. Seldom is financial data such as journals and ledgers centered both Section 2, Page 54 Chapter 9, Page 55
vertically and horizontally. The margins of the Excel templates has been defined as 1” top, bottom, left,
and right. If your printer cannot handle this margin value you can adjust it through this tab.
The “Header/Footer” tab allows you to place header and/or footer information on each printed sheet.
This capability is on each of the templates. The template data shows the file name, the worksheet name,
the page of pages, the date, and the time in each footer. This has been included so that the compilation of
multiple papers is easier. Click onto the “Header/Footer” tab and click on “Custom Footer.” The data
contained within the footer will be shown. Notice that these are codes calling for data during the printing
process making them dynamic or live. Holding your cursor over any of the numerous icons for several
moments will reveal that icon’s function. There are numerous default headers and footers that can be
utilized through the dropdown menus.
The “Sheet” tab contains numerous data bits that are very useful and versatile. The first bit of data is
the established print area of the worksheet in the top data window. With the demo file this is B2:K176, or
in English, the print area starts at cell B2 and contains all of the cells through row 176 as well as all of the
columns from B through column K. This tab also controls whether a specific row or set of rows will
appear at the top of every sheet printed and if a specific column or set of columns will be printed at the
left of each page printed. Assume that you are printing inventory reports running 125 pages. They contain
part number, location code, description, quantity on hand, quantity on order, quantity in WIP, and value.
By placing the titles row on each sheet through this function, you will retain column identity without
building it into the worksheet. This feature is utilized in the Excel templates provided to put your identity
and course data on each printed page if more than one page is used for the printed results to retain identity
of the student and exercise or problem. This transposes to columns to identify rows as well. You may
invoke columns and rows at the same time. Also on this tab, you can select the order in which the pages
will print.
If you are printing data that is presented by rows and its taking four sheets wide by four sheets high,
you may want to print the across and then down so that the first four sheets address the rows contained in
the early rows before introducing the second set of rows presented on pages five through eight. Gridlines
on the printed report, not the viewing screen are turned on or off through this tab. Many people do not like
grid lines on their data. The templates have them turned off but this tab can change that for the individual
template. This does not control the grid line presentation on the display. The grid line on the screen is
controlled by the path Tools > Options > View. If viewed through that path, they will not print. Neither of
these options affects cells that have borders placed on them. Page Break Preview
“Page Break Preview” is found through the path View > Page Break Preview from the menu bar or from a
button on the Print Preview screen. The “Print Preview” icon is the white
sheet with the folded corner and magnifying glass on the tool bar shown here.
“Page Break Preview” will show how the pages are going to print with the
current printer and page settings. The page breaks can be moved and
repositioned from within this view.
Excel Print
Hint: When resetting page breaks start in the upper left corner and work
Preview
down and to the right if your print order as shown in the Page Setup is down
Icon
and right. If the print order is across and then down, reset page breaks in that order. Each page setting
affects the following pages so setting page 10 to an ideal presentation and then changing page 9 may
cause Excel to reset your work and preferences on page 10. Starting at the bottom may also result in too
much on page 1 or too little.
To close “Page Break Preview” follow the path View > Normal from the menu bar. “Page Break
Preview” is dependent upon page setup and printer functions. Most printers, but not all, can print to ½” of
each edge of an 8 ½ X 11” sheet of paper. There are printers that require larger than 1” margins. Page 56 Solving Accounting Principles Problems Using Excel for Windows
Tip: The Excel templates for the exercises and problems provided on the computer media have been
designed to provide the largest application usage so the standards of 1” margins are seldom violated. Print Area
As addressed previously, Excel can and will consume as much paper as instructed or needed (if left to
default setting) to print a worksheet. Sometimes this is not necessary. For example, you have constructed
a large worksheet computing the data but the actual data presentation is only one physical page. In
construction of the worksheet you have identified various areas to clarify your calculations and work.
Contained in these areas you have extensive detail and support material that are not required for the
printed presentation. If not provided with parameters for printing, Excel will print in an 8 ½ X 11” format
for standard bond paper a range from the upper left cell, usually cell A1 in a rectangular or square profile
through the further right and lowest cells with data entered. So, if you have data in cell A1, cell Z1, and
cell A55, your print area may default to A1 through Z55. Some versions of Excel may trim the last page
or two from the printout if there is nothing on that page, to the right of that page, or below that page. It is
safer not to assume what Excel will and will not print by defining your print area. In your large worksheet
example, all of your supporting calculations are going to be printed and the printing will normally be in a
vertical then left to right format in portrait mode.
Redefining the print area is easily handled within Excel. The Demo File has a predefined print area to
preclude this and can be used as an example. On the Demo File worksheet of the Demo File, you will
notice that the worksheet is outlined by a gray border. This border has no function with the print area, it is
merely provided by the author as a visual reference of the print area. Anything you enter inside the gray
border will be printed on the sheet. Any item on or outside the border will not be printed on the worksheet
printout. To examine the worksheet’s predefined printed area follow the path File > Page Setup > Sheet
and read the values contained in the upper window of the “Print” area tab. This value should be
“B2:K176” or from cell B2 down and right through and including cell K176. If you know the print area
you want, you can enter it directly into the print area window on the “Sheet” tab of “Page Setup” by using
cell references and separating them by a colon. However, you can also define the print area from the
worksheet itself. To demonstrate this, click into and make cell A1 of the demo file the active cell. When
this is done the cell will have a heavy border around it. Now, press and hold the left mouse button down,
while holding the left mouse button down, sweep the mouse cursor down and to the right to highlight all
of the cells down to and over to the right to include cell L20. Once the cells are highlighted, release the
mouse button and do not click back onto the worksheet or your highlight will be lost. Follow the path File
> Print Area > Set Print Area and click on the “Set Print Area” option. Now you have defined the print
area without entering data or keystrokes. Utilize the “Print Preview” button or icon, discussed elsewhere
in this book, to see the redefined print area. Most of the Demo File is not available in the print version
since your print data selection is from cell A1 through L20. Also, the gray borders are part of the print
area. Close the Print Preview screen through the “Close” button and reset the print area by following the
path File > Page Setup then selecting the “Sheet” tab and entering “B2:K176” into the “Print Area”
window overwriting the “A1:L20” value and then clicking OK.. All of the Excel templates have the print
area defined for reasonable presentation of the exercises and problems.
You can also print areas that are not adjacent to each other. For example, assume that you want to
print the range from B2 through L50, and the range from R55 through Q90. Open up the “Sheet” tab of
the “Page Setup” dialog box, click into the “Set Print Area” window, click into cell B2 on the worksheet
and highlight down and right through cell L50, now type in a “,” (Comma) to define and instruct Excel
that you have identified the first area and are now identifying a second print area, then click into cell R55
and highlight through Q90 and click “OK.” Excel will identify these two ranges in the window separated
by a comma as “B2:L50,R55:Q90”. You can define multiple ranges this way. Remember that after
highlighting and identifying the first area, you must insert a comma. You can use the arrow keys and / or
the scroll bars to move to the second, third, and following areas. Chapter 9, Page 57
You cannot define one page to be printed portrait and another landscape within the same worksheet of
Excel. Your solution may be to have one worksheet inside the workbook setup with landscape and the
second worksheet setup portrait as the setup is to the worksheet, not workbook. Then simply have “Look
to” formulas on one worksheet referencing the source worksheet so data does not have to be entered more
than once. When this is accomplished, you can define how each worksheet will be printed without
affecting the defaults and settings of the other worksheet. Once the first worksheet is printed, select the
second worksheet and print it. Or, once the first page is printed, highlight the data for the second page and
set your preferences through the “Print” dialog box by following the path File > Print and set the values
and presentation techniques you desire – including landscape for the second print run. It is best if you
always preview the worksheet before printing to avoid paper consumption.
Tip: Once printed, review the document to insure that it is correct and complete. Just because a
computer printed it does not establish or verify it’s accuracy. Print Preview
By clicking on the “Print Preview” icon on the tool bar, a white sheet of page with the upper right corner
turned over and a magnifying glass overlaying it, you will access the print preview of your document. If
there is more than one page, there will be a scroll bar on the right side of the screen. If the print preview
presentation fills the page you are probably in the “Zoom” mode. Simply click on the sheet or on “Zoom”
on the menu bar and you should be presented with an overall presentation of your document. If there is
more than one page or it is larger than the screen you should have scroll bars to maneuver through the
document.
Clue: In order for “Print Preview” to work, your system must have a printer installed in the operating
system. The printer need not be available but Windows, Word, and Excel all provide “Print Preview”
based in the defaults and settings of the currently selected printer.
From this screen you will see how the document will appear in its printed mode. This includes
features such as headers and footers as well as margins. You can also adjust the page while in this
presentation. By clicking on the “Margins” button at the top of the menu bar you will be given a
presentation of current placement of the margins and columns. You can adjust the margins and column
widths at this point by placing the mouse cursor on the margin or column marker and getting it to change
to a double headed arrow and then pressing and holding the left mouse button down. While the mouse
button is being held down, drag the cursor left, right, up, or down, as desired. The margins at the outer
areas of the sheet at the top and bottom of the sheet are those of the page and are defaulted to the values
found under the path File > Page Setup > Margins. These margins are fully adjustable but will usually be
at or outside of the file margins. If the worksheet data is printed on top of the header and footer data,
utilize this presentation to move the data margins off the header or footer margins. The area between the
page margins and the file margins is where the headers and footers are placed at the top and bottom. Print preview icon Page 58 Solving Accounting Principles Problems Using Excel for Windows
There are numerous changes you can make within print preview besides margins and column width.
By clicking on the “Page Setup” button or icon you can adjust almost every item accessible through File
> Page Setup. Some are grayed or faded out as they are not available at this time but selection the Page
Setup dialog box through the path File > Page Setup will usually present them as these particular items
must be set from the normal view mode or from the page break view mode. Remember that under the
“Margins” tab within “Page Setup” you can center your page horizontally, vertically, or both if you
desire. Printing
Excel will print from several different commands and paths. The quickest while using the templates is the
“Print” icon on the tool bar. This symbol is the “Printer.” By clicking on this icon, Excel and Microsoft
Windows will print the currently active document utilizing the document settings under “Page Setup”
through the default printer as established by the Windows Control Panel settings for printers. When
printing other worksheets this command may result in excessive paper being used in the printing process.
The difference is that the Excel templates accompanying this book already have predefined layouts and
paper breaks. For original worksheets, the layout and print area should be verified and adjusted as
described in the “Print Area” and “Print Preview” sections elsewhere in this book.
If you do not desire the Windows default printer you can follow the path File > Print and a dialog
box, shown here, will appear: “Quick Print” icon to
Windows default printer
Installed printer selections
Specific pages or all pages
Number of copies From this dialog box, you can select printers installed on the desktop system, on the network, or fax
drivers that may be installed. If your worksheet consists of multiple pages, you can select any relevant
range of pages. For example, if your worksheet consists of five pages, you can select “All” to get pages 1
through 5, select from pages from 2 through 4, select from 3 to 3, or elect to print only the highlighted or
selected area through the “Selection” within the “Print what” box below the Print range box. You can also
determine the number of copies desired and whether those copies are collated or not. The default is to
collate multiple prints. This may not be desired if presenting a large amount of information to a group as
they may become involved in scanning the depths of the document during your presentation of page 1. By
not collating the documents, you can pass single sheets out, containing exposure without having to resort
the document. After all selections have been made, printing is initiated by clicking “OK.” Excel and
Windows will normally print the document to the selected printer without further action. Chapter 10 INVENTORY
Chapter Outline
Filter
Sort
Master Sort Column
SumIf CountIf
Subtotal
Vlookup Filter
Another excellent Excel tool is the “Filter” function. When a worksheet is selected or when specific
columns are selected and the path Data > Filter > AutoFilter is followed filters will be imposed on the
worksheet by Excel. A filter is a tool to screen and restrict the presentation of data. In the “Filters”
worksheet of the Filter data file on the data disk “Filter” has been established. For this worksheet,
columns A and B have had the “Filter” function imposed on them. Unless restricted, through
“AutoFilter”, Excel will put a filter on every column, why this may be a bad idea is shown in the filter
selections of column B where every individual quantity has its own selection criteria. While this does not
hurt, even if not used or appropriate, only Column A is really relevant or usable.
When filters are imposed on a worksheet, Excel puts an additional button on the top of each column
with a filter. This button has an arrow pointing down on it and is contained in the first row of the column.
By clicking on this arrow, you activate a dropdown menu showing a complete listing of every text and
numerical data entry in the column. In the Filter data file, click on the drop down arrow in column A and
click on “Apples.” All of the items not meeting the criteria of “Apples” in column A are hidden and every
row meeting the criteria is moved to the top of the worksheet. This compresses the data and hides the nonrelevant data. By clicking on the dropdown arrow again and selecting “All”, all of the data is presented in
the order that as held before the filtering action was invoked. Utilize the filter arrow for column B and see
the number of options available due to the large number of unique values contained in the column. The
filter database is automatically created by Excel. As noted earlier, this may be unnecessary.
Filters can be placed on every column in the worksheet and multiple criteria filtering is possible. In a
worksheet containing parts locations by state, county, city, warehouse, row and rack, you could filter
column A to only your part number, then filter column B to the State you wanted, column C to the
county, column D to the city, column E to the warehouse. That could easily and quickly take thousands of
rows down to one or two. With only one or several columns containing active filters Excel will maintain
row integrity of data. That is, Excel moves the entire row when a filtering action is imposed on one
column of the worksheet. The “Multiple Filters” worksheet on the “Filter” data file is an example of this
capability.
Filters are removed in the same manner they are invoked. Follow the path Data > Filter > AutoFilter
and they will all be removed. This is called a “Toggled” function – selecting it while it is active turns it
off, selecting it while it is off turns it on. Section 2, Page 59 Page 60 Solving Accounting Principles Problems Using Excel for Windows Sort
Excel will also allow multiple layer sorts. In a “Sort” function no data is hidden and the order of
presentation has several options. In the Sort data file there is a Sort worksheet to show this function. With
Sort it is necessary select the worksheet, the columns, the rows, or the cells that you would like sorted. A
caution that Excel will usually provide for you is that if selecting less than entire rows, Excel will sort
only the selected cells. This may destroy the integrity of the data if completed. The “Undo” arrows may
recover your data if a “Save” operation has not been initiated.
The most convenient way to utilize “Sort” is with titles in row 1 and row 1 frozen through Window >
Freeze Pane while row two is highlighted. This will cause Excel to freeze row 1 and help row 1 to be
identified as labels. The safest way to utilize the sort function is by selecting all cells within the worksheet
by clicking on the “Select All” button above the row 1 number and to the left of the column A letter. After
the worksheet is selected follow the path Data > Sort and a popup menu will appear. Excel will usually
assume that the first row of a frozen pane display is titles so titles should appear as the selection options.
If your presentation shows column letter identification, simply select the “My List Has  Header Rows”
option on the lower left corner of the dialog box. From this menu select Sort by > Part Nbr (Number)
through the dropdown selection pane, Then by > State through the second, or middle, dropdown pane,
Then by > County through the third, or lowest, dropdown pane. You can select whether you want them
in ascending or descending order by clicking on the buttons to the right of each sort level as you desire.
Clicking on “OK” will result in a sorted worksheet by your selected criteria. Worksheets can be sorted
time and time again in different manners without restrictions. This will change the order of the rows,
which, if important may not be recoverable unless a technique such as “Master Sort Column”, discussed
elsewhere, is used.
If the worksheet is not saved upon exiting, it will be in the same configuration and data arrangement
as the last save operation point. Master Sort Column
The “Master Sort Column” is a technique rather than an Excel function or formula. If you are going to
sort worksheets you may be required to return them to their original order for a function such as auditing.
For example, you have entered 41 inventory items into an Excel titled Master Sort Column (this is done
for you on the Master Sort Column data file) worksheet for data manipulation. In the process of working
with the data you discover what appears to be data entry errors that you want to verify. Once sort has been
invoked on a worksheet it is not easy to restore the input or original order. So try putting a “Master Sort
Column” on the worksheet and possibly additional sort columns as required for other reports, sort
preferences, or users. To do this, insert a column at column A by clicking on the Column A identifier.
Then right click on the highlighted column and select “Insert” from the options. This will insert a new
column and “push” the highlighted column one column to the right. You can also insert a column, once a
column is highlighted, through the path Insert > Columns. Excel will insert a new column and push the
other columns to the right. The only time Excel will not do this is if every column to the limit of the
worksheet has been utilized. Now title that new column “Master Sort” (or anything else you desire) and
insert indexed numbers starting in the first data row. You can utilize the row number, such as “2”, as your
first number to keep a match between worksheet row numbers to master sort numbers as shown in the
Master Sort Column data file. Drag or fill this column through the data entry range. Now, whenever you
need it back into data entry order simply select the worksheet and sort by the Master Sort column, column
A, in ascending order, and it is back in data entry order.
Suppose that the Director of Sales wants the inventory file in Part Nbr (Number) order frequently.
Insert a new Column B pushing the existing column B one to the right, and label the new column “Sales
Sort”, sort the worksheet by Part Nbr (Number). Now, while in sorted presentation, put sequential Chapter 10, Page 61
numbers into column B as you did in column A with the master sort. Now any time the Director of Sales
wants his presentation, select the worksheet and sort by column B in ascending order.
Hint: Remember that as the data within the worksheet changes, the sort may need to be run and the
sort index may need to be refreshed. Any party requesting special presentation of the data can have their
own sort row and these columns need not be printed by excluding them from the print area. This
exclusion capability is discussed in the “Print Area” elsewhere in this book. SumIf
Excel has many excellent tools to assist in mathematical valuations. Using inventory as an example, if the
inventory count sheets are entered into Excel worksheets the “Sum If” (“=SUMIF( )”) function can be
utilized to find the individual items and sum the total number of those parts that appears within the
worksheet. This is not counting the number of times the item or value appears within the Excel
worksheet, that function is accomplished through the “CountIf” function. The command string for this
function is =SUMIF(Reference range, selection criteria, sum range). If the selection criteria is text rather
than a number, it must be enclosed within quotation marks such as “Boxes–10”X10”X5”.”
Note: Numbers such as “123456789” should be handled as text to assure the proper results.
For example, the “SumIf” formula that would locate the number “4” within the rage of B2:B100 and
then sum the values within the range of C2:C100 would be =SUMIF(B2:B100,4,C2:C100). The “SumIf”
function to locate the text string of 123456789 within the same range would be
=SUMIF(B2:B100,“123456789”,C2:C100). In the “Sum If” data file on the data disk there is an
example of the “SumIf function counting apples, berries, and cakes. The caution with this function is
that “Apple” and “Apples” are two different text strings so data entry discipline is required.
Within the “Sum If” data file is a sample of three “SumIf” formulas contained in cells G2, G3, and
G4. These formulas look to column F to determine what they are each looking for – Apples, Berries, or
Cakes. The range they are looking at is column A, rows 2 through 250. The values these formulas are
summing are the corresponding values in column B, rows 2 through 250. Notice that there is one line
identified as “Apple.” This entry, row 26, is filled and font colored for easy locating. If you sum the
individual quantities and include this line in the grouping of “Apples”, you will find that your result and
Excel’s results do not agree. To Excel, “Apples” and “Apple” are two distinctly different items. This is
verified with other formulas on the page and subtotals for your own verification.
These formulas, as provided, all utilize “Look to” formula capabilities as shown in the text
presentation of the formulas in column H. This capability reduces typing, increases speed, and improves
accuracy. In rows 15 through 22 of the formulas columns the text values have been entered with the
required “Apples” type formatting. Excel is not case sensitive as to “Apples” or “apples” with the “Sum
If” formula.
Hint: When using “Sum If” for formula work where all of the items of a column will be placed and
counted into one or more categories make sure that all values are counted with a technique similar to that
used in cells G5 and G9. This reduces the probably of missed data. CountIf
The “Count If” function of Excel will count the number of occurrences of the specified value. In the
“Count If” worksheet within the “Count If” data file the function is shown in operation. The formula is
“=COUNTIF(range for the count, reference value).” The Count If range can be cells, single a column,
multiple columns, a single row, or multiple rows. This is shown on the “Extended Count If” worksheet of
the “Count If” workbook. Page 62 Solving Accounting Principles Problems Using Excel for Windows Subtotal
“Subtotal” is a tool that requires more show than tell to explain. What does “Subtotal” do? It tells you, for
example, all of the flight time Adam, Ann, and the rest of the pilots have flown, and what airplanes they
flew in as well, and the flight time for the aircraft and the total flight time. “Subtotal” requires that the
worksheet be sorted by the basic order for the subtotaling operation since “Subtotals” only works on the
rows format and has no sorting capability within itself. The “Subtotal” workbook on the data disk was
built for this purpose. With the “Subtotal” data file you will need to first sort the data by “Charged Pilot”,
then by “Aircraft”, then by “Date.” This will structure and sequence the data to be “subtotaled” by Excel.
Hint: If you click and highlight your data area to confine the process and avoid taking in extra rows
and columns, Excel may give you an advisory about header rows when you initiate “Subtotal.”
Usually simply acknowledge the message if you know what caused it. If you simply click on a
cell and then follow the path Data > Subtotal this advisory message will usually not appear, but
you may end up with extra rows in your data matrix.
Tip: Identify the data rows by highlighting them and accept / acknowledge the message.
Highlight the rows from row 1 through and including row 147. (This will cause an advisory message
about header rows that is not a factor in this data structure.) Then follow the path Data > Subtotals and the
“Subtotal” dialog box will pop up. Within this dialog box, set the “At Each Change In” window to
“Charged Pilot” through the drop down menu list accessed by clicking on the drop down arrow to the
right side of the window. Other options are available but the data file must be sorted to handle the
selection sequence – if you sort the data file by “Date” and then subtotal by changes in “Charged Pilot”
the resulting matrix will be useless.
In the “Use Function” window ensure that it reads “Sum” There are other mathematical operations
available and you should check these to see your options but you will be using “Sum” in this example.
In the “Add Subtotals To” window scroll up or down to see and select “Time” from the selection.
Ensure that all other items are clear of checkmarks. Then ensure that “Summary Below Data” is checked.
Since this is the first subtotal process on the worksheet, “Replace Current Subtotals” can be checked or
unchecked. This, while not
important now, will be
important shortly. Subtotal Dialog
Box Selections Before you click “OK”,
what has been done is, you
have sorted (earlier) a data
matrix into the order of
“Charged Pilot”, “Aircraft”,
and “Date.” Now you are
telling Excel to determine the
amount of hours each pilot
has flown for this data period. Click “OK” on the dialog box and watch the worksheet change.
The worksheet has new “scroll” bars (discussed soon) to the left and new rows inserted. These rows
contain the data you asked for in
the first run of the “Subtotal”
New Scroll
dialog box. Each pilot’s total flight
bars
time is now in the “Time” column.
Looking at the screen print of the
New Rows
results you can see that “Adam” Chapter 10, Page 63
flew a total of 2.0 hours and Ann flew 7.4 hours.
Now, add a data management feature to the worksheet. In the “First Subtotal” column enter a “2” in
row 2, in row 3 enter a “3”, in row four enter a “4” and then highlight these three cells and drag and fill
the cells down by grabbing the bottom right corner of the highlighted box when your cursor becomes a
heavy dark cross. You should be dragging down through the bottom of the data matrix, now row 210.
You may use this later.
The next subtotal process will address the next issue. Each aircraft has its own billing rate and
numerous pilots flew several aircraft during the period. So, select the rows from 1 through 210 – the
subtotal process increased the number of rows as it added Adam’s and Ann’s subtotals as well as
everyone else’s. Follow the path Data > Subtotal again. This time in the “At Each Change In” window
select “Change in Aircraft”, ensure that the function is still “Sum”, and the “Add Subtotals To” is “Time.”
This time, you do not want to remove the previous subtotals so ensure at there is no checkmark in the
“Replace Current Subtotals” box. If there is or if there is a faded one there, mouse click it until the box is
clear. Now click “OK” and watch the worksheet change again.
Now put a “2” in row 2 of the “Second Subtotal” column, a “3” in the third row, and a “4” in the
fourth row. Then highlight these three cells and drag and fill the column through the data matrix and
down through row 303. Excel and Subtotal has added numerous rows to the basic file. Use any of the
“Save” procedures to save the workbook.
Now the worksheet has more “scroll” bars to the left and more inserted lines. In this process, use the
scroll bars to the right and scroll through the data. You will see that, where a pilot has flown more than
one aircraft in the period, such as Deanna, Excel has subtotaled her flight time in N72RAS as 4.4 hours,
N78CHS as 1.5 hours, and N81JLS as 0.9 hours. Excel also tells you that Deanna flew a total of 6.8
hours.
Another data management issue, column J is titled “Names.” In the J1 cell, enter the formula
“=IF(D2="",J1,D2)” and drag it down through row 301 of the matrix. If you drag it into row 302 in this
example you will “credit” Zoë with the grand totals. Not harmful, just incorrect. This column will be
discussed shortly. Save the workbook to protect and preserve your work.
Now, those new “scroll” bars to the left. At the top of those bars are some new blocks with numbers
indicating levels of data that can be presented. In this example, level 4 is “Full Detail Shown”, the default
screen. Click on the level 3 box in the data file and you have a good presentation to do billing from.
Detail is gone but subtotals are clean and crisply presented so you do not need to find the subtotal within
the detail. Printing at this point will be what you see – summary down to pilot and aircraft. Make sure you
define the print area as you do not need sort columns and unnecessary data. Click on the level 2 box and
you can verify the total hours flown by each pilot, and you are not looking at the subtotals by aircraft
detail. Click on the level 1 box and you are told that your total billing for this period should be 183.7
hours. A nice cross check. Click back into the level 4 box to get full detail shown again. By clicking on
the minus signs on those new scroll bars data will disappear through their associated rows and the minus
sign will be replaced with a plus sign. The horizontal position or orientation in relation to the level boxes
at the top clue you into which data will be hidden or exposed by your clicks.
Click into the “Time” cells of any of the new rows where data appears and you will see new Excel
formulas within the cells. In cell G3 Excel and the “Subtotal” has inserted the formula
“=SUBTOTAL(9,G2:G2).” The “9” within the formula tells Excel this is a summing formula. The range
is G2 through G2. If you open Excel help and type “Subtotal Worksheet Function” you will see the
numerical drivers for the other subtotal functions – if you are interested.
This worksheet contains a lot of formulas and dynamic worksheets can lead to problems. To address
this minor issue, click into the Subtotal’s worksheet and select all cells by clicking on the “Select All”
button or the keystrokes Ctrl (Control)A. Then copy the worksheet through any of the numerous copy Page 64 Solving Accounting Principles Problems Using Excel for Windows
methods such as Ctrl (Control)C, the copy icon on the tool bar, or through the path Edit > Copy. Now
click into the “Invoicing” worksheet of the Subtotal workbook. Make cell A1 the active cell. Right click
cell A1 cell, select “Paste Special” from the options and select “Values” from the dialog box. Now the
Subtotals worksheet is pasted into the “Invoicing” worksheet without the formulas.
Select row 2 and then follow the path Window > Freeze Pane. If row 1 is already frozen through the
paste process, this is not necessary. This will “freeze” row 1 as a header row. To remove subtotal bars
from the “Invoicing” worksheet follow the path Data > Subtotal again for the popup dialog box. This
time select the “Remove All” option and click “OK.” Excel removes the subtotal features. No values are
lost since “Subtotals” will only remove formulas it placed and the new
“Scroll Bars” it inserted and you removed those formulas with the Paste
Special > Values operation just performed.
Now the reason for that “Name” and those various “Sort” columns.
Select the entire worksheet in any manner you like and follow the path Data
> Sort. Ensure “Header Rows” is identified on the dialog box. Then select
“Sort Column”, (column A of the worksheet) which represents the original
data input sequence. Select “Names” in the “Then By” window. The last
“Then By” window can be left blank. Then click “OK” and watch the
results.
You now have original data entry sequence in rows 2 through 147. In rows 148 through 303 you have
pilot subtotals by aircraft and totals flight times – without formulas. However, notice that the presentation
of data is not real good. The detail of aircraft is in column B and the hours are in column G. To solve this
issue, write the following formula in cell K148
“=IF(C148="",CONCATENATE(D148," ",G148),CONCATENATE(J148," ",C148," ",G148))”
Remember that “” is the null value while “ ” is a space. This formula will look at cell D148 to see if
there is anything there. If there is not the formula will “Concatenate” or make a text string out of the
contents of cells D148 and G148 separated by a space. These cells contain the pilot’s name with the word
“total” inserted by the subtotal operation and the total flight time. If there is anything in cell D148, this
formula will “Concatenate” or make a text string out of the contents of cells J148, C148, and G148 (in
that order) and separate them by a space. These cells contain the pilot’s name, the aircraft identification,
and the flight time for that aircraft by that pilot. Since the formula is relational, it can be dragged down
through row 301. Since it is a formula, copy column K and utilize Paste Special > Values to paste it back
in as values. Now the text strings can be moved or copied and they will retain their value. This gives nice
summary presentation for invoicing and records.
Save the data file in its current format to protect your work to this point.
Now that you have pilot invoicing detail on the pilots, you need activity by aircraft. Click into the
“Subtotals” worksheet and follow the path “Data > Subtotals to the “Subtotal” dialog box. Click on the
“Remove All” button. None of your data is lost since you copied it as “hard values” to the “Invoicing”
worksheet. Select all the cells and sort the “Subtotal” worksheet by “Aircraft” and “Then By” “Tach
Out.” This will sort the data by the aircraft and by the sequence of utilization in the event that the data
entry order was not sequential. Once again populate the “First Subtotal” column’s row two with “2”, row
three with “3”, and row four with “4” replacing any data there and then drag down the column through
row 147 (in this example) to populate the cells.
Select the entire worksheet or all the rows of the data matrix. Then utilize the “Subtotal” function
with “Change In” “Aircraft”, “Use Function” of “Sum”, and “Add Subtotal To” of “Time” and remove all
other checkmarks in this category. Then click on “OK.” Subtotal will again insert new features on the
worksheet. In the “Second Subtotal” column, once again populate row two with “2”, row three with “3”,
and row four with “4” and then drag down the column through row 154 (in this example) to populate the Chapter 10, Page 65
cells, replacing any data existing there. If there is data below the current data matrix from an earlier
process, delete or erase it. Select the entire worksheet and “Copy” the worksheet. Now select the “Aircraft
Activities” worksheet. Utilize Paste Special > Values in cell A1 to place the data and remove the dynamic
or live formulas into the worksheet. Then remove the “Subtotal” function through Data > Subtotals >
Remove All. Now, ensure all cells are selected and resort the worksheet with “Sort By” set to “First
Subtotal” and “Then By” set to “Second Subtotal.” The second “Then By” can be set to “None.”
The result will be a sequential presentation of aircraft utilization by aircraft in rows 2 through 147 and
a nice summary of aircraft utilization in rows 148 through 153. You could place the formula
“=CONCATENATE(C148," ",G148," hours")” in cell J148 (of this example) to make a nice text string
presentation and drag that formula through row 153 if desired. If you do, consider Copy > Paste Special >
Values to remove the formulas.
Save your data again to protect your efforts. You can return to the “Subtotal” worksheet and removal
all the “Subtotal” functions and resort by the “Sort Column” which will return your data matrix to input
order. No data will be lost since the aircraft activity data is now on the “Aircraft Activities” worksheet as
“hard values.” Vlookup
Vlookup is another powerful presentation tool of Excel that takes more show than tell to explain. There is
a “Vlookup” data file for this explanation. The “Vlookup” formula is located under the “Lookup and
Reference” category. In cell G5 of the “Vlookup” worksheet the formula looks like this:
=VLOOKUP($F5,$A$5:$E$6,2,FALSE)
The “decode” of this formula is that cell F2 contains the “Seeking value” that Excel and “Vlookup” is
seeking in the data matrix. The range $A$5:$E$6 defines where Excel and “Vlookup” are seeking a
match. Excel will only look DOWN the first column, the A column in this case, to find the match even
though the matrix extends to column E.
Hint: Excel does not put absolute references into the formula so these were inserted so the formula
could be dragged down the “Vlookup” worksheet. The absolute reference on the “seeking value”, the $F5
was manually absolute referenced by column so the formula could be copied right into then next two
columns.
The “2” is telling Excel to return the value found in the second column of the data matrix on the same
line if it finds a match to the “seeking value”, the value contained in cell F5 in this example. This is not
column B of the worksheet except by coincidence. Excel counts columns from the left most column of the
data matrix towards the right. This value will not change as the formula is copied or dragged from cell to
cell since it is not “relational.” The “False” tells Excel and “Vlookup” that if you do not find an exact
match to the “seeking value”, return nothing other than an “Alert” message. If this field is left blank Excel
and “Vlookup” will return the preceding value to the seeking value not to exceed the seeking value. This
can be dangerous and, as an accountant, do you want formulas returning unknown assumptions? The
“false” declaration will return a #N/A# if no exact match to the “Seeking value” is found in the data
matrix.
On the “Vlookup” worksheet there is are several data matrices so the data matrix identification
changes with the “Vlookup” formula location and purpose. In cells G5 through H14 “Vlookup” is looking
at the matrix defined by “$A$5:$E$6” which allows the part to be identified as a pen or pencil part. In
cells J5 through K14 “Vlookup” is looking at a matrix defined by “$A$9:$E$12” which defines the finish
of the pen or pencil. In cells M5 through N14 “Vlookup” is looking at a matrix defined by
“$A$15:$D$16” which informs the part reader if the part is an upper or lower assembly of the pen or
pencil. In cells P5 through Q14 “Vlookup” is looking at a matrix defined by “$A$19:$E$27” which
defines the actual pen or pencil part. Page 66 Solving Accounting Principles Problems Using Excel for Windows
When the formula is written in cell G5 as “=VLOOKUP($F5,$A$5:$E$6,2,FALSE)” it can be
dragged down the rows through 14 and maintain correct orientation and alignment. If you had not made
data matrix absolute reference, when you dragged the formula from cell G5 down to G14, the data matrix
rows would have indexed accordingly and alignment would have been lost. Now you can copy the
formula in cell G5 to cell H5. If the column reference of F had not been absolute, it would have
incremented to the right by one and that integrity would have been lost since you are not seeking a match
to the value in cell G5. Now you can change column value within the “Vlookup” formula to return from
“2” to “4” and the formula will return the value in the fourth column of the matrix when it finds a match.
Since this matrix contains merged cells – columns B and C were merged into one column, the next
column over is considered column number 4. When this change is made, the formula can be dragged from
row 5 down through row 14 and it will maintain proper alignment. Now the formula can be written into
cell J5 as “=VLOOKUP($I5,$A$9:$E$12,2,FALSE)” and dragged down and then copied from cell J5
into K5 and edited to seek the fourth column and dragged down through row 14.
Since the formulas are “live” or “dynamic”, you can change any of the values that the “Vlookup”
formula is seeking and you can see the results change almost instantly. Written into the matrix defined by
G2 through I20 is a grouping of “Vlookup” formulas looking at this feeder matrix. Replacing a number in
column F will cause the “Vlookup” formulas in columns G through I to scan the first column and first
column only of the table defined in the formula as A1 through D71 for that number. If the function finds
an exact match, as controlled by the true/false statement, Excel will return the values of the second
column of the table in column G, the value found in the third column in column H, and the value found in
the fourth column in column I.
Tip: Important issue with “Vlookup.” If you are seeking a value that appears in the data matrix more
than once “Vlookup” will complete its search on the first value found. If you are using the “True” in the
formula instead of “False”, make sure your data is sorted in ascending order since “Vlookup” will
complete its search upon finding the first value equal to or exceeding its “seeking value” and return the
(first) the exact match value or (second) the highest value not to exceed the seeking value. Even if an
exact match is lower in the data matrix.
There is an area on the “Vlookup” worksheet that shows how “Vlookup” can be used to assemble a
mailing label by entering one number in cell G37. Reading the formula will tell you where the “seeking
value” is, the data matrix, and verification that you only want exact matches.
Hint: “Vlookup” is structured for data in a vertical format. If your data is in a horizontal format,
utilize “Hlookup” which works in the same manner for data in a horizontal format. Chapter 11 DISPLAY
Chapter Outline
Freeze Panes
Split Panes
Conditional Formatting
Displaying Zero Values
Gridlines On The Screen Hiding Columns And Rows
Hidden
Indent Within A Cell
Truncate
Workspaces Freeze Panes
Freeze panes is actually “freeze rows or columns.” What this process does is keeps a specified set of
rows, columns or both rows and columns available and viewable at the top for rows, at the left for
columns, or at the top left for rows and columns while you move throughout the worksheet. Open the
Freeze Panes data file. In this workbook there are three tabs. On the “Rows” tab, the rows 1 through 3
were “frozen” in view by clicking onto the row 4 identifier then following the path Window > Freeze
Panes. At this point Excel will “freeze” all of the rows above the line highlighted since there were no
columns selected. As you move throughout the workbook you will notice that the first three rows are
always in visible. This is excellent for titles.
On the “Columns” tab, the “B” column was highlighted by clicking on the column identifier. Then
the “Freeze panes” was invoked through the path Window > Freeze panes. Since there were no rows
identified the column or columns left of the highlighted column will remain visible as you move
throughout the worksheet.
On the Rows and Columns tab the cell B5 clicked into and made active. Then the freeze pane was
invoked through the path Window > Freeze panes. Since a cell with both column and row identity was
active at the time the process was invoked, the rows above row 5 and the columns left of the B column are
now “frozen” into place. As you move left, column A will always remain in view. As you move down,
rows 1 through 4 will always remain in view. This provides you with column and row identity. This
capability is excellent for large worksheets where columns and rows are both elements of identity such as
multiple salesmen in rows and multiple sales items in the columns. By freezing the columns, the
salesman’s identity will always be visible, by freezing the rows, the title of the sold items will always be
visible.
While Freeze Pane is invoked on a worksheet CtrlHome will normally take you just below the frozen
rows and just to the right of the frozen columns as the frozen columns and rows are considered title areas
not normally used for data. You can still enter these areas by simply mouse clicking into them. Section 2, Page 67 Page 68 Solving Accounting Principles Problems Using Excel for Windows Split Pane
The “Split Pane” function is invoked on the Demo File upon opening. This function splits the single
worksheet into two or four separately displayed panes. This view is controlled through the path Window
> Split and, if invoked, Windows > Remove Split. To invoke a horizontally split screen presentation,
place your cursor on the row numbers at the left of the display pane and click into it to make it active.
Excel will highlight the entire row. Then follow the path Window > Split from the menu bar. Excel will
split the screen horizontally and provide a scroll bar for each pane. In this manner the data from the
exercise or problem can be read in one pane while accomplishing tasks in the other pane. To demonstrate
a further enhancement of this function, set your upper pane to show rows 9 through 15 of the demo file
while showing rows 39 through 42 in the lower pane. You can recreate the journal entry by clicking into
cell B39 of the lower pane and typing the “=” (Equal sign) (without the quotation marks), then click your
cursor onto cell B9 of the upper pane. When you press and release the “Enter” key, “Tab”, or click into
another cell, the date of “Jan 2” appears in the date cell of the journal entry, saving you time and reducing
the risk of data error through typing. Since the account to be debited is Cash, you can do one of several
things, you can type “Cash” into the debit account title area or you can do another “Look to” formula for
the account title in the general ledger. Examination of the general ledger will show that the “Cash”
account title is contained in cell B77 while the cash account number, used when posting, is in cell J77. So
try the formulas “=B77” and “=J77” in the appropriate cells of the journal entry to preclude typing in
those values. You can use the “=” sign and then simply click on the cell B77 or J77 to complete the
formula if you desire.
This capability of split screens is handy and precludes excessive scrolling and losing your place while
working within the templates. If you place your cursor near to the center of the display and make a cell
the active cell and then invoke split screen, you will end up with four panes to deal with. In this case, the
control of the display areas is interactive between the left & right and upper & lower panes through the
scroll bars. Becoming comfortable with the appearance and proficient with the use of split screens greatly
reduces your time to accomplish the tasks and reduces scrolling.
Once split screens are invoked, you can drag the border identifiers around to change their position and
sizes. Conditional Formatting
Excel will accept several levels of conditional formatting for a cell under the path of Format >
Conditional Format. On the Conditional Formatting data file there is an example in cell A1. If the value is
less then 1 the cell appears normal, if the cell contains a value between one and ten, the formatting is
horrible, if the value is greater than 10 the formatting gets worse. While this was done for effect, if you
were inputting markups for retail and wanted no percentage less than 50% nor more than 150%, you
could format the cell to go red if the percentage is not within this range as cell A3 is formatted to.
By examining the dropdown menu selection choices of the operator, defaulted to “between” you will
see that you have a wide range of operators to work with. You can impose up to three levels of
conditional formatting on a cell by clicking on the Add button at the bottom of the dialog box. If you have
imposed conditional formatting on a cell you can remove one or more levels of formatting by following
the path Format > Conditional Format while the target cell is selected and clicking delete on which level
of formatting you want removed. Displaying Zero Values
The “Zero values” option on the “View” tab controls whether or not a zero (0) will be displayed whether
the zero is entered as a “hard value”, one entered directly by keying in “0” (the numeral zero), or it is a
soft value” of “0” (the numeral zero) as a result of a formula action. If selected, checkmark in the box,
zeros will be shown, if not selected (no checkmark in the box), zeros will not be shown. An interesting,
and sometimes, confusing event occurs when “Zero values” are not selected for display. The user will see Chapter 11, Page 69
a “0” (the numeral zero) when he expected to eliminate these values from the presentation through his
“Options” setting. This happens when the actual value in the cell is below the level of presentation but not
equal to zero. If data entry or a formula calculation results in a value below the displayed decimal places
Excel will display the “0” or “0.00”(the numeral zero) value – not because it is zero but because it is
smaller than displayable. If the decimal setting is set to 2 significant digits such as 0.00, the value of 0.00
would not result in a presentation if the “Zero values” option is deselected. However, if the value in the
cell were 0.001, the presentation of Excel would be “0” or “0.00” since this is a value not equal to zero
and it is below the displayed value setting. If Excel is set to two decimal places and the actual value is
0.005, then Excel will show 0.01 as it rounds up the value for presentation. However, it will utilize the
real value of 0.005 in any calculations. Gridlines On The Screen
The gridlines seen on the computer presentation screen are controlled through the “View” tab. These
gridlines are the vertical and horizontal lines on the screen that create the cells. These gridlines are those
seen only on the screen presentation of Excel and greatly aid you in maintaining a positional reference
within the rows and columns of Excel, not those on the printout. Gridlines on the printout are controlled
through “Page Setup” dialog box. Below the selection for gridlines is the ability to change the gridline
colors through the “Gridlines colors” dropdown window. This selection will usually remain selected
except when using Excel for a screen presentation. Hiding Columns And Rows
Excel will allow you to hide a column, columns, a row or rows. The
methodology is to highlight the column, columns, row, or rows you
want hidden. (For this operation, these areas must be adjacent and
continuous.) Then follow the path Edit > Hide from the tool bar or
right click the highlighted area and select “Hide” from the popup
menu. Once the cell or cells are hidden you will see that the column
identifiers and row numbers do not appear to follow the A, B, C, 1, 2,
3 sequence. As shown on the “Hiding Columns And Rows” data file you can see that columns B, D, and
F as well as rows such as 5 and 6 are hidden. If you examine the borderlines between the column
identifiers and the row identifiers you will see a slight difference in their presentation if there are hidden
columns or rows there. These cells remain active in all formula and will still be active for functions like
Go to, Find, and Find and Replace. This technique is excellent for extended calculations that are not
desired in the presentation. However, copy and paste commands are active on these cells and caution
needs to be exercised when a cell or cell is hidden.
To unhide a column(s) or row(s) you have several options. Highlight the predecessor through
successor columns or rows that border the hidden columns or rows through a sweep operation and follow
the path Edit > Unhide from the menu bar or right click the highlighted area and select “Unhide” from the
popup menu. There is also “Hide” and “Unhide” options through Format > Column or Format > Row
from the menu bar.
In the “Hiding Columns And Rows” data file there are numerous areas for you to hide and unhide. Hidden
The “Hidden” capability within Excel is controlled through the “Protection” tab of the “Format Cells”
dialog box. The path to this tab is right click on a cell and select “Format Cells” from the popup menu
then the “Protection” tab on the “Format Cells” dialog box or follow the path Format > Cells to the
“Format Cells” dialog box and select the “Protection” tab. The “Protection” data file can be utilized for
this section. Page 70 Solving Accounting Principles Problems Using Excel for Windows
Assume you are giving a presentation and using Excel as a presentation tool. The worksheet is
extensive and has numerous large formulas contained within it. The object of the presentation is to
convey the information, not have the audience to stare in awe of your formula construction while you are
trying to convey your message. So you can make the formula bar and formula input window disappear by
following the path Tools > Options > View and remove the checkmark from “Formula Bar.” You can
right click the menu bars and uncheck all of the unwanted and unneeded menu and tool bars and make
them disappear. You can format your cells to “Hidden” by highlighting an area of cells or the entire
worksheet and right clicking to get the popup menu and select “Format Cells” or follow the path Format
> Cells and then format the cells to “Hidden” by insuring a nonfaded checkmark is in the “Hidden” box.
Now invoking worksheet protection through Tools > Protection > Protect Sheet will minimize the
appearance of formulas as you click on the worksheet. The results of the formula are still shown.
By formatting any unnecessary for presentation intermediate formulas and unwanted text and cells to
a text coloring the same as the background or fill coloring before protecting the worksheet these
unnecessary items will cause less distraction as they will be hidden  white ink on white paper type
printing. To remove this later simply select the entire worksheet and format all text coloring to
“Automatic” or whatever color you choose through the “Format Cells” dialog box.
Tip: You can also use “Full Screen” through the path View > Full Screen. There is an icon to return
to “Normal” view. If you close that icon off your screen for viewing purposes, you still have the main
menu bar at the top of the screen and can follow the path View > Full Screen, a toggle switch, to get back
to your normal default view.
Clue: The Excel scroll bars will tell you the general data area of a worksheet. If you open a worksheet
and the scroll bars fill the slide area, you are generally looking at the total data entry area. If the scroll
bars are little bars in a large slide area, the data area is rather large. Excel remembers where the data is
and allows you to use the scroll bars to get to it. And, even a “ ” (space) inserted 250 rows below the last
data row and 50 columns to the right of the last data column will cause the scroll bars to change and
represent this placement. Indent Within A Cell
To indent within a cell
without using spaces use
the Indent icon on the tool
Increase and Decrease Indent
bar. Each click on the
icon moves the start of
text and numerical data
about
2
spaces.
Remember that by default text is left justified and numerical data is right justified. Asking Excel to indent
numerical data to the right in a default formatted cell may result in an interesting move. The Indent
Within A Cell data file is available for you to check the operation of the Increase and Decrease Indent
buttons. Column A is default justified left because it contains text. Column B is specifically formatted as
right justified even though it contains text. Column D is specifically left justified even though it contains
numerical data while column E is default right justified as numeral data. Click into various cells in these
columns and click on various combinations of the Increase and Decrease Indent buttons and watch the
Excel response.
Hint: Excel will indent a cell that contains a formula looking to another cell or computing a value.
The exercise and problem templates provided on the data disk are set up to have the “Look to”
formula or function such as “=F12” used extensively to save keystrokes and reduce errors. However, how
do you handle journal entries with a “Look to” reference? On the “Indent Within A Cell” data file in rows
2 through 5 of column H is a mini chart of accounts. The task is to purchase $800 in supplies for cash and Chapter 11, Page 71
make the journal entry in cells H8 and H9 with the values in columns M and N. Utilizing the “Look to”
formula of =H4 in cell H8 you get the presentation of “Supplies” as the debit. In cell H9 you enter =H2
for Cash but it is flush with the left side of the cell while as a credit account it should be indented. Click
on the Increase Indent button and it is. Now enter the value of $800 in cell M8 and have N8 look to cell
M8 to ensure that the cells are always the same.
Now, starting at row 14, purchase $1,000 in supplies by paying cash of $250 and the balance on
account. Utilize Increase Indent to get the proper presentation of credit entries and formulas to have the
cash paid subtracted from the total purchase for the value on account.
Many of the cells within the exercises and problems supplied on the data disk have indent functions
imposed on them in their construction for proper presentation. Truncate
Truncate reduces the number of significant digits from one calculation to another. Within the
“Truncate” data file on the data disk, is a math computation that was accomplished to determine the flight
time for each flight by each pilot. This formula took a value such as (Tach In – column E) tachometer
(engine) hours of 553.0 as recorded at the completion of the flight and subtracted the (Tach Out – column
D) beginning tachometer (engine) hours of 551.4 to determine that Calvin should be charged with 1.6
hours for this flight as shown in cell F2. This same formula is also in G2. The formulas in column G was
then copied and with Paste Special > Values turned into hard numbers. When those hard numbers are
looked at, the values don’t make sense. The formula values are 551.4 hours subtracted from 553.0 hours
and the results should be 1.6 hours but the answer as shown in Excel to fourteen significant digits is
“1.60000000000002”. To avoid passing values that appear this way you can use the “Truncate” formula
as written in cell I2, “=TRUNC(G2,1).” This formula will take the value in cell G2 and reduce it to one
significant digit as instructed by the “1” following the cell reference and comma, trimming those extra
zeros and that inappropriate “2” from the number. However, this is once again, a formula so it needs to be
copied and pasted through Paste Special > Values to eliminate this presentation.
To save the steps, you can use an embedded formula such as shown in column J, “Calc Time 2” to do
the job in one process. The embedded formula is “=TRUNC(E2D2,1)” which truncates the results of
subtracting the out time from the in time and reducing or trimming the significant digits to one in a single
step. You now need to copy and “Paste Special > Values” only column J to complete the process of
eliminating formulas for your worksheet.
The balance of the worksheet in columns I, J, and K are available for your practice.
Note: The use of “Precision as displayed” through the path Tools . Options > Calculations addressed
part of this problem but removes nondisplayed values and detail from the workbook. Workspaces
A workspace within Excel is a structure of one or more workbooks within a preformatted presentation.
The example of a workspace, shown below, is the “Workspace Example.xlw” that accompanies this text.
Notice that the workspace extension is “xlw,” this extension is viewable in the default opening dialog box
of Excel. By opening this single “structure” you actually open all five workbooks – Description.xls, Unit
of Measure.xls, Department.xls, Agent.xls, and Workspace example formula.xls contained within the
workspace in a single action. Since these files are closely related, this is very convenient. The workspace
example formula contains a series of Vlookup formulas, covered in detail in the vlookup section, that
operate in relation to the “A” column of the Workspace example formula workbook. This series of
formulas looks at the other four workbooks to get information. Since they are all related, seeing them at
one time is handy. And by opening the workspace – a special structure within Excel, you have access to
all five workbooks. This workspace looks like this: Page 72 Solving Accounting Principles Problems Using Excel for Windows A workspace is created by sizing and positioning all of the applicable workbooks and worksheets in
the manner you desire. In this presentation, the Description workbook was reduced in size and positioned
in the upper left corner of the presentation by dragging it there with the mouse and changing its size by
dragging its borders. Then the Units of Measure workbook was reduced in size to match the height of the
Description workbook, a similar width, and then positioned next to the Description workbook through a
dragging operation. After that the Department and Agent workbooks were reduced and dragged into place
in a similar manner. Lastly, the Workspace example formula workbook was sized to fill in the rest of the
space on the presentation window. Once all were positioned, the path File > Save Workspace was
followed. The dialog box is very similar to the Save and Save As dialog boxes where you can change the
save location and provide a name for the workspace. Under “Save as type” the only option is “xlw” since
this is a special purpose extension for Excel.
Once saved, the five individual files can still be opened individually through the normal opening
procedures of Excel and can be updated or modified as appropriate and then saved. Their presence within
a workspace does not preclude normal access and operation of the workbook. However, when all
workbooks – i.e. the workspace is required, following the normal opening paths such as File > Open, the
key strokes CtrlO, or clicking on the Open File icon on the tool bar will present you with the Open dialog
box and the workspace name will be presented there. Doubleclicking on the workspace name will open
the workspace and all the contained files. While working in the workspace, you can maximize or
minimize individual workbooks by clicking on the maximize or minimize buttons of that workbook.
The active workbook is controlled by you clicking into the workbook you desire. If you desire to
make an entry into the Description workbook, click anywhere on the Description workbook and the
banner or title bar will become bright while the others will be faded. This indicates that the Description
workbook is the active workbook. You can now click into the worksheet and maneuver around in the
normal manner of movement. As stated, if the size is too small or it is simply easier to work with a larger Chapter 11, Page 73
workbook, use the maximize button in the upper right corner of that workbook to increase its size. while
the workbook is active within the workspace, all of the menu and taskbar commands are applicable only
to that workbook allowing you to save or format the workbook as required or appropriate.
It is important to remember that a workspace is really a structure that contains other workbooks.
There are not restrictions placed on the individual workbooks because they are part of a workspace. Upon
closing a workspace Excel will normally ask you if you want to save any of the contained workbooks that
you have changed since the last save or opening action. Chapter 12 ANSWERS
Chapter Outline
Formula Auditing
Formatting Cells
Merge Cells
Formatting Within A Cell
Goal Seek Null Value
Or
And
If Statement Formula Auditing
Excel has a very useful aid for assisting you with
the checks of your formulas and logic. The
“Formula Auditing” function is controlled
through the “Formula Auditing” tool bar or
menu. To display this tool bar, right click on the
menu bars at the top of the Excel screen, from the
popup menu select the “Formula Auditing”
option from the lists. When this tool bar appears
place and hold your cursor over the various icons
and Windows will display the function of the
icon. The icons of primary interest are “Trace
Precedents” and “Trace Dependent.” Click into
cell C3 of the “Formula Auditing” data file on
the data disk to make cell C3 the active cell.
Then click on the icon for “Trace Precedents” and Excel will display arrows from all of the cells that feed
this cell data. This is shown on the screen print. If the source cell is a formula, Excel will not “trace
through” to that cell’s source. To be effective, the “Trace Precedents” must be activated from a cell with a
formula that references at least one other cell. To remove the audit arrows you can click the eraser on the
tool bar and all audit arrows will disappear, regardless of where your active cell is. To make only one
cell’s audit arrows disappear, click into that cell and click the icon with the negative signs for the action –
dependent or precedent.
Click into cell A1. While cell A1 is the active cell, click on the “Trace Dependent” icon on the
“Formula Auditing” tool bar. A single arrow will appear from cell A1 to cell C3 showing that the data
flow is from cell A1 to cell C3. To see that cell A3 is also a source cell for cell C3 you can either click
into cell A3 and click onto the “Trace Dependent” icon or click into cell C3 and click on the “Trace
Precedents” icon. Once again, the trace icons with negative signs make individual arrows go away, the
eraser on the tool bar makes them all go way. Try this tool on some of the workbooks and worksheets
provided on the data disk if the logic or flow of data is confusing.
You can also access the “Formula Auditing” tools through the path Tools > Formula Auditing from
the main menu bar. With this path you can select a single event or elect to display the tool bar.
Section 2, Page 74 Chapter 12, Page 75 Formatting Cells
Excel has several ways to format cells and the data within a cell. To format a single cell, click into the cell
to make it the active cell. If the format is intended for a single range of cells, click into the upper left,
upper right, bottom left, or bottom right cell and sweep the range with the left mouse button down,
highlighting and selecting all of the cells in the range. Formatting can be applied to more than one cell or
one range by holding the Ctrl (Control) key down while selecting the sequential cells or ranges.
Formatting can also be applied to a single column by clicking on the column identifier, a single row by
clicking on the row number, or on multiple columns or rows by clicking on the column or row and then
sweeping to right, left, up, or down to highlight all the columns or rows or holding the Ctrl (Control) key
down while selecting non adjacent columns.
Once the cell or cells are selected, right click into the highlighted cell or cells and Excel will present
you with the “Format Cells” popup dialog box with the formatting options available at the time. That
dialog box appears below: Format used throughout most of the Excel
templates:
Number > Currency
Decimals Ø (zero) or 2 as appropriate.
$ English (United States) or None as appropriate
Negative numbers as red and contained within
parentheses. From this dialog box you can select any of the many formatting features available at the moment. If a
feature is grayed out or faded, that feature is not available at the moment. For the Excel templates, the
formatting is generally Currency with either Ø (zero) or 2 (two) decimal places as appropriate.
Occasionally 4 or 5 significant digits is set as required by the exercise or problem. Dollar signs, controlled
through the “Symbol” window, are only shown when needed.
In selecting numerical formatting Excel positions the right hand number appropriate for the
formatting selection as to how negative values are presented. Provided below is a display of numerous
Excel formats in positive and negative numbers. A close examination of this screen print will reveal that
not all of the numbers are aligned in the same vertical plane at the right side of the column. This occurs
when Excel is formatted to display negative numbers with a preceding “” negative sign in some cells
while other cells display negative numbers contained within parenthesis “( )”, Excel will position the
numbers farther to the right if a preceding negative sign is used since the space is not needed to the right
in the event of the closing parentheses. The solution for a ragged display of numbers that do not right
align correctly is to check that the formatting of the numbers is all the same by selecting and highlighting Page 76 Solving Accounting Principles Problems Using Excel for Windows
the cells and rightclicking them followed by selecting the desired format even if it appears correct. Excel
will occasionally only show the formatting of the upper left hand cell of a range or multiple selection. The “Format Cell” dialog box also contains the “Alignment” tab. From this tab, you can align the
contents of the cell in numerous horizontal and vertical formats as well as change its orientation and stack
it. Through the “Orientation” formatting function you can rotate your data
through various angles. The insert, shown here, contains an orientation
sample. Another mode shown here is referred to as “Hotel signage”,
referring to the letters HOTEL will be printed vertically on the sign in
front of the establishment. This is also shown in the insert. Text wrap will
try to keep the preset column width while creating a second, third, and
fourth row presentation within the cell of long data.
Tip: You can force line breaks in a cell, as done here, by the keystrokes “AltEnter” within a cell
rather than of simply Enter or a space. In the insert, “AltEnter” produces the same presentation as text
wrap except that you control the break points of “AltEnter” whereas text wrap makes its own decisions.
The functions of “Font” are as expected in any Windows standard application. You can change style,
size, and color through this tab. It is on this tab that you will find “Superscript” and “Subscript” functions
as well as “strike through.” You can also underline the text contained within a cell from this tab including
single and double lines for accounting. However, if underlining is selected, it is only effective on cells
with content in them, including spaces, and is only applicable to the cell. That is, if your entry covers two,
three, or four columns, only the column or columns selected will be underlined and then, only if data
actually appears within them. You can also highlight specific text within the Excel formula window and
underline or format only that text such as shown here: (Addressed in length later.) The “Borders” tab allows you to place borders and intermediary lines to the highlighted cell or cells.
You can put a thick line on the outer edges by selecting the heavier line first and then clicking the
“Outline” icon on the tab. You can then select a thinner line and place lines within the range. Excel will
allow you to place vertical, horizontal, and diagonal lines through a range. The functions available
through this tab are directly associated with the cell or range selected at the moment. Excel will not allow
you to place vertical or horizontal intermediary lines within a single cell through this method, you can
draw a line through the cell as explained in the “Drawing” section of the
book.
Under the “Patterns” tab of the “Format Cells” dialog box you will
find the colors to fill a cell and the available patterns for texture
presentation of those colors. These functions were used on the templates Fill color icon
Font color icon Chapter 12, Page 77
to define the work area, outlined in gray, and the student focus areas, filled in light yellow. To remove a
color, simply select the cell or range of cells, right click the selected cell or range, select format cells,
select “Pattern” and then select “No color.” This will remove both color and texture or pattern. Limited
colors can be added to a cell or range of cells through the “Fill” button or icon on the tool bar. This is the
“Paint bucket” as shown here. All colors can be removed through this button. Fonts can be colored, to
limited number of colors, with the “Font color” button or icon. Next to both the “Fill” and “Font color”
icons is a dropdown arrow to allow color selection. If the bar at the bottom of the icon is the desired color,
simply clicking the icon will place that color in the active cell or cells.
The last tab is the “Protection” tab. This tab allows you to invoke a minor level of protection on the
worksheet or workbook. If you did not want another party to have the privilege of modifying your
worksheet you can select the entire sheet by using the keystrokes CtrlA or by clicking on the cell above
the row 1 number and to the left of the column A letter, then right click the worksheet and select “Format
Cells.” After selecting the “Protection” tab, select the “Locked” option. Ensure that the appearing
checkmark is clear and distinct. A faded checkmark indicates that the selected area contains more than
one level of protection – some protected, some not protected. Also on this tab is the “Hidden” option. If
“Hidden” is selected and activated any functions or formulas will be hidden from the viewer – that is,
even if the cell contains a formula, the viewer will not see it in the formula window. Neither of these
options are active until empowered by following the path Tools > Protection. From this point you can
select “Protect Workbook” or “Protect Worksheet” as well as a couple of other options. When “Protect
Worksheet” or “Protect Workbook” is selected you will be presented with a popup dialog box asking
what privileges you want to grant the users and asking if a password will be utilized. You can leave the
password blank if you desire and it will still be protected or hidden. If a password is provided and later
forgotten, you may not be able to recover the workbook or worksheet. As a matter of technique, if
distributing a password protected workbook or worksheet, you may want to preserve an unprotected copy
in a secure location. None of these protection levels is sophisticated and they can be broken by an
individual with intent, therefore, these levels of protection should not be relied upon to protect sensitive
data. Merge Cells
The “Merge Cells” command is also on the “Alignment” tab. This function will create one cell that
occupies two or more rows or two or more columns or, as one group, multiple rows and columns. This
function is used in many of the templates to present instructions and text data in one, large, cell. For
example, the “Name” area is one cell while occupying the range D2 through F2. To utilize the “Merge
cells” function, click into the cell that you want to be the upper left most location of the merged cell.
Then, with the left mouse button held down, sweep right, down, or down and right highlighting and
selecting the cells you want to be replaced by the new, larger, merged cell. When the area is highlighted,
right click the highlighted area, select “Format Cells”, select the “Alignment” tab, and select “Merge
Cells.” The highlighted cells now become one single cell when the “OK” is clicked. Invoking the merge
cell function will result the loss of all data but the data in the upper left most cell of the selection. Excel
will usually advise you that this is going to happen but it is poor data management to rely on Excel’s
protective features for this. If this function is invoked, it can be revoked by selecting the affected cell and
then removing the check mark from the “Merge Cell” option. Selecting “Wrap Text” and / or “Shrink To
Fit” are text functions for the merged cells. The “Wrap Text”, discussed elsewhere in this book, it will
allow multiple lines of text for a single line of text entry. The “Shrink To Fit” selection will automatically
change the size or “points” of font to present all of the text into the cell. Page 78 Solving Accounting Principles Problems Using Excel for Windows Formatting Within A Cell
You can also invoke many of the formatting capabilities for
cells onto the specific elements contained within a cell. For
example, you want to enter into an Excel cell that the interest on
the note payable is due on the 30th of each month. When the
text is entered as “Interest on note payable is due on the 30th of
each month” the text is not automatically superscripted. For this
presentation, select the cell containing the text, then, inside the
formula window, highlight with the mouse the two characters
“th” within the text, then right click the highlighted selection
and select “Format Cells”, then select “Superscript” on the
“Font” tab. Notice that not all of the tabs that appeared when
“Format Cells” was selected while entire cells were selected are
visible now that you have only a portion of the text selected.
Since the additional capabilities are not available, they were not
presented, even grayed out or faded. Many of the formatting
features can still be invoke upon the text within a cell. Formatting Within A Cell Goal Seek
Goal Seek is a tools within Excel that will provide you the question if
you know the answer. With Goal Seek, click into a cell containing a
formula then follow the path Tools > Goal Seek and respond to the
dialog box. The worksheet in the screen print is in the Goal Seek data
file. The path is Tools > Goal Seek. The popup dialog box requires that
you identify a cell with a formula in it, for the example that cell is C4.
Then enter the value you want the formula to end up at, 50 as shown in
the screen print. Then tell Goal Seek what cell you want the value inserted into to create the answer of 50.
The cell identified as “By Changing Cell” must be referenced in the formula contained in cell C4. For the
example cell C2 was chosen. When OK is clicked with the mouse or the enter key is struck, Excel will
produce a popup text box stating that it found a solution to get the value of 20 and that that value is 47
and it asks if this answer is acceptable. Since it is, click on the OK and Excel will modify the value in cell
C2 to 47 and close the dialog box. If the answer is unacceptable hit Cancel and the value in cell C2 will
be left at 1. If Excel cannot compute the value it will error or fault out and tell you it cannot find a
solution. This is usually because cell identified in the “By Changing Cell” window is not referenced
within the formula contained within the “Set Cell” cell. Null Value
The “Null Value” is a special term – it is not a space as imposed by pressing and releasing the space bar,
it is not a letter or character such as a, b, or c, it is not a number such as 1, 2, or 3, nor is it a punctuation
mark or symbol such as !, ?, @, or #. It is the double quote symbol followed immediately by the double
quote symbol such as “” without anything separating them. The best way to define the “Null Value” is it
is nothing and it is not anything else. It is used frequently within formulas to return a “clean and clear”
presentation. On the “Null Value” data file the use of the Null value is demonstrated. The worksheet is
defaulted to show “0” values through the path Tools > Options > View and then ensuring that there is a
full color (not faded or blank) checkmark is within the “Zeros Values” box near the bottom of the screen.
This is also the default setting for most of the exercise and problem templates. This option or selection
will provide you with the presentation of: Chapter 12, Page 79
Sales:
Less: Cost of goods sold:
Gross profit:
Vice:
Sales:
Less: Cost of goods sold:
Gross profit: $100
$100
$0
$100
$100 In the first example it is clear that the value being presented is “0”, it the second example it is not
clear if the formula has even been entered.
The null value is frequently used in if statements as a response. In the “Null Value” data file, the
worksheet determines and presents appropriate periods for depreciation. In cell B4 you enter the life of
the asset in years. In cell B5 you enter the periodicity of depreciation – annual, semiannual, quarterly, or
monthly. The formulas in columns B and E starting on row 12 to determine if the period is applicable and
enter a period number or return the null value, as instructed through cells B4 and B5. Based on the
availability of depreciation periods, the concatenate formula in columns C returns a text string or the null
value while column F returns choice of text strings. As you scroll down the columns to see the difference,
where the periods are not available in the null value column, the columns are blank and appear to be
empty. In the “zero value” column, the response of nonavailable periods is “0”, an unnecessary and
cluttered display and an unnecessary text string.
Assume that the “ ” (space) is utilized instead of the null value or a 0. Almost the same thing will
happen. The appearance will be clean and crisp as shown in columns H and I. However, now Copy >
Paste Special > Values all three examples and you will find the difference. Inside the rows that are not
relevant to the “Null Value” columns, there is nothing – the value the if statements were returning was “”
– Null – nothing. Inside the rows of the Zero Value columns there are a number of zeros since the if
statements were returning zeros. Inside the space columns are a lot of cells with a bunch of spaces in
them. Each unnecessary zero and space adding size to your file.
Tip: These issues affect default page size in printing. Or
The “Or” function of Excel will return a “True” response if any one or more of up to thirty conditions
specified are true. This is shown on the “Or” workbook on the data disk. This data file is a copy of the
“And” workbook with the “Or” function rather than the “And” function. Some of the results are the same,
some are different. For example, now that the formula in cell D1 is saying if any one or more of the cells
A1 through A5 are greater than “2”, return “True.” And, since cells A3 through A5 are all greater than
“2”, the “Or” statement is returning the “True” value.
However, cell G14 now has a minor problem in its resulting logic. Since any one of the conditions is
true, that is, the number of apples equals or exceeds the minimum inventory level, the statement is
returning a “True” to the “If” statement and the “Concatenate” function is telling you to go skiing when in
reality you must order berries. And
The “And” function is found under the Logical” category of Excel’s formulas and functions. It can
compare up to 30 values against your specified requirements and return a “True”, “False”, numerical, or
text string response depending on your requirements. In the “And” data file on the data disk you will find
several examples of this formula in operation. In cell D1 the “And” function is being instructed to check
cells A1 through A5 to ensure that they are all greater than 2. Since only cells A4 and A5 are greater than Page 80 Solving Accounting Principles Problems Using Excel for Windows
2 (cell A3 is equal to 2), the “And” function returns a “False.” In cell D2 the “And” function is being
asked if A1 is equal to 1, if A2 is equal to 2, if A3 is equal to 3, if A4 is equal to 4, and if A5 is equal to 5.
Since they are, as supplied on the data disk, the function returns a “True.” Since the “And” function by
itself can only return a “True” or a “False” in cell D3 the “And” function is embedded within an “If”
statement to return a text string – if all of the cells A1 through A5 are greater than 3, return a “Good”, if
they are not all greater than 3, return a “Bad.” The “And” function is providing the “If” statement a
“True” or “False” determination. And, since the “If” statement is limited to seven layers of parenthesis
while the “And” statement can evaluate thirty values, you have greater latitude in your evaluations.
In cells G10 through G12 the “And” function is being utilized with the “If” statement and the
“Concatenate” function to make a determination and return a clean cell or a text string and a value. (This
is being done for demonstration purposes as an “If” statement and “Concatenate” function can accomplish
this as shown in cells G20 through G22.) The “And” function determines if it is a true statement that
counted inventory is equal to or greater than the minimum level located by the “SumIf” formula. If is, the
return is the “Null value”, if not, it utilizes the “Concatenate” function to state what has to be ordered and
the amount that must be ordered.
The “And” function does not have to see if all numbers or values are greater than a specific value or
less than a specific value. You can mix them up as needed. Inside cell D5 the following formula is placed:
“=AND(A1=1,A2<>3,A3>=3,A4<=4,A5<>A1).” This formula asks if cell A1 is equal to 1, if cell A2 is
not equal to 3, if cell A3 is equal to or greater than 3, if cell A4 is less than or equal to 4, if cell A5 is not
equal to cell A1. If ALL terms are true, the “And” function will return a “True”, if any one or more are
false, the “And” function will return a “False” response. The “And” function will also compare text
strings as required.
Tip: Make sure the question you are asking will provide the answer you need. If Statement
As shown throughout this text and as utilized extensively throughout the exercise and problem templates,
the “If” statement is a very powerful and easy to use logic tool. The “If” statement works on the principle
of comparing term one with term two and if the terms make the condition true, it replies with the first
field after the comparison, if the comparison is false, it replies with the second. The first and/or second
field can be another “If” statement value, formula, or text. The formula format is “=IF(2=2,4,0).” In this
simple “If” statement if 2 equals 2 the formula will put 4 in the cell, if 2 does not equal 2 the formula will
put 0 in the cell. Since 2 does equal 2 the response will be 4. This is shown in cell A1 of the “If
Statement” data file. However, the statement “=IF(2=Two,4,0)” will result in an error presentation since
text strings such as “Two” inside formulas must be contained within quotation marks such as
“=IF(2=“Two”,4,0).” This is shown in cell A2 of the “If Statement” data file. If the statement is entered
into Excel as “=IF(2=“Two”,4,0)”, as it is on the “If Statement” data file in cell A3, the response will be 0
as the number “2” is not equal to a text string of the letters “T”, “w”, and “o”. As stated earlier, Excel
does not read English, but it can compare text strings through spell check, F7, which is in reality, a big
“If” statement – if the text string being evaluated matches text strings found in the reference file, look at
the next text string, if the text string is not in the reference file, bring up the dialog box and ask for help.
Excel will compare text strings and provide responses as instructed. For example, the “If” statement
“=IF(“Two”=“Two”,4,0)” will return the value 4, as shown in cell A4, not because it is the sum of Two +
Two, but because the two text strings are equal, as asked, so the first field or the “True” term following a
true statement, is returned.
Tip: There is a detail of Excel that needs to be appreciated in all of the comparative statements. If the
value of 123.451 is entered into a cell with formatting to display two decimal places the entered value will
display as 123.45 but Excel will remember and respond as if it is 123.451. If the value 123.451 displayed
as 123.45 is compared to a displayed value of 123.45 which is in a cell formatted to display two decimal Chapter 12, Page 81
places but that value is actually 123.452, the values appear to be equal, as presented in the formatted cells,
but they are not. Examine the data in the range of E1 through F2 on the “If Statement” data file.
When Excel compares values and the formatting of numerical information is different, for example
“Accounting” with 2 decimals and “Currency” or “General” to 4 decimal places, Excel will ignore the
formatting and compare the values. However, it is possible to have what appears to be a numerical value
such as the 25 contained in cell J1 not be equal to the 25 in J2 since cell J1 as formatted to text before the
25 was entered and, since cell J2 is formatted to General by default, that 25 is numerical and text 25s are
not equal to numerical 25s.
Hint: Try to think of questions that are answered True/False or Yes/No with “If” statements.
The following is a table containing most of the comparison operators available to the “If” statement:
= (equal sign) if the first comparative term is equal to the second comparative term, the first field or
the true field will be returned, if the comparison is false, the second field or the false field will be
returned. Your question is “Is it true that the first comparative term is equal to the second comparative
term?”
< > (not equal to sign) (The order of the operators is important!) If the first comparative term is NOT
equal to the second comparative term, the first field or the true field will be returned, if the comparison is
false, the second field or the false field will be returned. Your question is “Is it true that the first
comparative term is NOT equal to the second comparative term?”
> (greater than sign) If the first comparative term is greater than the second comparative term, the
first field or the true field will be returned, if the comparison is false, the second field or the false field
will be returned. Your question is “Is it true that the first comparative term is greater than the second
comparative term?”
< (less than sign) If the first comparative term is less than the second comparative term, the first field
or the true field will be returned, if the comparison is false, the second field or the false field will be
returned. Your question is “Is it true that the first comparative term is less than the second comparative
term?”
> = (greater than or equal to sign) (The order of the operators is important!) If the first comparative
term is greater than or equal to the second comparative term, the first field or the true field will be
returned, if the comparison is false, the second field or the false field will be returned. Your question is
“Is it true that the first comparative term is greater than or equal to the second comparative term?”
<= (less than or equal to sign) (The order of the operators is important!) If the first comparative term
is less than or equal to the second comparative term, the first field or the true field will be returned, if the
comparison is false, the second field or the false field will be returned. Your question is “Is it true that the
first comparative term is less than or equal to the second comparative term?”
“If” statements can be imbedded in other formulas and can be embedded within themselves. The
maximum is seven layers deep. However, with the ability of Excel to run an “If” statement on the results
of an “If” statement, the seven layer depth restriction is of little concern. If you examine the formulas in
the range of D21 through M26, the “If” statements are embedded upon each other to respond to the input
of cell D22. If the answer is not contained within E22’s string of “If” statements, then E22 looks to E23
for an answer. If E23 does not know the answer, E23 looks to cell E24 for the answer. And it continues
down into cell E26. If all of the possibilities are exhausted, E26’s last option is to respond with the phrase
"I asked you to pick a letter, not a number or a special character. Please try again.", which is the last false
condition respond for the chain.
Hint: Read the section on the “And” function as an additional capability for the “If” statement. Chapter 13 TEXT
Chapter Outline
Concatenate
Text To Columns Paste Special
Today And Now Concatenate
“Concatenate” is a powerful “text” function of Excel. “Concatenate” is the function for joining two or
more strings of “data” into a single cell. On the “Concatenate” data file you will find a string of text
entered into the individual cells of column A. In cell B1 the “Concatenate” function is written as:
=CONCATENATE(A1," ",A5," ",A6," ",A7," ",A8," ",A9," ",A10," ",A11," ",A15," ",A16,"
",A17,".")
The result reads:
The quick brown foxes were chased by the slow black dogs.
This function is taking the contents of cell A1, placing a space as shown by the “ ” presentation, then
showing the contents of cell A2 and so on. However, the text string shows that the foxes, no number
specified, were chased by the dogs, no number specified. Since “Concatenate” is classified as a “text”
formula, this would appear to be its limitations. However, as earlier stated, Excel will frequently allow
embedded formulas and “Concatenate” is not the exception to that rule.
In cell B17 several embedded formulas have been added to put numbers into the statement. The
formula now reads:
=CONCATENATE(A1," ",SUM(A2+A4)," ",A5," ",A6," ",A7," ",A8," ",A9," ",
A10," ",A11," ",SUM(A12+A14)," ",A15," ",A16," ",A17,".")
This formula or function results in:
The 2 quick brown foxes were chased by the 4 slow black dogs.
Which includes the number of foxes and dogs as summed by the embedded formulas of “Sum.”
Hint: Embedded functions and formulas do not get introduced with an “=”.
With this function you can create the mailing label string for invoicing as shown in cells A21 through
E24. In this situation, since the data is intentionally entered horizontally, the “Concatenate” formula was
written into cell E21 and dragged through rows 24. Section 2, Page 82 Chapter 13, Page 83
Assume that you are making custom pens and pencils and using “Smart Part Numbers” – numbers
that indicate through a consistency of presentation what they are or what they do. The code is:
First Part:
PN – Pen part PL – Pencil part Second Part
G – Gold S – Silver B – Black N – N/A – No color Third Part:
UP – Upper portion LP – Lower portion: Fourth Part:
TB – Tube BK – Blank TP – Top plug or insert CL – Clip MB – Middle band NB – Tip OM – Operating mechanism PNR – Pen refill PLR – Pencil refill Starting at row 27 of the “Concatenate” data file the part numbers are established by whether it is a
pen or pencil part, by its plating, whether it is an upper or lower portion part, and then the part itself.
When the part numbers are set into the matrix of cells H29 through K39, the “Concatenate” formulas in
column L assembles the number into a string of numbers (hard to remember for inventory – use
alphanumeric combinations in real life). The “Concatenate” formulas in column M reads the part number
matrix and then assembles the “code” of two and three letter identifiers through “Vlookup” functions.
Lastly, the “Concatenate” formulas in column O reads the part matrix and through “Vlookup” assembles
the text description of the parts necessary for the pen or pencil. The dashes and spaces are put in as a
portion of the “Concatenate” formula. Through the use of relational and absolute references, the formulas
can be dragged and moved without changing the values of the references. While this “little” inventory
table may be quicker to build in pure text, how about a car manufacturer or aircraft builder’s inventory?
Hint: Once the text string is built and looks good – good data management requires that they be
inspected for validity – you can use the Edit > Copy then Edit > Paste Special > Values to eliminate the
formulas and stop the transition any time something changes.
If text is entered directly through the “Concatenate” formula it must be included in double quotation
marks – “ ” as the dashes and spaces were. If embedded formulas and functions are utilized such as the
“Vlookup” function, do not precede it with an equal sign (=), Excel will present you with an error
message. The comma is used to separate portions of the “Concatenate” formula. While “Concatenate” is
limited to seven levels of parenthesis it will operate with “Look To” bringing the results of other formulas
and cells into one string. That is, you can use “Concatenate” to assemble text strings generated by other
“Concatenate” formulas as shown in cells H44 through H52 where “Concatenate” assembles the part
number, the alpha number, the description, and the cost into one string of text. The “Fixed” function is
utilized to insure that the presentation of the values remains constant – with commas and two decimal
places. Text To Columns
The “Text To Columns” function is found through the path Data > Text to Columns from the menu bar.
Through this tool you can “unassembled” text strings such as those that were built by “Concatenate” in Page 84 Solving Accounting Principles Problems Using Excel for Windows
the “Concatenate” workbook. Open the “Text To Columns” workbook on the data disk. Much of the data
is copied from the “Concatenate” workbook to demonstrate how “Text to column” works. Column A
contains a copy of the data in column B. This will allow you work with column B and then copy column
A to column B later if you want more practice. Click into cell B1 to make B1 the active cell. Follow the
path Data > Text to Columns and you will be presented with the Text to Columns dialog box. The first
choices you are presented with is “Delimited” or “Fixed Width.” Delimited means that your data can be
dissected or cut apart by some specific character or event such as a space, a comma, a tab, or other,
identifiable character. Fixed width means that your columns will be 1, 2, 3, or as you state, wide to accept
the data. And each column can be set to a different width. You can select more than one row and
accomplish the process on a range.
For the data in cell B1, a pure text string with spaces between each word and words of inconsistent
length, select “Delimited” and click on the “Next” button. The next dialog box will ask that the delimiting
values be defined. To do this, ensure that all checkmarks are removed from the dialog box except for the
checkmark for “space.” The checkmark in “Treat consecutive delimiters as one” means that if two spaces
are found next to each other, they will be treated as if they were single space. This is handy and important
as Excel is going to remove the delimiter in accomplishing this process. In this example, the spaces
separating the words will “disappear” from the text as it is processed. At this point “Text to Columns”
will give you a preview of the possible results. To advance to the next step, click on “Next.” At this point
you are offered additional options – you can select a column by clicking on it and format that column in
the import process to a limited number of specific formats such as general, text, or date, or you can select
not to import the column at all. You can also change the starting point of the “dissection process” on this
screen. If you choose a cell other then the origin cell, B2, the data will be left in B2 and “dissected” into
the cell in you specify and to the cells to the right of that cell. If the process is going to overwrite existing
data contained within those cells, Excel may provide you with a warning but relying on this degree of
data protection is risky policy. The result of clicking on the “Finish” button is that the sentence is
“dissected” into individual words placed in cells to the right and the spaces that had previously separated
those words have been removed.
Click into cell B5. This is an address cell that contains two delimiters that you want removed from the
text in the process. They are the comma and the space. Follow the path Data > Text to Columns and select
“Delimiters” and next. This time ensure that the space and comma are checkmarked and then click “Next”
or “Finish.” If you clicked “Next” you will have to click “Finish” to complete the process. This time the
data has been dissected into neat, clean cells for later processing.
Click into cell B21. Analyze this cell for delimiters and the logical choices are commas and colons.
The use of spaces as a delimiting value will break apart the alpha numbers and descriptions. While this
may leave the cost issue together, you can run another “Text to Columns” process on the cell that contains
those values later.
The fixed width option can be used to dissect one or more cells, just like the delimited option. Fixed
width will work on text and numerical values just like delimited. Click into cell B31, which contains a
numerical value. These values are summed in row 35 to prove they are numerical values. Follow the path
Data > Text to Columns and select “Fixed Width” from the options and then click “Next.” In the resulting
dialog box you see the whole number since it is not apparent to Excel how this number should be
dissected. Place your cursor between the “2” and the “3” and click once to insert a “dividing” line. Click
into the space between the “3” and the “4” to insert a “dividing” line. Since the goal is to dissect the
whole number into two number groups, this line is placed incorrectly. Click the line once and hold the left
mouse button down and drag the “dividing line to the right between the “4” and the “5”. Click between
the “5” and the “6”. Again, bad placement. Double click the bad line and it will be removed. Continue to
place lines so that the number is dissected into two number groupings. Once complete, click “Next.”
From the new dialog box you can change the formatting of the columns and select columns not to be
imported through the process if required or desired. You can also select a new starting point for the Chapter 13, Page 85
dissected data to be placed. Remember that if you choose a cell other than the origin cell, the data will
remain in the origin cell for later use. Click on “Finish” to complete the process. Paste Special
When you generate a formula within Excel it is dynamic or “live” and dependent upon its reference cells.
If you change a value in a referred cell, the results of the formula will change according unless “Manual
Calculations” has been invoked through the Tools > Options > Calculations dialog box. As shown in the
“Concatenate” function and worksheet, changing a cell will change the text string produced by the
function. There is a way to convert formulas into their results which stops this without retyping or
reentering all of the data. Copy the target cell, in this case, click into cell B1 on the Paste Special data file,
which is a copy of the “Concatenate” data file. This cell contains a live formula or function of Excel that
creates the text string about foxes and dogs. In the formula window you will see the presentation of the
formula. Use the keystrokes CtrlC, click on the copy icon (two overlaying sheets) on the tool bar, follow
the path Edit > Copy, or right click and select the “Copy” option from the popup menu to copy the cell.
This copies the formula or function onto the
Windows clipboard. Place your cursor back over the
B2 cell and right click the cell to get a popup menu
or follow the path Edit > Paste Special to get the
same popup menu. From the popup menu, shown
here, select the “Paste Values” option and click OK.
At this point Excel will replace the formula or
function with the results of the formula or function.
The “Concatenate” function is gone, the resulting
Paste Special  Values
string is no longer dependent upon the parent cells.
So changing cell A17, the word “dogs” to “house”
will not change this string any farther. However, the
text string in cell B1 is still dynamic or “live” and it
will with the text change.
“Paste Special” is a handy function for
workbooks and worksheets that are going to be
distributed to individuals who like to “play” with
your extensive formulas and functions. By selecting
the entire sheet, copying, and pasting the data back through “Paste Special > Values” the data will be
static and not dependent upon the source cells. This process also reduces the size of large Excel
worksheets significantly making them easier to attach for transmission and distribution. This feature or
function also helps when the values within the worksheet are built on values contained within other
workbooks that the recipient may not have access to.
The “Concatenate” data file was copied as the “Paste Special” data file so you can practice “Paste
Special” on its numerous dynamic or “live” formulas. Try “Paste Special” on an “intermediate” formula –
one that is called upon by another “Concatenate” function such as those formulas where part numbers are
assembled and watch the results of the costing formulas when the parts grid is changed. Today And Now
Excel has two functions that will assist you in inserting current dates and times. By utilizing the function
“=TODAY( )” Excel will present the date in the current default value for the worksheet. On January 1,
2008, this function would return 01/01/08 if that was the default of the worksheet and no other formatting
had been applied to the cell. If the cell had been formatted to MMM D, YYYY format under “Custom”
through the Format > Cell path the function would return Jan 1, 2008. Once the function is put into a cell,
that cell can be formatted to present the date in the desired format. Page 86 Solving Accounting Principles Problems Using Excel for Windows
Tip: This function does not present time correctly. Excel makes a distinction, correctly, between
“Today” and “Now” in the same manner as the timeline for mowing the lawn.
To present date and time via a formula function use the “=NOW()” function. This function correctly
inserts the current date and time. Like the “Today” function, the “Now” function can be formatted to
obtain the precise desired format for the date and time, date only, or time only if desired through
“Custom” cell formatting.
Note: These values may be updated by Excel upon opening the file later so they should be converted
to “hard values” (nonformula or function) through direct entry type in or through the Edit > Paste Special
function addressed elsewhere in this book. Chapter 14 DEPRECIATION
Chapter Outline
Asset Acquisition Sheet
Depreciation
StraightLine Depreciation
DecliningBalance Depreciation Variable Declining Balance Depreciation
SumOfYear’sDigits Depreciation
Units Of Activity Depreciation Asset Acquisition Sheet
The acquisition of a plant, property, equipment, intangible, or natural resource asset is seldom a simple
onepage document event. Take for example the acquisition of a land plot, the construction of the new
plant building, and the purchase of equipment for that plant. The land may have purchase price,
commissions, back taxes, current taxes, survey costs, title searches, title insurance, and court and filing
fees associated with it. The construction of the plant may have survey costs, architectural and design
costs, environmental impact statement costs, construction fees, insurance during construction, interest and
finance fees incurred during and after construction, legal fees, filing fees, and bonding fees. The purchase
of a piece of major equipment for the new plant may have purchase price, commissions, site surveys,
transportation, insurance during transportation and installation, installation costs, bonding of installation
contractor, licensing and certification costs, costs for test materials, costs of training materials and labor
while training line personnel and other costs. Some of these costs can be “capitalized” – put on the
balance sheet and taken to the income statement over a period of time through depreciation. Others are
period expenses and immediately taken to the income statement through an expense account. Payment of
these fees and costs may be convoluted. One check paid to an escrow agent may address several issues on
property acquisition – some to be capitalized, some to be expensed. A payment to a building fund trust
agent may have the same issues – some to be capitalized, some to be expensed costs. The documentation
of what check paid for what items and whether it is capitalized or expensed becomes important as these
issues will most certainly be reviewed later and are taxation issues in this, and many following taxation
periods.
The logical solution is to use a powerful tool, such as Excel, to retain the information and values as
well as other information. On the “Asset Acquisition” data file there is a sample of what is possible for
this purpose. It is not complex or complicated, it is simple and effective. It contains areas for
identification of the machine, the supplier and point of contact information, capitalized and period costs
detail, and where the documentation of those costs is retained. The recovery of documentation is
simplified since the file states that the purchase paperwork is being kept in a fixed asset file in Accounting
while the certification paperwork is being kept in the Floor Supervisor’s office for access and presentation
during inspections.
While this could become a “standard form”, it can easily be changed to meet the requirements of the
acquisition since, as an Excel document, it can be easily edited. To preclude editing the base copy, it
could be saved as an Excel “Template”, discussed elsewhere, making it a “Read Only” file requiring
renaming before saving after data is entered.
Section 2, Page 87 Page 88 Solving Accounting Principles Problems Using Excel for Windows
Clue: The titling of “Template” as utilized for the exercises and problems of the text has a meaning of
all of the students has the same document. Within Excel, “Template” is a (normally) read only document
that is kept in this status to maintain standardization and preclude the population of these forms.
This table, contained on the “Land” worksheet of the “Asset Acquisition” data file, utilizes “Sum If”
capabilities to determine the totals of costs that can be capitalized and costs that are period expenses.
Asset:
Land plot at 3rd and B Streets
Item:
Classification:
Amount:
Purchase price
Acquisition
$75,000
Commissions
Acquisition
$3,750
Back taxes
Acquisition
$4,200
Current taxes
Period
$1,250
Title search
Acquisition
$750
Title insurance
Acquisition
$1,500
Survey costs
Acquisition
$2,000
Filing fee
Acquisition
$45
Prepaid interest
Period
$425
Acquisition costs:
Period costs:
Total: Date:
Paid to:
Mrs. J.K. Conners
Valley Realty
San Diego County
San Diego County
County Title Svc
State Title Insurance
Inland Survey Svc
San Diego County
Lenders Banking Ltd January 2, 2008
Document location:
3rd & B St file
3rd & B St file
SD Cty Tax file
SD Cty Tax file
3rd & B St file
3rd & B St file
3rd & B St file
SD Cty Tax file
Lenders Banking $87,245
$1,675
$88,920 Since Excel can “Link” or “Look To” other worksheets and workbooks, the values in these tables can
be used to provide information to the depreciation schedules as appropriate. The key to success is
knowing what you did and being able to prove it. Excel can become a database for information using
techniques like this even though other programs are more effective; user familiarity, user comfort, and
stability are keys with retrievability and recover. Depreciation
All depreciation files are in the “Depreciation” data file on the data disk. They all utilize a single
reference source on the “Input” worksheet. Each methodology has its own worksheet for value
calculations which looks to the “Input” worksheet. However, the “Units of Activity” worksheet has an
input area where you can put monthly or annual activities in and get the proper results. This makes the
discussion comparable since, if you input values on the “Input” worksheet, all the methods will portray
values based on the same inputs in their own methodology.
There are some assumptions imposed on these sheets. There are no asset that have a life greater than
40 years and the units of activities will be fully depreciated through the number of activities within 40
years of acquisition. StraightLine Depreciation
The straightline depreciation concept is handled through the “SLN” formula of Excel. The formula is
“=SLN(cost, salvage value, life).” Since each period’s depreciation is the same value, this is a simple
formula for Excel. This formula is shown in use on the StraightLine worksheet of the “Depreciation”
data file. In the “Depreciation” data file the “StraightLine” worksheet receives its inputs from the “Input”
worksheet. The example takes advantage of absolute references and embedded formulas to generate the
period expense per month for the life of the asset, the accumulated depreciation to date, and the book
value of the asset at the end of each period. The straightline depreciation formula of Excel is found under
the financial category and requires asset cost, asset salvage value, and life. Remember that Excel will not Chapter 14, Page 89
accept commas or dollar signs within formulas. The formulas in the “Depreciation” data file utilize “Look
To” and “Absolute References” extensively. The life must be in the same factor or terms that you wish to
record the depreciation in. If you record depreciation monthly, state the life in months, if you record
depreciation quarterly, state the life in quarters, if you record depreciation annually, state the life in years.
For an asset with a cost of $2,400, a salvage value of $300, and a life of 120 months, the formula is
=SLN(2400,300,120). Because straightline depreciation is simple math the formula can be manually
entered as =(2400300)/120. Both will result in approximately $17.50 per month.
The way that the StraightLine worksheet works is the formulas is column A checks to see if
depreciation periods are still available through the formula =IF(COUNT($A$12:A12)>=$B$5,"",
A12+1). If there are available periods, the formula increments the previous value by 1. If no periods are
available the null value – “”, is put into the cell.
Note: The “Null” value is not a space or character. It is actually “Nothing”, This formula basically
says, if there are no periods available, return “Nothing.” The null value is addressed in its own section of
this book.
The formula in column B of the matrix, =IF(A12="","",SLN($B$2,$B$4,$B$5)), looks at column A
and if it “sees” the null value (“”) is there. If it is, this formula places a null value in the cell. A null value
just gives you a clean presentation for “no relevant data.” If the null value is not in column A, the SLN
formula calculates period depreciation. In column D the formula =IF(A12="","",SUM($B$12:B12)) looks
at column A for the null value and gives a clean cell response if there are no depreciation periods
available. If there periods available, this formula sums all of the period depreciation to date for
accumulated depreciation. The formula in column E, =IF(A12="","",$B$2C12), looks at column A and
gives a clean, clear cell if it finds the null value since there are no depreciation periods available. It then
subtracts accumulated depreciation to date from depreciable acquisition cost for end of period book value.
The other periodicity options for straightline depreciation utilize the same concepts for presentation. DecliningBalance Depreciation
Excel handles declining balance depreciation through the “DDB” formula found in the financial category.
The formula requires asset cost, asset salvage or residual value, life, period of life, and depreciation
factor. Excel expects the number 2 or the percentage 200% to represent doubledeclining depreciation.
Depreciation at 150% declining balance would be input as 1.5 or 150%. Excel will accept and work with
any schedule entered in these formats.
The “Declining Balance” worksheet in the “Depreciation” data file shows the formula in action with
the same basic values as utilized for the straightline depreciation above. The “Declining Balance”
worksheet looks to the “Input” worksheet for values and factors. The “DDB” or double declining balance
formula requires the cost of the asset, the salvage value, the life, the period depreciation is asked for, and
the factor. Even though the title of this formula is “DDB” indicating double declining balance, it will
accept any logical factor you input. In cell B12 the formula reads “=IF(A12="","",DDB($B$2,$B$4,
$B$5,A12,$B$7)).” The “If” statement checks for available depreciation as explained in the
“StraightLine Depreciation” section. It then looks to cells B2 through B7 for cost, salvage value, and the
declining factor. The factor will default to 2, (200%) if left blank. Since declining balance depreciation is
sensitive to the period for valuation, the “A12” reference provides the period reference.
Clue: Utilizing the DDB formula may require an adjustment at the end of the life of the asset to get
the asset to fully depreciated amount of “0”. The table below shows doubledeclining depreciation by
“Pencil and paper” without salvage value and no adjustment. If the adjustment is held until the last period
of a 5year life, the last year’s depreciation is actually $12,960 while the fourth year’s depreciation
expense is only $8,640. This type of adjustment is not uncommon with accelerated deprecation schedules. Page 90 Solving Accounting Principles Problems Using Excel for Windows
The “VDB”, or “Variable Declining Balance”, concept partially addresses this situation as it is
capable of converting to straightline depreciation when that method is more beneficial. The Variable
Declining Balance method is addressed later. Book value: Period Book depreciation: value: $100,00
0.00 $24,000.00 $36,00
0.00 $36,000
.00 Variable Declining
Depreciation $60,00
0.00 $60,000
.00 Had the salvage or
been $5,000, the last
expense would have been
computed
by
the $40,000.00 $14,400.00 $21,60
0.00 $21,600
.00 $8,640.00 $12,960
.00 $5,184.00 $12,96 residual value of this asset
0.00 period’s
depreciation
$7,960, not the $5,184 value
$7,776.
00 mathematical operation. Balance Excel has a formula that will handle the concept of accelerated depreciation at the outset of the schedule
and then converting to straightline when straightline depreciation becomes beneficial. This is
accomplished through the “VDB” formula or “Variable Declining Balance.” Like the “DDB” formula,
“VDB” requires cost, salvage value, life, period reference, and factor. “VDB” also requires a specific
statement if you want to switch to straightline depreciation or retain accelerated depreciation throughout
the schedule. By inserting a 1 (one) into the “No Switch” window, Excel will maintain accelerated
depreciation through the life of the asset, by inserting a 0 (zero), Excel will switch to straightline
depreciation when it provides a greater benefit (more depreciation in the period) than continued
accelerated depreciation. The “Variable Declining Balance” worksheet in the “Depreciation” data file
shows this formula in action.
The way the formulas in column A works is explained in the “StraightLine Depreciation” section – it
determines if there are depreciation periods available. The depreciation formula in cell B12, reads
“=IF(A12="","",VDB($B$2,$B$4,$B$5,0,A12,$B$7,0)).” After checking to see if depreciation periods
are available through the “If” statement, the “VDB” formula looks for cost in cell B2, salvage or residual
value in cell B4, the life in cell B5, the start period – 0 (zero) in this case since it is the first period, later
lines will read the previous period from the line above, then the period its in from the row its on, the
factor from cell B7, and the instruction to switch to straightline when it is more beneficial. In the
templates, this is not changeable – it is set to “Switch.” A word about the periods issue – since every
period of declining balance depreciation has its own unique value the formula must know what
depreciation period it is in. This formula is capable of determining the accumulated depreciation between
any two points of the asset’s life. Assume you are preparing to sell an asset under “DDB” depreciation
scheduling at the end of the 8th year of its 10 year life. You can use this formula written as
=VDB(100000,5000,10,0,8,200%,1) to determine that accumulated depreciation to date would be
$83,222.78. In the data file you are trying to determine each period depreciation so you use 0 for the start
of the first period and 1 as its end. For the second period you are using a reference to end of period 1 as
the start of this period and a reference to period 2 as the end of this period. Chapter 14, Page 91
Tip: It is very dangerous to use the “VDB” formula to determine accumulated depreciation for
declaration of gain and loss on asset disposal since this mathematical calculation may not match the actual
journalized values for depreciation.
By using the “VDB” formula and enabling it to switch to straightline depreciation when more
beneficial there is no need for the adjustment required at the end of the life cycle as shown in the “DDB”
formula section. Additionally, this formula will not violate salvage value if the switch is enabled. SumOfYear’sDigits Depreciation
Excel handles Sumofyear’s digits depreciation through the “SYD” formula. On the “Sum of Year’s
Digits” worksheet of the “Depreciation” data file you will see this formula in action. The sumofyear’s
digits formula requires the cost, the salvage value, the life, and the period. With the “SYD” formula it is
recommended that you remain with years as life and divide the annual amount of depreciation by 12 if
you are posting depreciation monthly or divide annual depreciation by 4 if posting depreciation quarterly.
Monthly depreciation is provided on the worksheet as annual depreciation divided by 12 as well as
monthly depreciation based on monthly life for comparison.
Like the previous formulas, the “SYD” formula in the worksheet looks like
“=IF(A12="","",SYD($B$2,$B$4,$B$5,A12)) where absolute references acquire the values from the top
of the worksheet and relational references get period references. This allows the formula to be written in
the second row of the matrix and dragged down the worksheet.
The “SYD” formula will not violate salvage value or require an adjustment at the end of the asset life
as “DDB” and “unswitched” “VDB” may. UnitsOfActivity Depreciation
Excel does not have a formula to handle units of activity depreciation. While the concept is rather simple,
the sum of acquisition cost less salvage value divided by the expected units yields a per unit of activity
depreciation value, that value is multiplied by the units of activity in the period to get period depreciation
expense. Excel formulas to handle these issues get rather complicated if written to provide period
depreciation, calculate book value, and calculate accumulated depreciation without violating the concepts
of salvage or residual value. The “Units Of Activity” worksheet within the “Depreciation” data file has a
working model of how this function can be written into Excel. This model makes extensive use of
embedded formulas to ensure that depreciable value is not exceeded either in total or in a period entry.
Examination of the formulas in row 12 will show that the initial formula in this line is different then the
formula on row 13. This difference allows for a single period to fully depreciate the asset – it should have
been a period expense if that were possible, then it checks and provides the appropriate depreciation for
the period. In lines 13 and below, the formulas evaluate accumulated depreciation of prior periods to
determine available depreciation then it determines period depreciation and precludes the violation of
salvage value. When utilizing this worksheet you can put in period activity to determine period
depreciation. The option of entering annual data and having Excel divide that value into 12 evenly valued
units for monthly data entry is provided. A monthly input matrix is also provided. Then the worksheet
subtracts accumulated depreciation from cost to get book value.
This worksheet does not utilize the concept of “If” statements referencing the life or period column
since they are not relevant to units of activity depreciation. Chapter 15 LOANS AND THE TIME VALUE OF
MONEY
Chapter Outline
Cash Flow Within Formulas
Loan Payments
Payment To Principle
Cumulative Payment To Principle
Interest Payment Cumulative Payment To Interest
Present Value
Future Value
Bond Table Cash Flow Within Formulas
There are many formulas within Excel, particularly those regarding the payment of notes payable –
“PMT”, notes payable interest payments – “IPMT”, and notes payable principle payment – “PPMT” that
are sensitive to cash flow issues. For example, the formula to calculate the payment for a notes payable
issue would be “=PMT(interest rate for the compounding period, the life of the note payable in the same
periodicity as the interest rate, the principle amount, the future value or balloon payment, and whether the
payment is made at the end of the interest period – 0, or at the beginning of the interest period – 1. In cell
D19 on the Basic Formulas tab of the Basic Data and Formulas workbook the formula is written as
=PMT(C14/12,C15*12,C16,C17,C18). In this formula the formula “looks to” cell C14 and divides the
value that it finds there by 12 to get the monthly interest rate, it looks at cell C15 to get the life of the note
payable in years and multiplies it by 12 to get the life in months – IMPORTANT: The interest rate must
be in the same periodicity as the life of the note payable as shown here – months. It may be quarters,
semiannually, annually or some other appropriate value. Then the formula looks ate cell C16 to get the
principle value. This value is a positive value indicating that the cash flow is into the writer for the
formula. If the cash flow is out from the writer of the formula the value should be negative. This affects
the presentation of the answer. Then the formula looks at cell C17 to determine the value of the balloon
payment and looks at cell C18 to determine if the payment is made at the end (ordinary annuity) of the
interest period or at the beginning (annuity due) of the interest period.
Since the value of the principle positive, the answer will be negative. In the data files negative values
are usually defaulted to be in red text and in parentheses such as ($1,100.65). This enhances the
presentation on the display screen and on the printout even if the printout is a single color text as laser or
inkjet black since the red text will be slightly fainter than the black text of the display. Had the principle
value been negative indicating cash flows out at the initiation of the note payable the answer would be
positive in nature and would be displayed in black as $1,100.65. The formula in cell D19 has been set up
so that you can change the values of the formula and see the change in the answer. The cells below D19
are formatted with other options but utilize the same data to show some of the various formatting options
available. These options include currency variations, accounting variations, and number variations. Notice
that the right side of the cells are not evenly justified amongst themselves due to the different formatting.
Section 2, Page 92 Chapter 15, Page 93
Also the dollar sign ($) is not always placed in the same position amongst the different formatting. This
issue is addressed later in the text.
While the issue of positive or negative results of these cash flow sensitive formulas are important, the
presentation of negative numbers may be undesirable regardless of the nature of principle value. The
“Boss” knows the cash flow as in when the note payable was signed and he knows the cash flow for
payment is out or negative and he wants positive values for a “nice” presentation. One option is to write
the formula with an additional factor imposed making the input negative within the formula or making the
result positive through a secondary formula. In the case of making the principle negative, it is understood
that the principle upon initiation will always be shown as a positive number. By adding “*(1)” to the
principle portion of the formula, cell C16 as shown here =PMT(C14/12,C15*12,C16*(1),C17,C18) and
as shown in cell D29, the result will be positive if the principle value in cell C16 is positive. However, if
the value in C16 is negative, cell D29 will be negative since it multiples the principle value by negative
one making it a positive principle and Excel states positive principles have negative payments. To solve
this issue you can add a secondary formula making an absolute value of the answer regardless of the input
values through “ABS” as shown here =ABS(PMT(C14/12,C15*12,C16,C17,C18)) and in cell D31. With
this secondary formula, Excel works from the inside parentheses outward solving the payment formula
first than making the result of the payment formula an absolute value – always positive through the
“ABS” additional. Notice that the *(1) is not required or utilized in this formula.
Remember that features making the results of cash flow sensitive formulas always positive may be
nice looking and may be required by “The Boss”, they are theoretically incorrect and may present a loss
of points on homework and criticism on publicly released information. Loan Payments
Excel has numerous formulas that address loan payments. The most commonly used formula is the
“PMT” (Payment) formula. This formula is under the “Financial” category. With “PMT” you provide
Excel with the period interest rate, the number of periods, the present value, the future value if any, and
determine if the payment is made at the beginning or end of the period. The quickest way to access this
formula is by clicking on the Fx of the formula entry window and ask for “PMT” (Payment). Excel will
present you with the “PMT” (Payment) dialog box which looks like the box presented later in this section.
Excel requires the interest to be expressed in the same manner as the periods and the periods are
determined by the compounding feature of the loan. If the interest rate is 12% annually but compounded
monthly you can enter 12%/12 or 1%. The entry of 12%/12 should be considered safest since you do not
have to do math outside the formula to
determine values like 9 3/8% interest
compounded quarterly, you can enter it
as 9 3/8%/4, as shown, rather than
calculate it out as 0.0234375 in
decimal. You can use parentheses such
as (9 3/8%)/4 to ensure the results.
And, Excel will take the generated
value to appropriate significant digits
without interference or limitation.
Since the loan is a 10year loan with
quarterly payments you can enter 10*4
into the Nper or number of periods
window. The principle amount is
entered in the Pv window. As typical
with Excel, no dollar signs or commas
are appropriate. Page 94 Solving Accounting Principles Problems Using Excel for Windows
Clue: The cash flow direction of the principle is important to Excel. If you enter a positive value into
principle value that indicates you are getting the principle and making the payments. As such, the
payments will become a negative value indicating cash flows out. If you enter the principle value as a
negative number that indicates you are making a loan to someone and the principle is cash out so
payments will be positive indicating cash in. In the screen print of the Payments dialog box, the formula
results appear to be a negative 387.9278455 so Excel is indicating that the loan is to you and you are
going to make payments of $387.93 at the end of each payment period since the type box contains no
declaration and will default to “0” – payment at the end of the period. Had the principle value been
negative, the payment would have been positive indicating that you are providing the principle to
someone else and someone is making period payments to you.
The “Fv” window is for future value, if the loan has a balloon payment, the value of that payment
would be placed in this window. As with principle value, Excel is sensitive as to whether the balloon
payment is cash out – negative, or cash in  positive, and this determination will affect the final results.
The “Type” window is where the schedule of payment, at the beginning or the end of the period, is
established. The default is 0 (zero) and is assumed to be payment at the end of the period if not provided.
If populated with the provided data, clicking on the OK button will post the formula,
=PMT(9.375%/4,10*4,10000,0), to the worksheet. A review of this formula confirms your data entry. A
quick check of the result, $387.95 X 40 payments indicates that you will be paying approximately
$15,518 in payments over the 10 years of the loan. This value seems reasonable and can be checked with
two other Excel formulas “PPMT” and “IPMT”. Examine the “Loan” data file on the data disk to see how
a loan table can be built with these formulas as an integral part. The “PMT” formula is found in cell F16
of the “Loan” worksheet and in cell F18 of the “Balloon Payment Loan” worksheet on the “Loan”
worksheet in the Loan data file and is referred to in the matrix. Payment To Principle
Payment to principle of a loan payment for a period can be determined by the Excel “PPMT” (Payment
To Principle) formula. The data windows for this formula are very similar to the data entry windows for
the “PMT” (Payment) formula detailed above except that “PPMT” asks for the period that you want the
payment to principle for. This can be period 1, period 2, period 40, or any other period in the applicable
range of the loan. On the “Loan” data file, this value is defined in column A. In the “Loan” data file, the
maximum life of the loan is 100 years with monthly payments. In this example, the range is from 1 to
(100 years X 12 months per year) 1,200. The output value is under the same logic as “PMT” – if the
principle is positive, the payment will be negative. Examine the “Loan” data file to see an example of
how a loan table can be constructed with these formulas. The “PPMT” formula is contained within
column D of the “Loan” and “Balloon Payment Loan” worksheet matrix on the “Loan” data file. It looks
to column A to see if the line is applicable to the loan –
unneeded lines utilize the “null value” to appear clear
and clean. The “Null Value” is addressed in its own
section elsewhere in this book.
The “PPMT” formula will assist you in determining
how much of a loan payment will reduce the obligation
of the loan with the payment. Cumulative Payment To Principle
The formula “CUMPRINC” will generate the
cumulative principle paid or received to date if all
payments were made or received appropriately. The
dialog box for the “CUMPRINC” formula is shown Chapter 15, Page 95
here. The input values are similar to other loan associated formulas and functions. While this is an easy
formula to set up, it does not take into account or have the capability of addressing loans with balloon
payments. To overcome this situation, the “Balloon Payment Loan” worksheet of the “Loan” data file
simply uses a “Sum” formula from rows 23 on down in column H. The technique within the “Balloon
Payment Loan” worksheet is sum from an absolute reference of row 23 through its relational row as
dragged through the matrix so the formula in column H on the “Balloon Payment Loan” worksheet looks
like SUM($D$23:D23) since column D contains the payment to principle values. When the formula, with
its added “If” statement to provide the null value for a clean appearance if the period is not applicable to
the loan, is dragged down the relational “D23” cell reference will increment to the row so the formula in
row 24 will appear to be SUM($D$23:D24), thus summing the values of column D rows 23 and 24. The
power of Excel can be used to overcome its few faults and weaknesses.
The “CUMPRINC” (Cumulative Payment To Principle) formula is shown in column H on the “Loan”
worksheet of the “Loan” data file in column H. However, no balloon payment data can be input on this
worksheet. Interest Payment
The payment to interest of a loan payment for a period
can be determined by the “IPMT” (Payment To Interest )
formula of Excel. The dialog box for this formula is very
similar to the data entry windows for the “PMT”
(Payment) function detailed earlier except that “IPMT”
asks for the period that you want the payment to interest
for. This can be period 1, period 2, period 40, or any
other applicable period within the range of the loan for
the loan example. In the “Loan” data file, the period is
defined in column A. The output value is under the same
logic as “PMT” – if the principle is positive, the payment
will be negative, if the principle is negative, the payment
will be positive indicating cash flows in and out.
This formula is contained within column E of the
“Loan” and “Balloon Payment Loan” matrix in the “Loan” data file. The “If” statement determines if the
period is applicable to the loan. If the period is not relevant to the loan, the “If” statement returns the null
value for a clean and clear presentation. If the period is applicable, the “IPMT” formula looks to the input
data through absolute reference to attain the values for the “IPMT” formula.
Examine the “Loan” data file to see an example of how a loan table can be constructed incorporating
this formula.
The “IPMT” formula will assist you in determining how much of a loan payment will be the interest
cost value associated with the loan with the payment. Cumulative Payment To Interest
The “CUMIPMT” (Cumulative Payment to Interest) formula will generate the interest for a range of
periods rather than one specific period. Its dialog box is very similar to the “CUMPRINC” (Cumulative
Payment To Principle) discussed earlier. As with the “CUMPRINC” (Cumulative Payment To Principle)
formula, this formula does not take balloon payments into account. Due to the fact that the “Balloon
Payment Loan” worksheet was constructed to take balloon payments into account, this formula was not
usable in that data file structure so the sum function discussed under the “Cumulative Payments To
Principle” section was utilized in column I of the “Balloon Payment Loan” worksheet. This formula can
be seen in column I of the “Loan” worksheet of the data file. Page 96 Solving Accounting Principles Problems Using Excel for Windows Present Value
Excel has a very powerful and easy to utilize
formula for present value calculations under the
title of “PV” in the financial category. This
single formula will handle present value
calculations of single sums or annuities as well
as adjust them for payment at the beginning or
the end of the period. The inserted screen print
shows the “PV” (Present Value) dialog box. The
“Present Value” worksheet in the “Time Value
Of Money” data file on the data disk has
numerous examples of this formula. The basic
formula is =PV(Period interest, Number of
periods, Amount of each payments, Future
value, Payment at the beginning or the end of
the period) with payment at the end of the
period indicated by a 0 (zero) and payment at the beginning of the period indicated by a 1 (one). Leaving
the “Type” (Payment type) window empty causes Excel to default to payment at the end of the period. By
omitting the value for payments in the “PMT”, or by placing a 0 (zero)(best action), in the window, and
providing the “PV” dialog box or formula a “FV” (Future value), the formula will produce the present
value of a single sum. By placing the amount of an individual payment of an annuity in the “PMT”
(Payment window), the formula will produce the present value of an annuity. For annuities due place a 1
(one) in the “Type” window to indicate payment at the beginning of the period, for an ordinary annuity,
place a 0 (zero) in the “Type window or leave it blank to indicate payments at the end of the period.
Hints and Tips on utilizing the dialog box – First, if you establish the data matrix as shown on the
“Present Value” worksheet in cells A1 through D7 you can have formulas installed that will take the life
in years and multiply it by the compounding factor for Nper. You can also divide the interest rate by the
compounding factor to get the value for the period rate. Remember that, as with working with the tables
in the textbook, the interest rate and the number of periods must be expressed in comparable values. If the
document states that interest is 8% annually and it is compounded quarterly and that the life of the annuity
is 10 years, the effective values are 2% interest (8% / 4 quarters per year) and 40 periods (10 years X 4
quarters per year). Excel can do this within an input matrix or within the formula by entering a value such
as 8%/4 or 10*4.
The formula will also produce the value of $1, just like the tables in the textbook by placing the value
of 1 (one) in the “PMT” (Payment) or “FV” (Future value) window as appropriate. This value will be
carried out to more significant digits than the textbook’s tables even if formatted to show the same since
Excel keeps the real value in its “mind” even with trimmed or formatted presentations.
Excel works with standard finance and math logic. If the “PMT” (Payments) values are positive
indicating receiving cash or value, the “PV” (Present value) will be negative indicating cash or value
flowing out. If the “PMT” (Payments) are negative indicating cash or value flow out, the “PV” (Present
value) will be positive indicating cash or value flow in.
This worksheet has extensive “Concatenate” formulas to rephrase the statement for each formula.
You can read the “Concatenate” function explained elsewhere in this book. Future Value
Excel has a formula similar to “PV” (Present Value) for “FV” (Future Value) located under the Financial
formulas category. This formula is demonstrated on the “Future Value” worksheet of the “Time Value Of
Money” data file. The formula is written as “=FV(Period Interest, Periods, Payments, Future value, Chapter 15, Page 97
Payment at the beginning or the end of the period).” As with the “PV” (Present Value) formula, interest
rates and periods must be stated in the same terms. The appearance of the dialog box is very similar. Like
the “PV” (Present Value) formula this single formula with various inputs will provide the “FV” (Future
Value) of a single sum or a series of payments  an annuity. It will accept payments made at the beginning
or the end of the period. As with the “PV” (Present Value) formula, the “FV” (Future Value) formula will
produce the value for the sum of $1. This value will be kept within Excel to a greater number of
significant digits than your textbook’s tables. This may cause a slight difference between Excel generated
“PV” (Present Values) and “FV” (Future values) but the differences will not be material in nature –
pennies on thousands of dollars. Bond Table
Excel will handle the task of a bond issuance, determination of present value of the issue and both
effective and straightline amortization of the premium or discount through numerous formulas including
present value. The application of these formulas on the “Bonds – Premium” and “Bonds – Discount”
worksheets of the “Bonds Table” data file closely matches the structure of the bond table in your
textbook. As stated in the textbook, the essential pieces of information for a bond issuance are face
interest rate, market interest rate, periodicity of bond interest payments, life of the bond, number of bonds
issued and the face value of each bond as well as whether the amortization will be effective or straightline amortization. All of the worksheets in the “Bond Table” data file contain this information in the
upper portion and these values are used throughout the worksheets. By using the PV” formula of Excel
you can determine the present value of the principle and the present value of the interest payments. This is
done on rows 11 and 12 of each worksheet. The sum of these two values is the present value of the
issuance, shown on row 13. The difference between the present value of the issuance and the face value of
the issuance, (or the difference between face or stated interest and market interest) determines whether the
bond was issued at a premium or discount and the amount of that premium or discount. In the “Bonds –
Premium” and “Bonds – Discount” worksheets formulas have been kept to an absolute minimum to
closely correlate to the textbook presentation. Because of this, the worksheet will only present properly if
the proper worksheet is utilized for the data. Contained on each worksheet, off to the right, is also
straightline amortization of the premium or discount on that issue. With the power of worksheets
available on the desktop system effective interest amortization computation is almost as easy as straightline amortization – except that because of the varying amount per period for effective interest method the
journal entry cannot effectively be memorized.
If you look at the “Bond – Premium” worksheet, which is structured to accept semiannual interest
payments only to limit the use of formulas, you can see how the “PV” (Present value) formulas were
utilized to determine the present value of the issue. In rows 17 through 19 the journal entry for the
issuance of the bond is shown. In cell B24 you can enter the period of interest and see the journal entry
for that period. This is done through “Vlookup” formulas in cells E24 through F26. “Vlookup” formulas
are discussed elsewhere in the text. Since formulas are minimized and the worksheet is structured to take
up to 40 years or 80 periods, look at the results of the matrix when the term of the bond is 40 years. Then
change the term to a value like 10 years and the worksheet matrix “gets messy” and you have problems
finding the end of the data. Then change to premium interest rates to discount interest rates – face value
below market value, and look at the matrix and the journal entries. This same structure and problem exists
in the “Bonds – Discount” worksheet because of the rigidity of the formulas – two, three, or four values
and only one process.
In the “Bond Table” worksheet, contained in the “Bond Table” workbook, there are extensive “If”
statements that address all of these issues and add flexibility to the worksheet. With the structure and
formulas of the “Bond Table” worksheet you can enter up to 40 years for the bond life and specific if it is
semiannual or annual interest rates. There are “If” statements that “read” the interest rates as provided
and respond with a declaration of premium or discount. If you manage to “float” a bond with face at
market this worksheet has no problem with that issue either. In rows 17 through 19, the proper journal Page 98 Solving Accounting Principles Problems Using Excel for Windows
entry will be structured through “Look to” formulas and “If” statements which contain text responses
containing account titles and placements for all three possible issuance conditions. In rows 24 through 26
the period journal entries can be brought up by entering the period number in cell B24. In the matrix
itself, the “If” statements in column B present the period increments appropriate to the life of the bond or
the “Null Value.” This “If” statement process determines if the rest of that row will display making the
matrix neat and clean for lines not relevant to the life of the bond issue. Inside the columns sensitive to
premium and discount issues such as interest expense, “If” statements determine if the issue is a premium
or discount and then provide the math for that specific issue.
Reviewing the “Bond – Premium” and “Bond – Discount” worksheets and their simplicity and
presentation of data and then at the results of “Bond Table” and extensive “If” statements. Effort and
knowledge can provide professional methods of presentation.
All of the worksheets contain straightline amortization of premiums and discounts off to the right.
These tables look to the effective interest rate data so you can do a comparison of the two methods. You
can easily take this workbook on a 3.5” data disk as it is less than the disk’s standard capacity. It is also
on the data disk as “Bond Table Static” in a “static state” where all the formulas have been removed
through Copy and then Paste Special > Values. In this format the file is 89KB in size. While it presents all
of the data as presented, the formulas are gone making the workbook smaller, easier to transmit, and
transport.
Clue: The workbook is protected but there is no password. Chapter 16 ADVANCED EXCEL
Chapter Outline
Average & AverageA
Command Access
Convert
Embedded Formulas Intermediate Formulas
Keystrokes & SHortcuts
Linking Worksheets
Macros Average & AverageA
Within Excel there are two “Average” functions under the “Statistical” category. The first is “Average”
and returns a simple average of an identified range. This formula is shown in operation on the “Average”
data file on the data disk. The function written in cell E2 determines the average number of the category
“Apple” – cell B2. Cell E3 determines the average number of “Apples”, a range from cell B3 through
B11. The formula in cell E4 determines the average number of “Berries”, a range from cell B12 through
B23 while the formula in cell E5 determines the average number of “Cherries” in a range from cell B24
through 33. This formula is simply written as “=AVERAGE(Range).” The range can be a continuous
string of cells in a column or row or can be a range of cells such as columns and rows such as the formula
shown in the range of F10 through M31. Average will not count text strings into its averages.
The second formula is “AverageA”. With this formula you can even account for text strings in your
average such as “True” – a value of 1, “False” – a value of 0, while the occurrences of text strings such as
“N/A” get counted in the denominator of the average equitation but there is no value for them in the
numerator of the equitation. This is a handy tool for the average number of correct answers on a
“True/False” question as shown in column O of the “AverageA” worksheet. Command Access
Excel has numerous command paths to accomplish almost every one of its many commands. The
selection and utilization is up to you. If you are a proficient typist, you may select keyboard commands
while typing and mouse commands while doing graphic tasks. Others will always select the mouse while
some will use the “Alt” (Alternate) key path.
The most common method of accessing Microsoft Excel commands is through the mouse. With this
concept, you would select a word on the menu bar by placing the mouse cursor over it and clicking on it.
A dropdown menu attached to the menu bar would appear with the options available at that point in time
and available in relation to that position on the worksheet. If an item is grayed out or faded, it is not
available for some reason. You would then select the desired option by clicking on it with the mouse. If
the option has an arrowhead pointing to the right or left, that selection has other choices available to it and
selecting it or placing the cursor over that selection will result in an expanded display for that selection.
Once shown, clicking on the desired option will result in its selection. If no selection is found, or you
decide you are in the wrong path, clicking anywhere other than on the dropdown menu will back you out Section 2, Page 99 Page 100 Solving Accounting Principles Problems Using Excel for Windows
without a selection being activated. You may also press and release the “Esc” (Escape) key to cancel the
menu and avoid a selection.
The second methodology of selecting commands it to use keyboard commands. With the use of a
predecessor key such as the “Ctrl” (Control) key or the “Alt” (Alternate) key on the keyboard other
options become available with additional keystrokes. For example, to copy something using keyboard
commands, activate the cell or cells you would like to copy and then press and hold the Ctrl (Control) key
down and then press and release the “C” key. This series of keystrokes is shown throughout the text as
“CtrlC”. The shift key is not hit and it does not matter if the keyboard is locked into upper or lower case
at the time. Once the “C” key is struck, the keys can be released in any order.
An alternative is to use the “Alt” (Alternate) key to access the menu bar. By striking this key you will
see the first item on the menu bar become active. By using the arrow keys to move left and right as well
as up and down, you can prowl the menu bar for your selection. When your selection is located, striking
the “Enter” key will select and activate the command you have chosen.
Regardless of the method you have used to access the command structure of Excel, the “Esc”
(Escape) key will usually back you out of the command path without making a selection. It may take one
or more strikes of the “Esc” key to accomplish the backout if you are several “layers” into a selection
process. Convert
The “Convert” function will accept one value and then convert that value from one measuring standard to
another measuring standard. Tired of trying to remember that 2.2 pounds is roughly 1 kilogram and have
to convert 825 pounds to kilograms for an shipping invoice? Try the “=SUM(CONVERT(825,
"lbm","g")/1000”. One of the requirements for this function to work is that the “Unit Id” must be in
double quotation marks and in the proper case – some upper case, some lower case. For example, while
Excel ignores “=CONVERT(”, “=Convert(”, “=convert(” upper or lower case issues, it will “fault out” if
the formula is written as “=CONVERT(825,“LBM”,g)” since it will not recognize the unit ID of upper
case “LBM” because of the upper case issue. Nor will it recognize the lower “g” without the double
quotation marks.
Utilize the “Help” screen for “Convert Worksheet Function” from Microsoft Excel for more units and
information. This table is a brief listing of the values that Excel can convert from and into:
Unit Id:
Weight:
"g"
"lbm"
"ozm"
Distance:
"ft"
"in"
"m"
"mi"
"Nmi"
"yd"
Time:
"day" Unit Title:
Gram
Pound mass
(avoirdupois)
Ounce mass
(avoirdupois)
Foot
Inch
Meter
Statute mile
Nautical mile
Yard
Day Unit Id:
Temperature:
"C"
"F" Unit Title:
Degree Celsius
Degree
Fahrenheit "K"
Degree Kelvin
Liquid Measurement:
"cup"
Cup
"gal"
Gallon
"l"
Liter
"oz"
Fluid ounce
"pt"
U.S. pint
"qt"
Quart
"tbs"
Tablespoon
"tsp"
Teaspoon Chapter 16, Page 101
"hr"
"mn"
"sec"
"yr" Hour
Minute
Second
Year On the “Convert” data file there is a data matrix of
common convert terms or “Unit Ids.” The values within
column B cannot be utilized as “Look to” values since
they are in quotation marks, the way you are required to
type them into Excel. However, the column is
duplicated in column C without quotation marks. This
allows you to write a formula such as
“=CONVERT(B1,C11,C10)” in cell C1 to convert the
value of 825 contained in cell B1 from pounds as
identified through the value contained in cell C11, to
grams through the value contained in cell C10. Had you
utilized the column B values of the “Convert” data file for these references you would get a fault
indication such as “#N/A” or “#Name”. By looking at the dialog box you would see that Excel is
interpreting the formula as “=CONVERT(B1, ““B12””, ““B10””)” which contains too many double
quotation marks around the values – so the double quotes are removed from column C for your ease of
use. Embedded Formulas
As addressed elsewhere in this book, the prefix of “=SUM(” starts Excel into the “I have to do something
with formulas and functions” mode. As shown elsewhere in this book, Excel will perform functions and
formulas within other functions and formulas. This embedding capability generates a power and
flexibility within Excel that greatly aids the professional. The way to embed a formula or function is
relatively simple. First and foremost, understand the functions and formulas you are utilizing. For
example, if you are using the “Count If” function on an inventory sheet to find the total number of apples,
your results will be incorrect. This is shown on the Embedded Formulas data file where the desire was to
sum the number of apples, berries, and cakes but the “Count If” formula resulting in finding out how
many times the text apples, berries, and cakes each occurred in the data field.
In the Embedded Formula data file the first task is establish a value for inventory by finding the value
of the apples, the berries, and the cakes and then summing those subtotal values. In cells G23 through
G25 and G27 Excel finds the number of each item. The values for these items as individual elements is
contained in cells H23 through H25 and H27. In cells I25 through I25 and I27 the “Sum If” formula
determines the sum total of each element and then Excel multiplies it by the element value contained in
the H cell ranges. For the apples on row 23 the formula is:
=SUMIF($A$2:$A$250,F23,$B$2:$B$250)*H23
This embedded formula is the “Sum If” formula and a multiplication function by adding the “*H23”
at the end of the previously discussed “Sum If” formula. By using the “Look To” capability you can
change the element value of the inventory items and see the results immediately.
Suppose that we wanted to be alerted if the value of any inventory category exceeded $20,000. This
can be accomplished through conditional formatting – covered elsewhere, so utilize “Concatenate”, a
function discussed shortly, and “IF” statements. The formula in I23, broken down into three lines due to Page 102 Solving Accounting Principles Problems Using Excel for Windows
its length, reads like this:
=IF((SUMIF($A$2:$A$250,F33,$B$2:$B$250)*H33) > $I$31,
CONCATENATE("$",FIXED(SUMIF($A$2:$A$250,F33,$B$2:$B$250)*H33,4,0),
" Exceeds target value"),SUMIF ($A$2:$A$250,F33,$B$2:$B$250) *H33)
To understand this function appreciate that Excel looks at the deepest pair of parentheses and then
starts working its way outward. In this case those parenthesis are contained within the “Sum If” function.
So in line 1 of the formula Excel sums the number of directed inventory items, those identified in cell F33
– Apples, and multiplies the summed number by the value in cell H33  $0.12 per element item as
supplied on the data disk. Then Excel is instructed to compare this total value to the value in cell I31 and
determine if the summed value is greater than the value in cell I31. If the summed value is greater than
the value in cell I31, $20,000, as supplied on the data disk, Excel moves to the set of parenthesis inside
the formula – those of the “If” statement. Since the statement is false (9,674 apples at $0.12 each total
$1,160.8800) Excel executes the function contained in the second part of the “If” statement. This is
actually a repeat of the “Sum If” formula summing the number of referenced inventory items and
multiplying that sum by the element cost contained in cell H33.
However, in cell I35 the statement is true. The 15,771 cakes at $1.8700 each total to $29,491.7700
which exceeds the reference value in cell I31. Since this “If” statement is true, Excel executes the first
half of the “If” statement – the concatenate function. This function is set to sum the total number of
referenced inventory items – cakes, and multiply that sum by the cost in cell H35. Then Excel is told to
format this value to 4 decimal places and with commas through the “Fixed” function. Concatenate adds a
dollar sign as text in front of the total value for a presentation of “$29,491.7700”. The concatenate
function adds spaces and a text string saying that this value exceeds the limit. While not real useful in life,
as will be shown in the Intermediate Formulas section to follow, this string of formulas and functions
demonstrates the power Excel can provide if the user has imagination and knowledge.
Notice that the only equal sign in the formula is the one at the beginning of the long formula string.
An equal sign buried within the formula such as in front the “Sum If” or “Concatenate” formulas and
functions will result in an error message from Excel. You can only nest 7 sets of parenthesis within Excel.
However, as shown in the section on Intermediate Formulas, this is only a restriction within a cell, not
within Excel per say and can be easily compensated for.
The actual solution to this problem should have been conditional formatting of the cell or an “If”
statement as shown in cells F40 through J45 where both “If” statements and Conditional Formatting are
used on the values resulting in easier to write and troubleshoot the formulas. Intermediate Formulas
Excel has many built in formulas and functions which can be
found through the Fx icon on the tool or tool bar. Shown here
is the dialog box that is presented when you click on the Fx
icon and then search all functions or the Math and Trig
functions. This dialog box provides an index of most of the
structured Excel formulas and functions. Two very nice
features of this dialog box are (1) the ability to select “All” as a
category and scroll through every function and formula, and (2)
the brief summary of actions and capabilities of the formula or
function in the lower third of the dialog box, shown inside the
rectangle on the screen print. In this screen print the “Sum If”
function is selected. The dialog box tells you that it adds the
cells specified by a given condition or criteria. Exactly what we Chapter 16, Page 103
were seeking in the “Sum If” tasking for the valuation of inventory earlier.
This dialog box can be narrowed down in its search through the selection made in the “Or Select A
Category” window in the upper third of the dialog box. The categories include financial, math & trig,
statistics, lookup and reference, text, and logical. Selecting one of these categories will narrow your
search but may preclude you from seeing a viable option contained under another category.
Formulas and functions in Excel normally start with the equal sign, “=”, which keys Excel into
expecting to accomplish something through a formula or function. From this point, text, data, and
reference information is provided. The simple Excel formula of =B2, a “Look to” formula or function,
placed in cell C1 would simply provide you with the contents of cell B2 being presented again in cell C1
as shown on the Intermediate Formulas data file. This formula can be used through many of the exercise
and problem templates as the dates and values as well as some account titles are available in a “clean
target” or source presentation. Excel will also do “Look to” on text values as shown in cell D2.
When text is incorporated as part of a formula or function within Excel it must be enclosed within
double quotation marks or Excel will product an error message. In the “If” statement, a logic function or
formula, shown in cell D12 in the Intermediate Formulas data file, you will see the word Apple or Apples.
The formula or function, an “If” statement, in cell E12 is looking at cell D12 and responding based on the
value in D12. If the value is singular – “apple”, Excel hopes it’s a Washington State Apple. If the text is
“apples” – plural, Excel hopes they are all Washington State Apples. However, if the text string is neither
“Apple” or “Apples”, Excel tells you that it is not apples. Excel cannot really read, it is comparing text
strings for validity. The formula says if you find the text string of “Apple” respond with the first of the
“true” statements, if the text string is not equal to “Apple” Excel executes it false statement which is
another “If” statement to see if the text string is equal to “Apples.” If Excel finds a matching text string,
Excel responds with the second true statement in the formula, the plural response for “Apples.” If Excel
did not find the text string “Apples” on the “second look”, it responds with the false response which
declares they are not apples.
As addressed elsewhere in this book, there is a nesting limit of 7 levels within Excel. Can Excel
determine what letter of the alphabet you entered and respond based on your input if there are 26 letters to
choose from? Certainly. Enter a letter (A to Z in upper or lower case) in cell D21 and watch the response.
Excel will determine the letter and respond for that letter. To overcome the 7 layer restriction on nesting,
after 6 levels, the formula looks to the next line – hidden by making the text white – for an answer. All of
the lines, 4 in total, look to cell D21 and evaluate the value. If the value exceeds the limits of the first line
on 21, the formula in the first line looks to line 22. If the value in cell D21 matches one of the values in
line 22’s range, line 22’s formula responds, if line 22’s range is exceeded, line 22 looks to line 23, and
then line 23 looks to line 24. This process can continue until you run out of cells in the worksheet. If line
24 has the valid answer, it passes it to line 23 which passes it to line 22 which passes it to line 21 which
displays the valid response as the “False” response to its own “If” statement. The “False for line 21 is
look at line 22. After filling a worksheet with chained references you can reference the next worksheet in
the workbook. After the number of worksheets is exceeded, you have Excel reference a second, then
third, then fourth workbook and so on. So, while there may be a nesting limit within a cell of Excel, there
is no true nesting limit if you “Look To” the next cell, worksheet, or workbook.
Often a formula can be set up in a “template” format for frequent use without new formula structures
being required. Suppose that you did a lot of work with the Present Value formula. Set the formula up as
shown in cells D31 to G35. In cell G32 the present value formula resides and looks to the cells D31
through D35 for information. Anytime that information is changed the formula recalculates the present
value. (Almost) Instant answers to a formula with little typing once the formula is set up.
Occasionally Excel will have problems with formulas that should yield specific results. The
frequently effective solution is to insure that the formatting of the cells is not precluding the formula or
function action. For example, click into cell H38 of the Intermediate formulas data file. Once the cell is Page 104 Solving Accounting Principles Problems Using Excel for Windows
active, click on the Omega symbol (Σ) for quick sum and Excel looks over to column A and generates a
formula of =SUM(A38:G38) while you would have expected Excel to generate a value of 28
(1+2+3+4+5+6+7) the response is only 17. A check of the formula in cell H38 shows a correct formula =SUM(A38:G38), the sum is obviously wrong. Click into cell H39 and click the Omega symbol (Σ) for
quick sum and Excel looks over to column A and generates a formula of =SUM(A39:G39). And the
response is 28, the correct sum of 1+2+3+4+5+6+7. Now click into cell A38. Once the cell is active, left
mouse button down and begin to sweep right on row 38, when cells A38 and B38 are highlighted, the sum
at the bottom, right side of Excel shows “3”, the correct answer. As you sweep right along row 38 the sum
increases to 6, and then 10. When you sweep into the cell containing the value of 5, there is no increase,
there is no increase when you sweep into the cell with the value of 6, but sweeping into the cell with the
value of 7 results in a sum of 17 appearing at the bottom of the screen.
Time to troubleshoot and the focus has been identified. Click into cell D38. Then right click the cell
and read the formatting – it is “General.” Click into cell E38 and check its formatting – it is “Text.” In the
“Format Cells” dialog box Excel tells you that even though a numerical value may appear in this cell, it is
text and will not function as a number or value. The same for cell F38. Reformat the cells as “General” or
a numerically acceptable format – number, currency, or accounting, and then reenter your data and the
sum is correct – as shown in line 39. Excel had the numbers 5 and 6 entered after the cells were formatted
to text so even reformatting the cells to “General” does not change the data – text images of 5 and 6, to
numerical data until the cells are reformatted and the data is reentered.
Scrolling through the “All” formulas and functions category of Excel will reveal many useful
formulas and functions applicable to the exercises and problems of the textbook. However, Excel is a tool
and the results of every Excel formula and function needs to be evaluated for reasonableness. When
working on extensive and intricate data proof the concept with basic data and simple values. For example,
with the “DDB” function to handle doubledeclining depreciation of an asset, verify it with a $10,000
asset, $0.00 salvage value, 5year life at 200%. This should return a value of 40% of $10,000 (100% / 5
year life X 200%) or $4,000 for the first period. If another value is returned, something is wrong with the
formula selection, the data input, or formatting. In this formula, Excel is not expecting 200 for 200%, it is
expecting either “2” for twice straightline or “200%”. Excel also is not expecting commas to be entered
into the values.
Tip: While working on formulas and functions, you can insert a single text character in front of the
equal sign and the formula or function becomes a text string and will not process. This does not reformat
the cell and preclude later mathematical operations. This is a handy “escape” while troubleshooting
problems and situations that may arise during entry. Excel does not want you to leave the cell with a
formula or function it cannot handle. Excel may present you with proposed resolutions and trouble
screens. Just because Excel presented it, does not mean it is correct or acceptable. By inserting a character
in front of the equal sign this can be stopped. As a matter of presentation, if the character is a single or
double quote, Excel will not show that character in the display of the cell data. This makes the single or
double quote presentation neat and clean. You can see many of these inserts through the data files. Keystrokes & Shortcuts
There are many keystrokes that work in a standard Windows format regardless of the program. Some are
unique to the program. Here are some simple and useful keystrokes for Excel:
The keystrokes of CtrlHome (key / keystrokes not type in entry) will take you to cell A1 unless the upper
row(s) or left column(s) frozen through “Freeze Pane.” Then CtrlHome will take you to the upper, left
most cell not frozen. CtrlHome will not take you into frozen columns or rows.
The keystroke of Home will take you to column A of the row with the currently active cell. It will not
take you into frozen columns. Chapter 16, Page 105
Page up will change the screen displayed up one “pane” of cells. If the top row is row 128, page up
will make the bottom row on the new screen 127. If there is not enough rows available because of the
upper or lower limit of Excel, nothing will happen. It will not take you into frozen rows.
Page down will change the screen displayed down one “pane” of cells. If the bottom row is row 128,
page down will make the top row on the new screen 129. If there is not enough rows available because of
the upper or lower limit of Excel, nothing will happen.
Arrow keys will move you one cell up, right, down, or left for each striking, if held, they will scroll
you in the selected direction. If you have edited the data within a cell, the use of the arrow keys will not
let you leave the cell. “Tab” or “Enter” must be struck or you must click into another cell with the mouse.
CtrlA selects all cells on the worksheet.
CtrlB toggles the selected cell or range of cells into and out of bold text mode.
CtrlC copies the selected cell or range of cells.
CtrlD is fill down – starting with the source cell, highlight the range you want filled in and press and
release CtrlD for the fill to occur.
CtrlF brings up the “Find” and “Find and Replace” dialog box.
CtrlG brings up the “Go to” dialog box.
Ctrl H brings up the “Find” and “Find and Replace” dialog box.
CtrlI toggles the selected cell or range of cells into and out of italics text mode.
CtrlK brings up the “Hyper link” dialog box.
CtrlN opens a new worksheet.
CtrlO brings up the “Open Workbook” dialog box.
CtrlP brings up the “Print” dialog box.
CtrlR is fill right– starting with the source cell, highlight the range you want filled in and press and
release CtrlR for the fill to occur.
CtrlS saves the file if it has been saved before, it brings up the “Save” dialog box if this is the first
save operation.
CtrlX is cut the cell or range of cells.
CtrlZ is “Undo.”
Ctrl`  the accent character on usually to the left of the 1/! Key brings up the audit tool bar.
Ctrl1 (The number one) is format cell or cells.
F1 (The number 1) brings up the Help screen.
F5 is refresh.
F7 starts spellcheck.
F12 brings up the “Save As” dialog box. Linking Worksheets
Excel can utilize the “Look to” function as a quick link between worksheets within the same workbook
and link between worksheets in difference workbooks. This is different then a link or hyperlink which
provides the path which opens a target document. If you are working within the same workbook but
different worksheets within Excel simply enter the equal sign into the target cell, select the source
worksheet and click into the source cell and press and release the “Enter” key. This is demonstrated in the
“Linking” data file on the data disk. On the “Links” worksheet, the “=” (equal sign) was entered into cell
A1. Then the Source worksheet was selected and cell A1 was clicked into to make it the source cell and
“Enter” was struck. This returns you to the “Links” worksheet as the function is complete. Then cell A1
as selected and the formula within cell A1 was dragged down through cell A5. The dragging operation is
relational as explained under Absolute References so the cells A1 through A5 of the Links worksheet are
now “children” or “dependents” of the “parent” or “source” cells of the Source worksheet.
Notice the special format and sequence of the linking formula, =Source!A1. This can be typed in
manually if desired but it is quicker and less troublesome to let Excel write the formula. You can utilize Page 106 Solving Accounting Principles Problems Using Excel for Windows
these cells for data within other formulas and you can add functions and formulas to the linking formula.
In cells D4 through D7 additional math actions are added to the linking function.
Linking can also be accomplished through Copy then Paste Special > Paste Link. When done with
this method the references are created as Absolute References by Excel, the absolute reference function
can be inserted on the “Look to” method as well. This allows the “dependent” or “child” cell to be moved,
copied and pasted, or dragged to another location without losing its reference.
When Excel links between two workbooks the formula becomes slightly more complicated. The
formula “=[Book3]Sheet1!$A$1” inserted into a cell in a workbook other than Book3 would look to
Book3 and return the data entered in cell A1. In this formula, the parent and child workbooks are within
the same directory. On your data disk is a “Linking Source” data file, another file, “Linking Dependent”,
looks to this file to attain data. Ensure that both these files are in the same directory and open the
“Linking Dependent” workbook. Upon opening Excel will ask if you want to update the link. Select
“Update” and Excel will “read the “Linking Source” workbook for changes. It will not open the
“Linking Source” workbook and it does not need to open the workbook to read the linking data. Assume
that the child or dependent file is closed and the source or parent file, “Linking Source” is open and being
edited. The child or dependent will not be updated upon saving the source or parent file. However, when
the child or dependent file, “Linking Dependent”, is opened, Excel will ask if you want it updated and
check for changes if you do.
Tip: You have a big, dependent or child file that is critical and numerous individuals can make
changes to the parent or source file. Upon opening the file you can select not to update the file and, once
open, use File > Save As to save the file as a new name. If you desire you can Copy and Paste Special >
Values the entire worksheet to remove the links and turn all the formulas into hard values. Then reopen
the (real) child or dependent file, update it, and inspect it. If unacceptable changes have been made to the
parent or source data file you have the child saved in a previous, unaffected condition through this
technique.
Excel will allow you to link between directories as long as you can define the path or generate the
path through Windows movements. With the ability to link the worksheets and workbooks you can build
subsidiary ledgers containing accounts receivable and accounts payable data and then link it to the general
ledger of accounts receivable and accounts payable in another directory. The subsidiary ledgers would be
the source or parent with the general ledger being the child or dependent cells. You would not have to
have the general ledger open to make entries into the subsidiary ledgers. But when the dependent or child
workbook, the general ledger file is opened, Excel would ask if you would like to check for changes and
update the file. This capability is demonstrated with the data disk directories “Linked Source” and
“Linked Child.” The Linked Source directory contains a data file titled “Linking Source  Directory.xls”
which is looked to by the child or dependent file titled “Linking Dependent  Directory.xls” in the
“Linking Child” directory. The formula in cell A1 of the “Linking Dependent – Directory.xls” file is long
and specific to how you copied the files to your directory. If the links have difficulty in reestablishing
upon opening, you can reinstate them by opening both files, clicking into cell A1 of the “Linking
Dependent  Directory.xls” entering an equal sign, then clicking into cell A1 of the “Linking Source Directory.xls” data file and striking the Enter key. Now that the formula is in cell A1, remove the absolute
reference dollar signs from the cell reference of “$A$1” to make it a relational “A1” and drag it down 35
or more rows to bring in the populated cell range of the “Linking Source  Directory.xls” data file. The
links are reestablished.
Clue: The cell A1 to A1 relationship is not required. You can link any cell to any cell through this
process.
The “Linking” and “Look to” capabilities makes Excel a powerful tool for data management. There is
a danger created in this manner that the data being utilized to generate values on your worksheets and in Chapter 16, Page 107
your workbooks may not be easily reviewed or visible for review and audit. And, as a professional, you
are responsible for the work you provide, regardless of the validity of the source. Chapter 17 MICROSOFT WORD
Chapter Outline
Word File Storage
Word Upon Opening
Word Tables Word Table Formulas
Excel Tables Into Word Word File Storage
During an initial installation Word will bring in the defaults of the installation process for file storage and
document setting. If the installation is an upgrade installation the installation process will incorporate the
previous file storage and document settings. The location of Word files can be left at “Default” which
may be in the “My Documents and Settings” directory. You can determine the actual storage location of
your will be Word Upon Opening
Word is a complete word processing application and more. Upon opening Word, after registration is
complete, you will be presented with a blank page usually titled “Document 1” by default. The cursor will
be flashing in the upper left corner of the “sheet” waiting for text to be entered and, depending on the
default settings of the installation process or as established by prior users.
To enter text During an initial installation Word will bring in the defaults of the installation process
for file storage and document setting. If the installation is an upgrade installation the installation process
will incorporate the previous file storage and document settings. The location of Word files can be left at
“Default” which may be in the “My Documents and Settings” directory. You can determine the actual
storage location of your will be Word Tables
Word has the ability to generate basic tables on its own through the path Table > Insert. The table inserted
below was created with this function. While it does not have all of the features of Excel, it is a good tool
for the presentation of limited material or materials not found elsewhere and not necessarily needed later.
By following the path Table > Insert > Table and telling Word through the dialog box that you want a
table with 5 rows and 5 columns the result is: Section 2, Page 108 Chapter 17, Page 109 1/A B C D E 2 10 15 20 25 3 11 22 33 44 4 12 144 1728 20736 5 33 60.33 By default, Word will make these columns fill the width of the page. You can select the table by
clicking into the table and then moving your cursor to the upper left corner area. While moving your
cursor in that area, you will see a fourheaded arrow appear. Click on that arrow and you will have
selected the entire table. You can also sweep and highlight the table from one corner to another if desired.
When the table is selected, you will
activate the tab markers in the
ruler/measurement bars at the top and
side of the page. By moving these tab
Microsoft Word Table Tabs in the Ruler
markers with your cursor (select them
by left mouse click and hold the left
button down) you can change the column width and row height by dragging them. You can also change
row height and column width by selecting the row(s) or column(s) by highlighting them, then right
clicking on the highlighted cells and following the path to Table Properties and selecting the Row or
Column tabs and changing the values there.
The borders on the table can be formatted by highlighting the specific cell, cells, or the entire table
and using the Borders icon on the tool bar or right clicking and selecting Borders and Shading. The
borders of row 3 have been removed through this process and the double lines under row 5 were placed
there by the same process. Under this tab, the line weight and characteristics can be modified as desired to
include dashes, dots, and mixes of other symbols. Row 2 was modified in this manner.
The background and text coloring of the cells and their data are controlled as other formatting
features that are in Word. You can select the table, the row, the column, or the cell and invoke the colors
and features through the tool bar icons or by following the path Format > Font while the cells are
highlighted.
Once a table has been inserted you can select a row or column and follow the path Tables > Insert and
increase the number of rows or columns as needed by instructing Word to place the inserted rows or
columns into the existing table above, below, to the right, or to the left of the cursor placement. To insert
a specific number of rows or columns, select/highlight that number of rows or columns and then follow
the path Table > Insert and select rows or columns from the selection options and inform Word if you
want them above, below, to the left, or to the right of the highlighted cells. Rows and columns can be
deleted through the path Table > Delete after the row(s) or column(s) are highlighted.
Tip: The entire row or column need not be selected to insert or delete rows or columns, only cells
representing the number of rows or columns you desire to have Word insert or delete. However, the
technique / habit of highlight all of the cells reinforces what will be inserted or deleted before the action is
invoked. Page 110 Solving Accounting Principles Problems Using Excel for Windows Word Table Formulas
Microsoft Word tables have their own formulas under the path Table > Formulas. It is through this path
that you establish and modify formulas within Word tables. Formulas in Word are not “read” in a formula
or data entry window like Excel. Word table formulas are done similar to Excel formulas in that they use
cell references of columns and rows. The upper left most cell is cell A1, the cell to its right is B1, the cell
below cell A1 is A2 and so on. In a Word table though the formula to add the contents of cells B2, B3,
and B4 is “=SUM(b2,b3,b4)” and the formula to multiply the contents of cells C2, C3, and C4 is
=PRODUCT(d2,d3,d4). Notice that the operation is controlled by the leader – Sum or Product and that
the cell references are separated by commas, not attached to each other by math operatives such as the +
or * symbols. The following table contains the formula =SUM(b2,b3,b4) in cell B5 and it adds the rows 2
through 4 into cell B5, the formula =AVERAGE(c2,c3,c4) in cell C5 and it calculates the average of the
values within cells C2, C3, and C4 into cell C5, and the formula =PRODUCT(d2,d3,d4) in cell D5 which
multiplies the value in cell D2 by the value in cell D3 and the product of that operation by the value in
cell D4 and places the final product in cell D5.
A1
B1
C1
D1
A2
2
3
4
A3
3
4
5
A4
4
5
6
A5
9
4
120
A6
Sum
Average
Product
Word formulas are not “live” like Excel when manual calculation is not invoked. That is, if you had
access to this table and changed the value in cell B2 from 2 to 20, the value shown in cell B5 would not
change.
Note: It is frequently easier to create the table within Excel and then copy and paste it into Word. Excel Tables Into Word
While Excel may be a major tool for accountants, communication of information is the key to success.
Once an Excel worksheet is populated with data its presentation can be adjusted through hidden columns
and rows, paste special, and other tools such as “look to” formulas from a specific area of the worksheet
or a new worksheet to give clean presentation. However, this may not suit the complete requirement and
the constraints of Excel may hinder the presentation. Much of the work presented within this text was
accomplished by entering the data, such as an exercise or problem, into Excel, then highlighting the data,
copying it through the keystrokes CtrlC, clicking the copy icon on the tool bar, or following the path Edit
> Copy. Then the data is pasted into Microsoft Word by
selecting Word, placing the cursor into the appropriate
area, the target zone, and using the keystrokes CtrlV,
clicking the paste icon on the tool bar, or following the
path Edit > Paste. Once placed in this manner, the data is
“static” in nature. If the source within Excel changes, the
data within Word will retain its original value without update.
From this point, the data is normally contained within a table which can be formatted as many other
Word objects can be with borders, fill colors, fonts and sizing. To move this inserted object place your
cursor near the top of the table and a square with a four
headed arrow, as shown in the screen print will appear.
When it does, click on it and the table will be selected.
While the table is selected, it will highlight to emphasize
Positioning blocks or cubes
the fact that it is selected. By right clicking on the Chapter 17, Page 111
highlighted table, you will be presented with a popup menu which has formatting options on it. While it
is highlighted, you will see a new set of “blocks” or “cubes” appear in the ruler bar of Word. These blocks
or cubes, as shown in the screen print, are the margin tools for the table. You can justify the table by
clicking on the Word left, right, center, or fill justify buttons. You can also reposition and resize the table
by dragging these margin tools left or right with your mouse.
Additionally, Excel and Word have the ability to paste the data as “linked” information from Excel
into Word. With this feature, when the source data in Excel is updated, the Word data will be updated
upon the next opening of the file Word file. This feature makes your data dynamic. Word of caution,
while being a “really neat” feature, it may not be appropriate for all situations so its use needs to be
appropriate. To link the data in Word back to the source in Excel, copy the data as previously explained
from Excel, open Word and locate the target area within Word and follow the path Edit > Paste Special,
then select Paste Link from the left side of the popup dialog box and select Microsoft Excel Worksheet
Object in the “As” window. The data, as copied, is pasted into the document and linked back to its parent
or source.
Reading the information below it becomes immediately apparent that its presentation is unclear and
does not convey the complete data as words are chopped and lost.
40,
Shares
000 $1
pur par value
$50 p Amount
,000$12 co rate per
flig
If the cells in5Excel’s source data are reformatted to increase their width, the “trimming” problem is
resolved. But this may not be practical as resizing cells would affect the rest of the parent or source
worksheet presentation. This trimming is because the cells are shown as they are in Excel and numerous
empty cells were copied and pasted, that is, while all of the data has been entered into the cells, the data
exceeded the cell width of the column and the Word page and flowed into the areas of other columns. By
copying and pasting the link with a smaller range of cells, the presentation is:
40,000
shares purchased
$1
par value per share
$50,000
amount contributed to RAS, Inc for the shares
$125
rate per flight hour flown
Once pasted through the linking operation you can select the data in Word and add additional
formatting to it as mentioned earlier without it affecting the source data but that capability is limited. Fill,
borders, and font coloring from the tool bar are the easiest to invoke. This is done below.
40,
000 shares purchased
$1 $50
,000 par value per share
amount contributed to RAS, Inc for the shares $12
5 rate per flight hour flown The copied and linked blocks can be moved around once placed by selecting the matrix of linked cells
and then moving the tab markers on the index markers. Chapter 18 HEWLETTPACKARD HP12C
Chapter Outline
HewlettPackard HP12C Basics
Days Between Dates With The HP12C
Storing Values In The HP12C
Balloon Payments With The HP12C
Time Value Of Money With The HP12C
Present Value Of A $1 With The HP12C Present Value Of An Annuity Of $1 With The
HP12C
Future Value Of An Annuity Of $1 With The
HP12C
Other HP12C Functions HewlettPackard HP12C Basics
HewlettPackard produces the HP12C Programmable Financial Calculator, a calculator for the
professional accountant, financial analysts, and business users. This calculator uses “RPN” or “Reverse
Polish Notation” logic. With this logic as an operating system to add 2 and 2 together and get the result of
4, the keystrokes are:
“2” – The display shows “2”.
“Enter” – The display shows 2.0000. (If the display is set to 4 significant digits.)
“2” – The display shows “2”.
“Plus” – The display shows 4.000. (If the display is set to 4 significant digits.)
This is slightly different than the
“2” – The display shows “2”.
“+” – The display shows 2.0000. (If the display is set to 4 significant digits.)
“2” – The display shows “2”.
“=” – The display shows 4.000. (If the display is set to 4 significant digits.)
of calculators like the BA II+ of Texas Instruments. When mastered, the HP12C can greatly assist
you in your academic endeavors, accounting, financial, and business career. The HP12C has an extensive
manual and many features, only a few will be addressed here. The same basic features will be addressed
for the Texas Instrument BA II+ (Business Analyst II+) in other chapters.
Hint: There is no “=” (equal sign) on the HP12C calculator.
First, the HP12C default for significant digits displayed is four shown as “0.0000”. In most
academic, accounting, financial, and business situations four or five significant digits are appropriate.
And, when the requirement is less, the few extra digits displayed can easily be ignored. They will also be
hidden if the HP12C needs more display room for whole numbers. To set or change the number of
decimal places displayed in the HP12C’s display follow these steps:
1. Turn the HP12C on by pressing and releasing the black “ON” button on the lower left corner of
the key pad. This is a “toggle” switch, pressing it while the HP12C is on will turn the calculator
off. Section 2, Page 112 Chapter 18, Page 113
2. Press and release the yellow “F” (Function) key once. This key is just to the right of the “ON” on
the bottom row.
3. Press and release any of the numerical numbers from “0” through “9”. The display changes to the
requested display immediately. Any values current in the display are redisplayed in the new
format.
4. Now test the calculator, if you chose “4” as the significant digits, dividing 0.025 by 30 is actually
0.000833333….. This is done by the keystrokes “0.025”, “ENTER”, “30”, “÷” and the display
should show “0.0008” when the HP12C is asked for this calculation while set to four significant
digits. The trailing 3s round down and therefore, “off” the display. This complete value to some
significant number of digits is still held in memory, without rounding, for future calculations.
Multiply the display by 100,000 through the keystrokes “100000” and then “X” (Multiply) and
the results should be “83.3333” – demonstrating that the nondisplayed values are not lost.
5. Now divide 0.025 by 26 with the keystrokes “0.025”, “ENTER”, “26”, “÷” and the display
should show “0.0010” when the HP12C is asked for this calculation while set to four significant
digits. The HP12C should return “0.0010” while the actual value is 0.00096154… and it has
been rounded up to 0.0010. Multiply the display by 100,000 through the keystrokes “100000”
and then “X” (Multiply) and the result should be 96.1538 – again demonstrating that the nondisplayed values are not lost.
Second, the HP12C gives you a quick way to support the time value of money calculations in
numerous periodicities as well as annual statements and monthly interest and payments. No adjustments
or setting need be made. These will be discussed in detail later.
Tip: When using the time value of money function on the calculator develop the habit of entering
values in all “fields” from left to right or right to left even if that value is “0” (zero). This should preclude
any old values from entering the equation unexpectedly.
Enter the following data through the keystrokes – enter “60” in the “n” field of the time value of
money register of the HP12C by typing in “60” into the display and pressing and releasing the “n” key in
the upper left corner of the HP12C. Pressing the “ENTER” key is not required since you will not be
using it for a “keyboard” operation. Type in “6.9” and press and release the “ENTER” key, the type in
“12” followed by the “÷” (Division) key. The display should read “0.5750”. Press and release the “i”
(Interest) key to record this value as the period interest. Type in “35000” and press the “PV” (Present
value) key to enter the loan value into the present value field. Type in “0” and press and release the
“PMT” (Payment) field. As a matter of discipline, entering a value, even “0” in the fields or register from
left to right or from left to right will preclude an unintended value from entering the calculation. Type in
“0” and press and release the “FV” (Future value) field. Now press the “PMT” key and the HP12C will
calculate the payment for the loan as “691.3918”.
The HP12C will respect the presentation of cash flow and value flow within the time value of money
(TVM) calculations. If the “PV” (Present value or principle value) is entered as a positive number
indicating cash flows in or value flows in, the “PMT” (Payment) and / or “FV” (Future value” will be
negative indicating cash flows out or value flows out. If “PMT” (Payment) is entered as a positive value
indicating cash flows in or value flows in, then computed “PV” and / or “FV” (Future value) will be
negative indicating cash or value flows in the opposite direction  out. If you wanted true discipline, you
can enter the present value as positive since it was value inflow to you and cash outflow in payments will
be negative. However, if you were the used car salesman and wanted discipline, in the data enter, type in
“35000” and then press the “CHS” (Change sign) key in the fifth column of the first row. This will
convert a positive display to a negative value (or a negative value to a positive value). Rerun the
calculation simply by placing a “35000” (negative) into the present value field and recompute the Page 114 Solving Accounting Principles Problems Using Excel for Windows
payment. This time the HP12C should show “691.3918”, a positive value indicating cash flows or value
flows in since the present value is negative, representing cash flows or value flows out.
Annuities can be paid at the beginning or the end of the payment period To make this change there
are two buttons on the HP12C. The “Beginning” button labeled “BEG” in blue font on the slanted face of
the “7” and the “End” button labeled “END” in blue font on the slant face of the “8”. To change to
“Annuity Due” or “Beginning”, press and release the blue “g” key in the lower left corner of the HP12C
and then press the “BEG” (Beginning / 7) key since you are in the “Blue text options” after pressing and
releasing the blue “g” key. At this time the HP12C will display “BEGIN” in the center, bottom of its
display. To enter an “Ordinary Annuity” where payments are made at the end of the period, press and
release the blue “g” key in the lower left corner of the HP12C and then press the “END” (Ending / 8) key
since you are in the “Blue text options” after pressing and releasing the blue “g” key. At this time the HP12C display of “BEGIN” in the center, bottom of its display will disappear indicating that it is in the
default mode of “Ordinary Annuity.”
Clue: While the HP12C is in the “Annuity Due” mode – “BEGIN” indicating that the annuity
payment is due at the beginning of the period there will be displayed in the center, bottom of the display.
If the HP12C is in the “END” there will be no additional display items as this is the default mode. Days Between Dates With The HP12C
The HewlettPackard HP12C will calculate the days between dates. A very nice function for accounts
receivable and accounts payable aging as well as determining issues such as interest payments made or
received every 30 days. To determine the days between the invoice date of January 24, 2008 and the date
of March 15, 2008:
1. Turn the HP12C on by pressing and releasing the “ON” button on the bottom left corner of the
key pad.
2. Press the blue “g” key in the third column of the bottom row followed by the “M.DY” (“5” /
“M.DY”) key, in the eighth column of the second row from the top. This sets the HP12C to a
date format of “01.242008” for January 24, 2008. Your instruction manual has other date formats
available.
2. Type in the first date, January 24, 2008, as “01.242008” and press “Enter.”
3. Type in the second date of March 15, 2008 as “03.152008”.
4. Press and release the blue “g” key followed by the “▲DYS” (EEX / ▲DYS) in the sixth column
of the second row from the top. This key reads as “Delta in days” with “▲” being an engineering
symbol for “delta” or “Degree of change.” The “▲DYS” is in blue font on the slant face of the
key since you are in the “g” (blue) key board options. The HP12C should respond with “51”
days between these two dates.
You can also calculate the date a note, such as a 120day note, issued January 24, 2008 is due. To do
this:
1. Turn the HP12C on by pressing and releasing the “ON” button on the lower left corner of the
key pad.
2. Press the “g” (blue) key in the third column of the bottom row followed by the “M.DY” (“5” /
“M.DY”) key, in the eighth column of the second row from the top. This sets the HP12C to a
date format of “01.242008” for January 24, 2008. Your instruction manual has other date formats
available.
2. Type in January 24, 2008 as “01.242008” and press “Enter.”
3. Type in “120” into the HP12C. Do not hit the “ENTER” key.
4. Press the “g” (blue) key in the third column of the bottom row followed by the “DATE” (“CHS” /
“DATE”) key, in the sixth column of the top row. The HP12C should respond with “5,23,2008
5” indicating the note will be due on May 23, 2008, which is a Friday as indicated by the trailing
“5”. Chapter 18, Page 115
Tip: Entering the days between dates value as a negative will generate a valid response for a earlier
date. If you wanted a 180day note payable on June 30, 2008, enter “06.302008” and press “ENTER.”
Then enter “120” and press the “CHS” (change sign) key in the sixth column of the top row. The display
will show “120”. Press the blue “g” key followed by the “Date” key (“CHS” / “DATE”) again. The HP12C will respond with “3,02,2008 7” indicating that the note should be issued Sunday, March 2, 2008. Storing Values In The HP12C
A major asset of a professional calculator like the HP12C is the ability to store data. In the example you
are issuing 10 year bonds. The bonds carry a face interest rate of 8% while the market is 7%. Interest is
paid semiannually. The issue consists of 1,500 bonds with face values of $1,000 each. Your challenge is
to identify the present value of the interest payments, the present value of the bonds, and the present value
of the bond issue. The “Store” and “Recall” capability are major tools in this task.
First, determine the face value of the bonds – 1,500 bonds X $1,000 = $1,500,000 by typing in
“1500” and pressing and releasing the “Enter” key. Then type in “1000” and press and release the “X”
(multiply) key. The display will show “1,500,000”. Now use the keystrokes “STO” (Store) near the
bottom of the calculator in the fourth column, followed by pressing and releasing the “1” key. This will
store $1,500,000 into the Storage Register 1.
Now you will address the present value of the bonds.
While the $1,500,000 is still in the display, press and release the “FV” (Future value) key to record
this value into that field.
Type in “0” (zero) and press and release the “PMT” (Payment) button. It is recommended that you
develop a discipline of entering values in all the TVM (Time value of money) fields from left to right or
right to left to ensure previous values do not enter your current calculation. This field will remain at “0”
for this calculation.
Type in “0” (zero) and press and release the “PV” button. It is recommended that you develop a
discipline of entering values in all the TVM (Time value of money) fields from left to right or right to left
to ensure previous values do not enter your current calculation. This will be your “solve” value in a
couple of steps.
Type in the market interest rate as a whole number – “7” then press and release “ENTER” key, enter
“2” followed by the ÷ (division) key. The display will properly state the annual (stated) interest rate a
period of interest rate of 3 ½%. Press and release the “i” (Interest) key to enter the value into the interest
field.
Type in “10”, the life of the bonds in years, then press and release the “ENTER” key followed by “2”
and the “X” (Multiply) key in the right most column of the calculator. The display should read “20”. Press
and release the “n” (Period) key to enter this value into the “Number of periods” or “life” register.
Now press and release the “PV”(Present value) key in the third column of the top row. The HP12C
should respond with “753,848.8266”. This is the present value of the bonds.
Press and release the “STO” button in the fourth column of the bottom row followed by the “2”. You
have now stored that value into the second storage register.
To solve the present value of the interest payments, enter “0” (zero) into the HP12C and press and
release the “FV” (Future value) key.
Press and release the “RCL” (Recall) key, in the fourth column of the bottom row of the key pad,
followed by the “1” key. The value stored in the first memory register, “1,500,000” should appear in the
display. Page 116 Solving Accounting Principles Problems Using Excel for Windows
Press and release the “ENTER” key then enter “8” and press and release the “%” for the face interest
rate in the fifth column of the second row from the top. The display should show “120,000.0000”. Now
enter “0.5” indicating semiannual interest payments. Press and release the “X” (multiply) key on the
right side of the HP12C. The display should show the cash interest payments of “60,000.0000”.
Press and release the “STO” (Store) key and then the “3” key to place this value in the third storage
register.
The “60,000.0000” value should still be in the display. Press and release the “PMT” (Payment) key.
Press and release the “PV” (Present value) key and the HP12C should display “852,744.1981”, the
present value of the interest payments.
The “N” (Periods) and “I/Y” fields were entered in your first calculation for the present value of the
bonds and you have not changed them in this short period of time so reentry was not necessary.
Press and release the “STO” (Store) key followed by the “4” to store this value in the fourth memory
register.
The present value of the bonds is the sum of: “RCL” “2”, “ENTER”, and “753,848.8266” should
appear. Press and release the “RCL” (Recall) key followed by pressing and releasing the “4” key and the
display should read “852,744.1981”.
Press the “+” (Plus) sign to display the sum of these two numbers – “1,606,593.025”. Since your
display was set to four significant digits but the HP12C needed one of those places to display a whole
number it took it away. This value is the present value of the bonds.
While the “1,606,593.025” is in the display, press and release the “STO” (Store) button followed by
the “5” to store this value.
To determine the premium or discount, recall the value in memory register 1 by “RCL” “1” and ADD
these numbers as one is a positive value while the other is a negative value, the premium of “106,593.025” on this bond issue. Store this value in memory register by pressing “STO” followed by “6”.
Now, the bond issue is stored in “1”, the present value of the bonds is stored in “2”, the cash interest
payments are stored in “3”, the present value of the interest payments is stored in “4”, the present value of
the bond issue is stored in “5”, and the premium is stored in “6”.
The memory registers can be “overwritten” or “rewritten” by simply “STO” and the specific registry
number. All the memory registers can be cleared at one time by the keystrokes the yellow “f” key
followed by the “Clear – Reg” key which is in the fifth column and the third row for the top which has
white text of “CLX and orange/yellow text above it labeled “REG” which is under the “Gang title” of
“Clear.” The valid registers are from “0” (zero) through “9” (nine). Balloon Payments With The HP12C
The HewlettPackard HP12C (HP12C) respects cash and value inflows and outflows. If the present
value (PV) (or principle) entered is positive indicating cash inflows or value inflows to you, then the
payment (PMT) or future value (FV) will be an outflow or a negative value. However, for most uses you
realize that you are getting a loan or making a loan and enter the present value (PV) as positive to save
keystrokes and simply read “691.3918” as “$691.39” as your payment or cash outflow. However, assume
that in this loan example, you are buying a $35,000 vehicle, the loan term is 60 months, the interest is
6.9% annually, and you are going to make a balloon payment of $5,000 at the end of the loan with and
payments are made monthly. “N” or number of periods is (5 years X 12 months) “60”, “i” or interest is
“6.9 ENTER 12 ÷” or “0.5750”, “PV” or present value is “35000”, and “PMT” or payment is 0 as a
matter of discipline. Now cash flow representation is important. Since the balloon payment is cash flow
out and the principle is cash flow in, if the principle is positive, then the balloon payment or “FV” – future Chapter 18, Page 117
value, must be negative. If you had entered the 35000 as a negative number representing cash flow or
value flow out, then the balloon payment entered in “FV” must be positive. When “5000” is entered into
the HP12C you will need to press the “CHS” key in the sixth column of the top row of the key pad to
convert the positive value to negative “5000”. Now press and release the “FV” key to enter the negative
“5000” into that field. Now press “PMT” (Payment) the key in the fourth column of the top row. The HP12C should return 621.13716. Without the balloon payment your payment was $691.3918, with the
balloon payment your payment is only $621.3716. Had the balloon payment been put in as a positive
number matching the “PV” or present value field, the payment would be 761.4121 or $761.4121. Which
fails a basic logic evaluation of lower your payment level by the balloon payment. Time Value Of Money With The HP12C
The HewlettPackard HP12C (HP12C) utilizes the same field identities as the accounting and financial
professionals in the calculation of the time values of money. On the top row of keys of the HP12C from
left to right (column 1 through column 5) you are presented with:
“n” for the number of periods for the event. There is a way to turn years into months with the HP12C. In the loan example the term is 5 years. Enter “5” into the HP12C, then press the blue
“g” key followed by the “12X”, which is the “n” key in the upper left corner of the key pad.
By pressing the blue “g” key first you enter a secondary key pad which states “Take the input
value and multiply it by 12” when you press the “n” key. The display will show “60”, the
result of 5 years multiplied by 12 months per year. No additional settings or actions are
required to take advantage of this function. If you press “RCL” (Recall) in the bottom row of
the key pad followed by the “n” key you will see the value of “60”.
“i” is for interest for the event. The HP12C is expecting interest in whole values so 6.9% is
entered as “6.9”. If this is an annual interest rate and interest is compounded monthly you can
enter the “6.9” followed by blue “g” key followed by the “12÷”, which is the “i” key in the
second column of the upper row of the key pad. By pressing the blue “g” key first you enter a
secondary key pad which states “Take the input value and divide it by 12” when you press
the “i” key. The display will show “0.5750”, the result of 6.9(%) divided by 12 months per
year. No additional settings or actions are required to take advantage of this function.
“PV” is the present value field. For this example you will remain with the basic loan and conform
to discipline. Since the cash flow or value is in, it will be positive, so enter 35000 into the
HP12C and press and release the “PV” key in the third column of the top row.
“PMT” is the payment field. Since you are trying to solve for payment and want to ensure every
field’s value, enter “0” (zero) into the HP12C and press the “PMT” key.
“FV” is the future value field. For this example you will retain your balloon payment issue. Since
the “PV” or present or principle value is positive, this must be negative indicating that you
are making the payment, representing cash flows out, at the end of the loan. Enter 5000 into
the HP12C then press and release the “CHS” (Change sign) key in the sixth column of the
top row of the HP12C. The display changes to 5,000. Press and release the “FV” future
value key in the fifth column of the top row.
Now solve for “PMT” or payment by pressing the “PMT” (Payment) button in the fourth column of
the top row of the HP12C. The HP12C should respond with 621.3716 indicating our payment is
$621.3716. Page 118 Solving Accounting Principles Problems Using Excel for Windows Present Value Of A $1 With The HP12C
You need the present value of $1 with 15 periods at 15% interest and no payments, the fields would be:
n = 15
i = 15
PV = 0 as a matter of discipline to ensure the values in the HP12C.
PMT = 0 as a matter of discipline to ensure the values in the HP12C.
FV = 1
When “PV” (Present value) is pressed and released the HP12C should respond with 0.1229 which
can be checked with printed present value table for validity.
To find the present value of $150 with 15 periods at 15% interest and no payments, the fields would
be:
n = 15
i = 15
PV = 0 as a matter of discipline to ensure the values in the HP12C.
PMT = 0 as a matter of discipline to ensure the values in the HP12C.
FV = 150
When “PV” (Present value) is pressed and released the HP12C should respond with 18.4342 which
can be checked with printed present value table for validity by multiplying $150 X 0.1229 = $18.4350
and the difference is a rounding factor.
Future Value Of A $1 With The HP12C
You need the future value of $1 with 15 periods at 15% interest and no payments, the fields would be:
n = 15
i = 15
PV = 1
PMT = 0 as a matter of discipline to ensure the values in the HP12C.
FV = 0 as a matter of discipline to ensure the values in the HP12C.
When “FV” is pressed and released. The response should be 8.1371 which can be checked with
printed future value table for validity.
To find the future value of $150 with 15 periods at 15% interest and no payments, the fields would
be:
n = 15
i = 15
PV = 150
PMT = 0 as a matter of discipline to ensure the values in the HP12C.
FV =0 as a matter of discipline to ensure the values in the HP12C.
When “FV” (Future value) is pressed and released the HP12C response should be 1,220.5592 which
can be checked with printed future value table for validity by multiplying $150 X 8.1371 = $1,220.5650
and the difference is a rounding factor. Chapter 18, Page 119 Present Value Of An Annuity Of $1 With The HP12C
You need the present value of an annuity of $1 with 15 periods at 15% interest and no future value. The
payments are to be made at the end of the period. The fields would be:
n = 15
i = 15
PV = 0 as a matter of discipline to ensure the values in the HP12C.
PMT = 1
FV = 0 as a matter of discipline to ensure the values in the HP12C.
When “PV” is pressed and released the response should be 5.8474 which can be checked with
printed present value of an annuity table for validity.
To find the present value of an annuity of $150 with 15 periods at 15% interest and no future value.
The payments are to me made at the end of the period. The fields would be:
n = 15
i = 15
PV = 0 as a matter of discipline to ensure the values in the HP12C.
PMT = 150
FV = 0 as a matter of discipline to ensure the values in the HP12C.
When “PV” is pressed and released the HP12C should respond with 877.1055 which can be
checked with printed present value of an annuity table for validity by multiplying $150 X 5.8474 =
877.1100 and the difference is a rounding factor.
For the present value of an annuity due, change the HP12C into “BEGIN” or “Beginning” mode
(due) by utilizing the keystrokes:
1.
2. 3. Press and release the blue “g” key once. This key is in the third column of the bottom row.
Press and release the blue font “BEG” (7 / BEG) key, this text is shown below the “7” on the
slant face in the top row of the key pad, the seventh column. The blue text on the slant face of the
key is used since you are in the “blue” or “g” keyboard options.
The display should show “BEGIN in the bottom, center of the display. Recomputing the example as an annuity due by pressing the “PV” (Present value) key should result in
a value 1,008.6713.
To change to “ordinary annuity” or an annuity paying at the end of the period:
1.
2. 3. Press and release the blue “g” key once. This key is in the third column of the bottom row.
Press and release the blue font “END” (8 / END) key, this text is shown below the “8” on the
slant face in the top row of the key pad in the eighth column. The blue text on the slant face of
the key is used since you are in the “blue” or “g” keyboard options.
The display of “BEGIN in the bottom, center of the display will disappear as this is the default
mode. Future Value Of An Annuity Of $1 With The HP12C
You need the future value of an annuity of $1 with 15 periods at 15% interest and payments at the end of
the periods – ordinary annuity, the fields would be:
n = 15
i = 15
PV = 0 as a matter of discipline to ensure the values in the HP12C. Page 120 Solving Accounting Principles Problems Using Excel for Windows
PMT = 1
FV = 0 as a matter of discipline to ensure the values in the HP12C.
When “FV” (Future value) key in the fifth column of the top row is pressed and released the HP12C
response should be 47.5804 which can be checked with printed future value of an annuity table for
validity.
To find the future value of an annuity of $150 with 15 periods at 15% interest and payments made at
the end of the periods, the fields would be:
n = 15
i = 15
PV = 0 as a matter of discipline to ensure the values in the HP12C.
PMT = 150
FV = 0
Then press and release the “FV” (Future value) key. The response should be 7,137.0616 which can
be checked with printed future value of an annuity table for validity by multiplying $150 X 47.5804 =
7,137.0616 and the difference is a rounding factor.
For the present value of an annuity due, change the HP12C into “BEGIN” or “Beginning” mode
(due) by utilizing the keystrokes:
1.
2. Press and release the blue “g” key once. This key is in the third column of the bottom row.
Press and release the blue font “BEG” (7 / BEG) key, this text is shown below the “7” on the
slant face in the top row of the key pad, the seventh column. The blue text on the slant face of the
key is used since you are in the “blue” or “g” keyboard options. Recomputing the example as an annuity due by pressing and releasing the “FV” (Future value) key.
The HP12C should respond with a value of “8,207.6209”.
To change to “ordinary annuity” or an annuity paying at the end of the period:
1. Press and release the blue “g” key once. This key is in the third column of the bottom row.
2. Press and release the blue font “END” (8 / END) key, this text is shown below the “8” on the
slant face in the top row of the key pad in the eighth column. The blue text on the slant face of the
key is used since you are in the “blue” or “g” keyboard options.
3. The display of “BEGIN in the bottom, center of the display will disappear as this is the default
mode. Other HP12C Functions
There are other HP12C functions explained in the HewlettPackard HP12C manuals and documents.
Some of these include the determination of the present value of a bond issue through a single math
function, net present value, internal rate of return, and depreciation functions. Chapter 19 TEXAS INSTRUMENTS BA II+
Chapter Outline
Texas Instruments BA II+ Basics
Days Between Dates With The BA II+
Storing Values In The BA II+
Balloon Payments With The BA II+
Time Value Of Money With The BA II+
Present Value Of A $1 With The BA II+ Present Value Of An Annuity Of $1 With The
BA II+
Future Value Of An Annuity Of $1 With The
BA II+
Other BA II+ Functions Texas Instruments BA II+ Basics
The Texas Instruments TI BA II Plus Advanced Business Analyst Calculator is a powerful tool that, if
learned, can greatly assist you in your academic endeavors, accounting, financial, and business career.
The BA II+ has an extensive manual and many features, only a few will be addressed here.
First, the BA II+ default for significant digits displayed is two shown as “0.00”. In most academic,
accounting, financial, and business situations four or five significant digits are appropriate. And, when the
requirement is less, the few extra digits displayed can easily be ignored. They will also be hidden if the
BA II+ needs more display room for whole numbers. To set or change the number of decimal places
displayed in the BA II+’s display follow these steps:
1.
2.
3. 4.
5.
6.
7. 8. Turn the BA II+ on by pressing and releasing the “On/Off” button on the upper right corner of
the key pad.
Press and release the yellow “2nd” key once. This key is in the first column of the second row.
Press and release the “Format” key. This is in yellow text above the “.” (period) key in the center
of the bottom row. The yellow text above the key is used since you are in the “2nd” keyboard
option as selected in step 2.
Press and release the “4” key, if four significant digits are desired, or, press any number from 0
through 8.
Press and release the “Enter” key on the first row, second column.
Press “CE/C” on the lower left corner of the keyboard to clear the display and return to normal
operations.
Now test the calculator, if you chose “4” as the significant digits, dividing 0.025 by 30 is actually
0.000833333….. When the BA II+ is asked for this calculation while set to four significant digits
the response should be “0.0008” since the trailing 3s round down and therefore, “off” the display.
This value is still held in memory, without rounding, for future calculations. Multiply the display
by 100,000 and the result should be 83.3333 – demonstrating that the nondisplayed values are
not lost.
Now divide 0.025 by 26. The BA II+ should return “0.0010” while the actual value is
0.00096154 and it has been rounded up to “0.0010”. Multiply the display by 100,000 and the
result should be “96.1538” – again demonstrating that the nondisplayed values are not lost. Section 2, Page 121 Page 122 Solving Accounting Principles Problems Using Excel for Windows
Second, the BA II+ comes defaulted to compute the time value of money with monthly payments and
interest compounding – set for car dealers and real estate agents. In the academic, accounting, financial,
and business world, interest is frequently compounded in daily, monthly, quarterly, semiannual, and
annual terms. To insure that the BA II+ will handle most of these options without getting them confused it
is recommended that you set the payments and compounding periods (P/Y = Payments per year and C/Y
= Compounding periods per year) to “1” so that you can control the payment and compounding periods as
follows:
1.
2.
3. 4.
5.
6.
7. Turn the BA II+ on by pressing and releasing the “On/Off” button on the upper right corner of
the key pad.
Press and release the yellow “2nd” key once. This key is in the first column of the second row.
Press and release the “P/Y” key, this text is shown above the “I/Y” key on the third row in the
second column. The yellow text above the key is used since you are in the “2nd” keyboard
options as selected in step 2.
The display should show “P/Y=” and a value such as “12”. Enter “1” and then press the “Enter”
key.
Press the up arrow or down arrow on the first row in the third or fourth column.
Check the value of “C/Y” and insure that it is also “1”. If it is not, enter “1” and then press the
“Enter” key.
Press “Clear” to return to the normal operations mode of the calculator. As a test, you are going to determine the payment for a $35,000 vehicle, the loan term is 60 months,
the interest is 6.9% annually and payments are made monthly. The BA II+ time value of money keys are
the five keys in the third row labeled “N” for number of periods, “I/Y” for the interest rate, “PV” for the
present value, “PMT” for payments, and “FV” for future value.
Tip: When using the time value of money function on the calculator develop the habit of entering
values in all “fields” from left to right or right to left even if that value is “0” (zero). This should preclude
any old values from entering the equation unexpectedly.
For the example loan, enter 60 into the keypad and press and release the “N” key in the first column
of the third row. Then enter 6.9% by typing in 6.9 then press and release the division (÷) key and type in
12 followed by the equal sign. This provides the period interest rate (6.9 / 12) of 0.5750. Enter this into
the interest field by pressing the “I/Y” key in the second key in the third row.
Clue: “I/Y” converts the value input into a percentage so had you entered 6.9%/12 or 0.00575, the
final calculation will be off.
Type in the $35,000 as 35000 and press the “PV” key, the center key in the third row. Since the object
is to compute payment (PMT), type in “0” (zero) and press the “PMT” or payment key, the key in the
fourth column of the third row. This will preclude any residual value from carrying over. Since there is no
balloon payment on this loan, type in “0” (zero) and press the “FV” or future value key. Now press and
release the “CPT” (compute) in the upper left corner of the keyboard. Now press and release the “PMT”
or payment key. The BA II+ should calculate 691.3918 indicating that for only $691.39 a month you can
have that used, minimum option, SUV hidden on the back lot.
Clue: The asterisks appearing above the value of the payment indicates that this value is computed,
not inputted by you.
The payment is negative since the present value or principle was entered as a positive indicating a
cash or value in to you. If you wanted true discipline, you would enter the present value (in this example)
as positive number since it was value inflow to you and cash outflow in payments will be negative.
However, if you were the used car salesman and wanted discipline, in the data enter, type in 35000 and
then press the +/ key just inside the bottom right corner of the keyboard. This will convert a positive Chapter 19, Page 123
display to a negative value (or a negative value to a positive value). Rerun the calculation simply by
placing a 35000 (negative) into the present value field and recompute the payment. This time the BA II+
should show 691.3918, a positive value since the present value is negative and it indicates the payments
will be cash in flows or value inflows.
Annuities can be paid at the beginning or the end of the payment period. To make this change there is
a “Toggle” function – the same keystrokes once will place it in the opposite mode of what it was in. So if
you run this set of keystrokes from the default of “END”, the BA II+ will be switched to “BGN” or
“Beginning” for annuity due. If it was in “BGN” for annuity due, the process will switch it to an ordinary
annuity or one paid at the end of the period. The keystrokes for this toggle on the BA II+ are:
1.
2.
3. 4. 5.
6.
7. Turn the BA II+ on by pressing and releasing the On/Off button on the upper right corner of the
key pad.
Press and release the yellow “2nd” key once. This key is in the first column of the second row.
Press and release the “BGN” key, this text is shown above the “PMT” key on the third row in the
fourth column. The yellow text above the key is used since you are in the “2nd” keyboard options
as selected in step 2.
The display should show “END” or “BGN” to indicate which mode you are currently in. If this is
the mode you desire, press and release the “CE/C” key on the lower left corner of the key pad to
cancel the toggle process.
If you want to change from “End” to “Beginning” or from “Beginning”” to “End, press and
release the “2nd” key once, from this intermediate position.
Then press and release the “SET” key once. This key is in the second column of the first row.
The display will change from “END” to “BGN” or from “BGN” to “END.”
Press and release the “C/CE” key to return to the normal operations of the BA II+. Clue: While the BA II+ is in the “Annuity Due” mode – “BGN” indicating that the annuity payment
is due at the beginning of the period there will be displayed near the top and towards the right side of the
display. If the BA II+ is in the “END” there will be no additional display items as this is the default mode. Days Between Dates With The BA II+
The Texas Instrument BA II+ will calculate the days between dates. A very nice function for accounts
receivable and accounts payable aging as well as determining issues such as interest payments made or
received every 30 days. To determine the days between the invoice date of January 24, 2008 and the date
of March 15, 2008:
1.
2.
3. 4. 5. 6. Turn the BA II+ on by pressing and releasing the On/Off button on the upper right corner of the
key pad.
Press and release the yellow “2nd” key once. This key is in the first column of the second row.
Press and release the “DATE” key, this text is shown above the “1” key on the key pad. The
yellow text above the key is used since you are in the “2nd” keyboard options as selected in step
2.
The display should show “DT1=” for Date 1. To enter the date of January 24, 2008, enter
“01.2408” and press and release the “ENTER” in the second column of the first row. The display
should show “DT1= 1  24  2008”.
Use the up or down arrows in the third and fourth columns of row one to display the “DT2=”
screen. This screen may contain a date from previous work or a default date. Enter March 15,
2008 as “03.1508” and press and release the “ENTER” key. The display should show “DT2= 3 15 – 2008”.
Use the up and down arrows in the third and fourth columns of row one to display the “DBD=”
screen. This screen may show a value from a previous computation. Press the “CPT” button in Page 124 Solving Accounting Principles Problems Using Excel for Windows
the first column of the first row. The BA II+ should display “DBD= 51.000”, days between dates
is 51.
Hint: Each time a new date is entered the “CPT” process must be run again to generate the correct
number of days between dates.
You can also calculate the date a note, such as a 120day note, issued January 24, 2008 is due. To do
this:
1. Turn the BA II+ on by pressing and releasing the On/Off button on the upper right corner of the
key pad.
2. Press and release the yellow “2nd” key once. This key is in the first column of the second row.
3. Press and release the “DATE” key, this text is shown above the “1” key on the key pad. The
yellow text above the key is used since you are in the “2nd” keyboard options as selected in step
2.
4. The display should show “DT1=” for Date 1. To enter the date of January 24, 2008, enter
“01.2408” and press and release the “ENTER” in the second column of the first row. The display
should show “DT1= 1  24  2008”.
5. Use the up or down arrows in the third and fourth columns of row one to display the “DBD=”
screen. This screen may contain a value from previous work or a default value. Enter “120” and
then press and release the “ENTER” key. The display should show “DBD= 120”.
6. Use the up and down arrows in the third and fourth columns of row one to display the “DT2=”
screen. This screen may show a date from a previous computation or a default date. Press the
“CPT” button in the first column of the first row. The BA II+ should display “DT2= 5 – 23 –
2008”. The date a 120day note issued on January 24, 2008 would be due.
Tip: Entering the “DBD” (Days between dates) value as a negative will generate a valid response for
a earlier date. If you wanted a 180day note payable on June 30, 2008, enter “DT1” as “06.3008” and
“DBD” as “180” and then compute “DT2” to determine that the note should be issued January 02, 2008,
displayed as “Tue = 1022008”. Storing Values In The BA II+
A major asset of a professional calculator like the BA II+ is the ability to store data. In the example you
are issuing 10 year bonds. The bonds carry a face interest rate of 8% while the market is 7%. Interest is
paid semiannually. The issue consists of 1,500 bonds with face values of $1,000 each. Your challenge is
to identify the present value of the interest payments, the present value of the bonds, and the present value
of the bond issue. The “Store” and “Recall” capability are major tools in this task.
First, determine the face value of the bonds – 1,500 bonds X $1,000 = $1,500,000 and use the
keystrokes “STO” (Store) near the bottom of the calculator in the first column, followed by pressing and
releasing the “1” key. This will store $1,500,000 into the Storage Register 1.
Now you will address the present value of the bonds.
While the $1,500,000 is still in the display, press and release the “FV” (Future value) key to record
this value into that field.
Type in “0” (zero) and press and release the “PMT” (Payment) button. It is recommended that you
develop a discipline of entering values in all the TVM (Time value of money) fields from left to right or
right to left to ensure previous values do not enter your current calculation. This field will remain at “0”
for this calculation.
Type in “0” (zero) and press and release the “PV” button. It is recommended that you develop a
discipline of entering values in all the TVM (Time value of money) fields from left to right or right to left
to ensure previous values do not enter your current calculation. This will be your solve value in a couple
of steps. Chapter 19, Page 125
Type in the market interest rate as a whole number – “7” then press and release the ÷ (division) key,
enter “2” and press and release the equal sign to properly state the annual (stated) interest rate a period of
interest rate of 3 ½%. Press and release the “I/Y” (Interest) key to enter the value into the interest field.
Type in “10”, the life of the bonds in years, then press and release the “X” (Multiply) key in the right
most column of the calculator followed “2”, the number of interest periods per year by the “=” (Equal)
sign. The display should read “20”. Press and release the “N” (Period) key to enter this value into the
“Number of periods” or “life” register.
Now press and release the “CPT” (Compute) key in the upper, left corner of the calculator followed
by the “PV”(Present value) key in the center of the TVM row. The BA II+ should respond with “PV= 753,848.8266”. This is the present value of the bonds.
Press and release the “STO” button in the first column near the left, bottom corner followed by the
“2”. You have now stored that value into the second storage register.
To solve the present value of the interest payments, enter “0” (zero) into the BA II+ and press and
release the “FV” (Future value) key.
Press and release the “RCL” (Recall) key, near the left bottom corner of the key pad, followed by the
“1” key. The value stored in the first memory register, “1,500,000” should appear in the display.
Press and release the “X” (Multiply) key followed by the “(“ (Open parenthesis) then press and
release the “8”, the face interest rate, press and release the “%” (Percentage) key, followed by the ÷
(Division) followed by “2” and then the “)” parenthesis. The display will show “0.04”. This is the period
interest rate of the face or stated interest rate (8% / 2). Since you already have “1,500,000” and “X”
hovering in cyberspace from the earlier step, press “=” (Equal) and the BA II+ should respond with
60,000.00. This is your period interest payment in cash.
Press and release the “STO” (Store) key and then the “3” key to place this value in the third storage
register.
The $60,000.0000 value should still be in the display. Press and release the “PMT” (Payment) key.
Press and release the “CPT” key followed by the “PV” (Present value) key and the BA II+ should
display “PV= 852,744.1981”, the present value of the interest payments.
The “N” (Periods) and “I/Y” fields were entered in your first calculation for the present value of the
bonds and you have not changed them in this short period of time so reentry was not necessary.
Press and release the “STO” (Store) key followed by the “4” to store this value in the fourth memory
register.
The present value of the bonds is the sum of: “CE/C” to clear the display and working register to
preclude bring in an unintended value, “RCL” “2”, and “753,848.8266” should appear. Press and release
the “+” (Plus) key followed by “RCL” (Recall) and then the “4” key and the display should read “852,744.1981”.
Press the “=” (Equal) sign to display the sum of these two numbers – “1,606,593.025”. Since your
display was set to four significant digits but the BA II+ needed one of those places to display a whole
number it took it. This value is the present value of the bonds.
While the “1,606,593.025” is in the display, press and release the “STO” (Store) button followed by
the “5” to store this value.
To determine the discount, recall the value in memory register 1 by “RCL” “1” and ADD these
numbers as one is a positive value while the other is a negative value, the premium of “$106,593.0248”
on this bond issue. Store this value in memory register by pressing “STO” followed by “6”. Page 126 Solving Accounting Principles Problems Using Excel for Windows
Now, the bond issue is stored in “1”, the present value of the bonds is stored in “2”, the cash interest
payments are stored in “3”, the present value of the interest payments is stored in “4”, the present value of
the bond issue is stored in “5”, and the premium is stored in “6”.
The memory registers can be “overwritten” or “rewritten” by simply “STO” and the specific registry
number. All the memory registers can be cleared at one time by the keystrokes “2ND” then “MEM” (the
text above the “0” key), the “2ND” again followed by the “CE/C” key. The valid registers are from “0”
(zero) through “9” (nine). Balloon Payments With The BA II+
The Texas Instruments BA II Plus (BA II+) respects cash and value inflows and outflows. If the present
value (PV) (or principle) entered is positive indicating cash inflows or value inflows to you, then the
payment (PMT) or future value (FV) will be an outflow or a negative value. However, for most uses you
realize that you are getting a loan or making a loan and enter the present value (PV) as positive to save
keystrokes and simply read “691.3918” as “$691.39” as your payment or cash outflow. However, assume
that in this loan example, you are buying a $35,000 vehicle, the loan term is 60 months, the interest is
6.9% annually, and you are going to make a balloon payment of $5,000 at the end of the loan with and
payments are made monthly. “N” or number of periods is (5 years X 12 months) 60, “I/Y” or interest is
6.9 /12 months or 0.5750, “PV” or present value is 35000, and “PMT” or payment is 0 as a matter of
discipline. Now cash flow representation is important. Since the balloon payment is cash flow out and the
principle is cash flow in, if the principle is positive, then the balloon payment or “FV” – future value,
must be negative. If you had entered the 35000 as a negative number representing cash flow or value flow
out, then the balloon payment entered in “FV” must be positive. When 5000 is entered into the BA II+
you will need to press the “+/” key near the bottom right corner of the key pad to convert the positive
value to negative 5000. Now press and release the “FV” key to enter the negative 5000 into that field.
Now press “CPT” the key in the first column of the first row and then the “PMT” or payment key. The
BA II+ should return 621.13716. Without the balloon payment your payment was $691.3918, with the
balloon payment your payment is only $621.3716. Had the balloon payment been put in as a positive
number matching the “PV” or present value field, the payment would be 761.4121 or $761.4121. Which
fails a basic logic evaluation of lower your payment level by the balloon payment. Time Value Of Money With The BA II+
The Texas Instrument BA II Plus (BA II+) utilizes the same field identities as the accounting and
financial professionals in the calculation of the time values of money. On the third row of keys of the BA
II+ from left to right (column 1 through column 5) you are presented with:
“N” for the number of periods for the event. If you set your BA II+ up as earlier discussed with “1” in
the “P/Y” and “C/Y” parameters, this value is easy to determine. If the loan is for 5 years and payments as
well as interest is compounded monthly, enter the value of 5 into the BA II+ then press the “X” (times)
key in the right column of the BA II+, then press “12” followed by the “=” (equal sign). The display will
now indicate “60.000” representing the periods of the loan, in this example, months. Now press and
release the “N” or number of periods key in the first column of the second row.
“I/Y” is interest per year and, if the BA II+ has been set up as previously discussed, this is now
interest per period. Since it is interest per period and the interest rate is provided as annually, enter the
interest rate of 6.9 and DO NOT PRESS THE “%” KEY.. Now divide this value by the same factor as
you multiplied the term by – 12. So press and release the “÷” (divide) key followed by the “=” (equal
sign). The display should show 0.5750. Press and release the “I/Y” key to enter the value into the “I” or
interest field. Chapter 19, Page 127
“PV” is the present value field. For this example you will remain with the basic loan and conform to
discipline. Since the cash flow or value is in, it will be positive, so enter 35000 into the BA II+ and press
and release the “PV” key in the third column of row three.
“PMT” is the payment field. Since you are trying to solve for payment and want to ensure every
field’s value, enter “0” (zero) into the BA II+ and press the “PMT” key.
“FV” is the future value field. For this example you will retain your balloon payment issue. Since the
“PV” or present or principle value is positive, this must be negative indicating that you are making the
payment, representing cash flows out, at the end of the loan. Enter 5000 into the BA II+ then press and
release the “+/“ key near the bottom right corner of the BA II+. The display changes to 5,000. Press and
release the “FV” future value key in the fifth column of the third row.
Now solve for “PMT” or payment by pressing the “CPT” (Compute) button in the upper left corner of
the BA II+ followed by pressing and releasing the “PMT” (payment) key and the BA II+ should respond
with 621.3716 indicating our payment is $621.3716. Present Value Of A $1 With The BA II+
You need the present value of $1 with 15 periods at 15% interest and no payments, the fields would be:
N = 15
I/Y = 15
PV = 0 as a matter of discipline to ensure the values in the BA II+.
PMT = 0 as a matter of discipline to ensure the values in the BA II+.
FV = 1
Then “CPT” and “PV.” The response should be 0.1229 which can be checked with printed present
value table for validity.
To find the present value of $150 with 15 periods at 15% interest and no payments, the fields would
be:
N = 15
I/Y = 15
PV = 0 as a matter of discipline to ensure the values in the BA II+.
PMT = 0 as a matter of discipline to ensure the values in the BA II+.
FV = 150
Then “CPT” and “PV.” The response should be 18.4342 which can be checked with printed present
value table for validity by multiplying $150 X 0.1229 = $18.4350 and the difference is a
rounding factor.
Future Value Of A $1 With The BA II+
You need the future value of $1 with 15 periods at 15% interest and no payments, the fields would be:
N = 15
I/Y = 15
PV = 1
PMT = 0 as a matter of discipline to ensure the values in the BA II+.
FV = 0 as a matter of discipline to ensure the values in the BA II+.
Then “CPT” and “FV.” The response should be 8.1371 which can be checked with printed future
value table for validity.
To find the future value of $150 with 15 periods at 15% interest and no payments, the fields would be:
N = 15
I/Y = 15
PV = 150
PMT = 0 as a matter of discipline to ensure the values in the BA II+.
FV =0 as a matter of discipline to ensure the values in the BA II+. Page 128 Solving Accounting Principles Problems Using Excel for Windows
Then “CPT” and “FV.” The response should be 1,220.5592 which can be checked with printed
future value table for validity by multiplying $150 X 8.1371 = $1,220.5650 and the difference is a
rounding factor. Present Value Of An Annuity of $1 With The BA II+
You need the present value of an annuity of $1 with 15 periods at 15% interest and no future value. The
payments are to be made at the end of the period. The fields would be:
N = 15
I/Y = 15
PV = 0 as a matter of discipline to ensure the values in the BA II+.
PMT = 1
FV = 0 as a matter of discipline to ensure the values in the BA II+.
Then “CPT” and “PV.” The response should be 5.8474 which can be checked with printed present
value of an annuity table for validity.
To find the present value of an annuity of $150 with 15 periods at 15% interest and no future value.
The payments are to me made at the end of the period. The fields would be:
N = 15
I/Y = 15
PV = 0 as a matter of discipline to ensure the values in the BA II+.
PMT = 150
FV = 0 as a matter of discipline to ensure the values in the BA II+.
Then “CPT” and “PV.” The response should be 877.1055 which can be checked with printed present
value of an annuity table for validity by multiplying $150 X 5.8474 = 877.1100 and the difference is a
rounding factor.
For the present value of an annuity due, change the BA II+ into “BGN” or “Beginning” mode (due)
by utilizing the keystrokes:
1. Press and release the yellow “2nd” key once. This key is in the first column of the second row.
2. Press and release the “BGN” key, this text is shown above the “PMT” key on the third row in the
fourth column. The yellow text above the key is used since you are in the “2nd” keyboard options
as selected in step 2.
3. The display should show “END” or “BGN” to indicate which mode you are currently in. If this is
the mode you desire, press and release the “CE/C” key on the lower left corner of the key pad to
cancel the toggle process.
4. If you want to change from “End” to “Beginning”, press and release the “2nd” key once, from this
intermediate position.
5. Then press and release the “SET” key once. This key is in the second column of the first row. The
display will change from “END” to “BGN.”
6. Press and release the “C/CE” key to return to the normal operations of the BA II+.
7. You will have a “BGN” text string in the upper right area of the display while in this mode.
Recomputing the example as an annuity due should result in a value 1,008.6713.
The steps of 1 through 6 are “toggles.” Accomplishing them again should result in a “BGN” in step 2
and “END” in step 5. Chapter 19, Page 129 Future Value Of An Annuity Of $1 With The BA II+
You need the future value of an annuity of $1 with 15 periods at 15% interest and payments made at the
end of the periods – ordinary annuity, the fields would be:
N = 15
I/Y = 15
PV = 0 as a matter of discipline to ensure the values in the BA II+.
PMT = 1
FV = 0
Then “CPT” and “FV.” The response should be 47.5804 which can be checked with printed future
value of an annuity table for validity.
To find the future value of an annuity of $150 with 15 periods at 15% interest and payments are made
at the end of the periods, the fields would be:
N = 15
I/Y = 15
PV = 0 as a matter of discipline to ensure the values in the BA II+.
PMT = 150
FV = 0
Then “CPT” and “FV.” The response should be 7,137.0616 which can be checked with printed
future value of an annuity table for validity by multiplying $150 X 47.5804 = 7,137.0600 and the
difference is a rounding factor.
For the future value of an annuity due, change the BA II+ into “BGN” or “Beginning” mode (due) by
utilizing the keystrokes:
1. Press and release the yellow “2nd” key once. This key is in the first column of the second row.
2. Press and release the “BGN” key, this text is shown above the “PMT” key on the third row in the
fourth column. The yellow text above the key is used since you are in the “2nd” keyboard options
as selected in step 2.
3. The display should show “END” or “BGN” to indicate which mode you are currently in. If this is
the mode you desire, press and release the “CE/C” key on the lower left corner of the key pad to
cancel the toggle process.
4. If you want to change from “End” to “Beginning”, press and release the “2nd” key once, from this
intermediate position.
5. Then press and release the “SET” key once. This key is in the second column of the first row. The
display will change from “END” to “BGN.”
6. Press and release the “C/CE” key to return to the normal operations of the BA II+.
7. You will have a “BGN” text string in the upper right area of the display while in this mode.
Recomputing the example as an annuity due should result in a value 8,207.6209.
The steps of 1 through 6 are “toggles.” Accomplishing them again should result in a “BGN” in step 2
and “END” in step 5. Other BA II+ Functions
There are other BA II+ functions explained in the Texas Instrument BA II Plus manuals and documents.
Some of these include the determination of the present value of a bond issue through a single math
function, net present value, internal rate of return, depreciation functions, and breakeven points. ...
View
Full
Document
This note was uploaded on 09/19/2010 for the course ACCT 220 taught by Professor Ullmann during the Fall '10 term at University of Nebraska Kearney.
 Fall '10
 Ullmann
 Accounting

Click to edit the document details