ch13_15 - Solving Accounting Principles Problems Using...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

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 Ctrl-C, 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 pop-up 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 pop-up menu or follow the path Edit > Paste Special to get the same pop-up menu. From the pop-up 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” (non-formula 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 Straight-Line Depreciation Declining-Balance Depreciation Variable Declining Balance Depreciation Sum-Of-Year’s-Digits Depreciation Units Of Activity Depreciation Asset Acquisition Sheet The acquisition of a plant, property, equipment, intangible, or natural resource asset is seldom a simple one-page 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. Straight-Line Depreciation The straight-line 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 Straight-Line worksheet of the “Depreciation” data file. In the “Depreciation” data file the “Straight-Line” 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 straight-line 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 straight-line depreciation is simple math the formula can be manually entered as =(2400-300)/120. Both will result in approximately $17.50 per month. The way that the Straight-Line 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$2-C12), 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 straight-line depreciation utilize the same concepts for presentation. Declining-Balance 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 double-declining 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 straight-line 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 “Straight-Line 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 double-declining depreciation by “Pencil and paper” without salvage value and no adjustment. If the adjustment is held until the last period of a 5-year 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 straight-line 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 straight-line when straight-line 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 straight-line 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 straight-line 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 “Straight-Line 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 straight-line 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 straight-line 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. Sum-Of-Year’s-Digits Depreciation Excel handles Sum-of-year’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 sum-of-year’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. Units-Of-Activity 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, semi-annually, 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 10-year 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 straight-line 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 straight-line 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 semi-annual 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 semi-annual 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 straight-line 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.

Ask a homework question - tutors are online