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 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. ...
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