ch16_19 - 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 16 ADVANCED EXCEL Chapter Outline Average & AverageA Command Access Convert Embedded Formulas Intermediate Formulas Keystrokes & SHortcuts Linking Worksheets Macros Average & AverageA Within Excel there are two “Average” functions under the “Statistical” category. The first is “Average” and returns a simple average of an identified range. This formula is shown in operation on the “Average” data file on the data disk. The function written in cell E2 determines the average number of the category “Apple” – cell B2. Cell E3 determines the average number of “Apples”, a range from cell B3 through B11. The formula in cell E4 determines the average number of “Berries”, a range from cell B12 through B23 while the formula in cell E5 determines the average number of “Cherries” in a range from cell B24 through 33. This formula is simply written as “=AVERAGE(Range).” The range can be a continuous string of cells in a column or row or can be a range of cells such as columns and rows such as the formula shown in the range of F10 through M31. Average will not count text strings into its averages. The second formula is “AverageA”. With this formula you can even account for text strings in your average such as “True” – a value of 1, “False” – a value of 0, while the occurrences of text strings such as “N/A” get counted in the denominator of the average equitation but there is no value for them in the numerator of the equitation. This is a handy tool for the average number of correct answers on a “True/False” question as shown in column O of the “AverageA” worksheet. Command Access Excel has numerous command paths to accomplish almost every one of its many commands. The selection and utilization is up to you. If you are a proficient typist, you may select keyboard commands while typing and mouse commands while doing graphic tasks. Others will always select the mouse while some will use the “Alt” (Alternate) key path. The most common method of accessing Microsoft Excel commands is through the mouse. With this concept, you would select a word on the menu bar by placing the mouse cursor over it and clicking on it. A drop-down menu attached to the menu bar would appear with the options available at that point in time and available in relation to that position on the worksheet. If an item is grayed out or faded, it is not available for some reason. You would then select the desired option by clicking on it with the mouse. If the option has an arrowhead pointing to the right or left, that selection has other choices available to it and selecting it or placing the cursor over that selection will result in an expanded display for that selection. Once shown, clicking on the desired option will result in its selection. If no selection is found, or you decide you are in the wrong path, clicking anywhere other than on the drop-down menu will back you out Section 2, Page 99 Page 100 Solving Accounting Principles Problems Using Excel for Windows without a selection being activated. You may also press and release the “Esc” (Escape) key to cancel the menu and avoid a selection. The second methodology of selecting commands it to use keyboard commands. With the use of a predecessor key such as the “Ctrl” (Control) key or the “Alt” (Alternate) key on the keyboard other options become available with additional keystrokes. For example, to copy something using keyboard commands, activate the cell or cells you would like to copy and then press and hold the Ctrl (Control) key down and then press and release the “C” key. This series of keystrokes is shown throughout the text as “Ctrl-C”. The shift key is not hit and it does not matter if the keyboard is locked into upper or lower case at the time. Once the “C” key is struck, the keys can be released in any order. An alternative is to use the “Alt” (Alternate) key to access the menu bar. By striking this key you will see the first item on the menu bar become active. By using the arrow keys to move left and right as well as up and down, you can prowl the menu bar for your selection. When your selection is located, striking the “Enter” key will select and activate the command you have chosen. Regardless of the method you have used to access the command structure of Excel, the “Esc” (Escape) key will usually back you out of the command path without making a selection. It may take one or more strikes of the “Esc” key to accomplish the back-out if you are several “layers” into a selection process. Convert The “Convert” function will accept one value and then convert that value from one measuring standard to another measuring standard. Tired of trying to remember that 2.2 pounds is roughly 1 kilogram and have to convert 825 pounds to kilograms for an shipping invoice? Try the “=SUM(CONVERT(825, "lbm","g")/1000”. One of the requirements for this function to work is that the “Unit Id” must be in double quotation marks and in the proper case – some upper case, some lower case. For example, while Excel ignores “=CONVERT(”, “=Convert(”, “=convert(” upper or lower case issues, it will “fault out” if the formula is written as “=CONVERT(825,“LBM”,g)” since it will not recognize the unit ID of upper case “LBM” because of the upper case issue. Nor will it recognize the lower “g” without the double quotation marks. Utilize the “Help” screen for “Convert Worksheet Function” from Microsoft Excel for more units and information. This table is a brief listing of the values that Excel can convert from and into: Unit Id: Weight: "g" "lbm" "ozm" Distance: "ft" "in" "m" "mi" "Nmi" "yd" Time: "day" Unit Title: Gram Pound mass (avoirdupois) Ounce mass (avoirdupois) Foot Inch Meter Statute mile Nautical mile Yard Day Unit Id: Temperature: "C" "F" Unit Title: Degree Celsius Degree Fahrenheit "K" Degree Kelvin Liquid Measurement: "cup" Cup "gal" Gallon "l" Liter "oz" Fluid ounce "pt" U.S. pint "qt" Quart "tbs" Tablespoon "tsp" Teaspoon Chapter 16, Page 101 "hr" "mn" "sec" "yr" Hour Minute Second Year On the “Convert” data file there is a data matrix of common convert terms or “Unit Ids.” The values within column B cannot be utilized as “Look to” values since they are in quotation marks, the way you are required to type them into Excel. However, the column is duplicated in column C without quotation marks. This allows you to write a formula such as “=CONVERT(B1,C11,C10)” in cell C1 to convert the value of 825 contained in cell B1 from pounds as identified through the value contained in cell C11, to grams through the value contained in cell C10. Had you utilized the column B values of the “Convert” data file for these references you would get a fault indication such as “#N/A” or “#Name”. By looking at the dialog box you would see that Excel is interpreting the formula as “=CONVERT(B1, ““B12””, ““B10””)” which contains too many double quotation marks around the values – so the double quotes are removed from column C for your ease of use. Embedded Formulas As addressed elsewhere in this book, the prefix of “=SUM(” starts Excel into the “I have to do something with formulas and functions” mode. As shown elsewhere in this book, Excel will perform functions and formulas within other functions and formulas. This embedding capability generates a power and flexibility within Excel that greatly aids the professional. The way to embed a formula or function is relatively simple. First and foremost, understand the functions and formulas you are utilizing. For example, if you are using the “Count If” function on an inventory sheet to find the total number of apples, your results will be incorrect. This is shown on the Embedded Formulas data file where the desire was to sum the number of apples, berries, and cakes but the “Count If” formula resulting in finding out how many times the text apples, berries, and cakes each occurred in the data field. In the Embedded Formula data file the first task is establish a value for inventory by finding the value of the apples, the berries, and the cakes and then summing those subtotal values. In cells G23 through G25 and G27 Excel finds the number of each item. The values for these items as individual elements is contained in cells H23 through H25 and H27. In cells I25 through I25 and I27 the “Sum If” formula determines the sum total of each element and then Excel multiplies it by the element value contained in the H cell ranges. For the apples on row 23 the formula is: =SUMIF($A$2:$A$250,F23,$B$2:$B$250)*H23 This embedded formula is the “Sum If” formula and a multiplication function by adding the “*H23” at the end of the previously discussed “Sum If” formula. By using the “Look To” capability you can change the element value of the inventory items and see the results immediately. Suppose that we wanted to be alerted if the value of any inventory category exceeded $20,000. This can be accomplished through conditional formatting – covered elsewhere, so utilize “Concatenate”, a function discussed shortly, and “IF” statements. The formula in I23, broken down into three lines due to Page 102 Solving Accounting Principles Problems Using Excel for Windows its length, reads like this: =IF((SUMIF($A$2:$A$250,F33,$B$2:$B$250)*H33) > $I$31, CONCATENATE("$",FIXED(SUMIF($A$2:$A$250,F33,$B$2:$B$250)*H33,4,0), " Exceeds target value"),SUMIF ($A$2:$A$250,F33,$B$2:$B$250) *H33) To understand this function appreciate that Excel looks at the deepest pair of parentheses and then starts working its way outward. In this case those parenthesis are contained within the “Sum If” function. So in line 1 of the formula Excel sums the number of directed inventory items, those identified in cell F33 – Apples, and multiplies the summed number by the value in cell H33 - $0.12 per element item as supplied on the data disk. Then Excel is instructed to compare this total value to the value in cell I31 and determine if the summed value is greater than the value in cell I31. If the summed value is greater than the value in cell I31, $20,000, as supplied on the data disk, Excel moves to the set of parenthesis inside the formula – those of the “If” statement. Since the statement is false (9,674 apples at $0.12 each total $1,160.8800) Excel executes the function contained in the second part of the “If” statement. This is actually a repeat of the “Sum If” formula summing the number of referenced inventory items and multiplying that sum by the element cost contained in cell H33. However, in cell I35 the statement is true. The 15,771 cakes at $1.8700 each total to $29,491.7700 which exceeds the reference value in cell I31. Since this “If” statement is true, Excel executes the first half of the “If” statement – the concatenate function. This function is set to sum the total number of referenced inventory items – cakes, and multiply that sum by the cost in cell H35. Then Excel is told to format this value to 4 decimal places and with commas through the “Fixed” function. Concatenate adds a dollar sign as text in front of the total value for a presentation of “$29,491.7700”. The concatenate function adds spaces and a text string saying that this value exceeds the limit. While not real useful in life, as will be shown in the Intermediate Formulas section to follow, this string of formulas and functions demonstrates the power Excel can provide if the user has imagination and knowledge. Notice that the only equal sign in the formula is the one at the beginning of the long formula string. An equal sign buried within the formula such as in front the “Sum If” or “Concatenate” formulas and functions will result in an error message from Excel. You can only nest 7 sets of parenthesis within Excel. However, as shown in the section on Intermediate Formulas, this is only a restriction within a cell, not within Excel per say and can be easily compensated for. The actual solution to this problem should have been conditional formatting of the cell or an “If” statement as shown in cells F40 through J45 where both “If” statements and Conditional Formatting are used on the values resulting in easier to write and troubleshoot the formulas. Intermediate Formulas Excel has many built in formulas and functions which can be found through the Fx icon on the tool or tool bar. Shown here is the dialog box that is presented when you click on the Fx icon and then search all functions or the Math and Trig functions. This dialog box provides an index of most of the structured Excel formulas and functions. Two very nice features of this dialog box are (1) the ability to select “All” as a category and scroll through every function and formula, and (2) the brief summary of actions and capabilities of the formula or function in the lower third of the dialog box, shown inside the rectangle on the screen print. In this screen print the “Sum If” function is selected. The dialog box tells you that it adds the cells specified by a given condition or criteria. Exactly what we Chapter 16, Page 103 were seeking in the “Sum If” tasking for the valuation of inventory earlier. This dialog box can be narrowed down in its search through the selection made in the “Or Select A Category” window in the upper third of the dialog box. The categories include financial, math & trig, statistics, lookup and reference, text, and logical. Selecting one of these categories will narrow your search but may preclude you from seeing a viable option contained under another category. Formulas and functions in Excel normally start with the equal sign, “=”, which keys Excel into expecting to accomplish something through a formula or function. From this point, text, data, and reference information is provided. The simple Excel formula of =B2, a “Look to” formula or function, placed in cell C1 would simply provide you with the contents of cell B2 being presented again in cell C1 as shown on the Intermediate Formulas data file. This formula can be used through many of the exercise and problem templates as the dates and values as well as some account titles are available in a “clean target” or source presentation. Excel will also do “Look to” on text values as shown in cell D2. When text is incorporated as part of a formula or function within Excel it must be enclosed within double quotation marks or Excel will product an error message. In the “If” statement, a logic function or formula, shown in cell D12 in the Intermediate Formulas data file, you will see the word Apple or Apples. The formula or function, an “If” statement, in cell E12 is looking at cell D12 and responding based on the value in D12. If the value is singular – “apple”, Excel hopes it’s a Washington State Apple. If the text is “apples” – plural, Excel hopes they are all Washington State Apples. However, if the text string is neither “Apple” or “Apples”, Excel tells you that it is not apples. Excel cannot really read, it is comparing text strings for validity. The formula says if you find the text string of “Apple” respond with the first of the “true” statements, if the text string is not equal to “Apple” Excel executes it false statement which is another “If” statement to see if the text string is equal to “Apples.” If Excel finds a matching text string, Excel responds with the second true statement in the formula, the plural response for “Apples.” If Excel did not find the text string “Apples” on the “second look”, it responds with the false response which declares they are not apples. As addressed elsewhere in this book, there is a nesting limit of 7 levels within Excel. Can Excel determine what letter of the alphabet you entered and respond based on your input if there are 26 letters to choose from? Certainly. Enter a letter (A to Z in upper or lower case) in cell D21 and watch the response. Excel will determine the letter and respond for that letter. To overcome the 7 layer restriction on nesting, after 6 levels, the formula looks to the next line – hidden by making the text white – for an answer. All of the lines, 4 in total, look to cell D21 and evaluate the value. If the value exceeds the limits of the first line on 21, the formula in the first line looks to line 22. If the value in cell D21 matches one of the values in line 22’s range, line 22’s formula responds, if line 22’s range is exceeded, line 22 looks to line 23, and then line 23 looks to line 24. This process can continue until you run out of cells in the worksheet. If line 24 has the valid answer, it passes it to line 23 which passes it to line 22 which passes it to line 21 which displays the valid response as the “False” response to its own “If” statement. The “False for line 21 is look at line 22. After filling a worksheet with chained references you can reference the next worksheet in the workbook. After the number of worksheets is exceeded, you have Excel reference a second, then third, then fourth workbook and so on. So, while there may be a nesting limit within a cell of Excel, there is no true nesting limit if you “Look To” the next cell, worksheet, or workbook. Often a formula can be set up in a “template” format for frequent use without new formula structures being required. Suppose that you did a lot of work with the Present Value formula. Set the formula up as shown in cells D31 to G35. In cell G32 the present value formula resides and looks to the cells D31 through D35 for information. Anytime that information is changed the formula recalculates the present value. (Almost) Instant answers to a formula with little typing once the formula is set up. Occasionally Excel will have problems with formulas that should yield specific results. The frequently effective solution is to insure that the formatting of the cells is not precluding the formula or function action. For example, click into cell H38 of the Intermediate formulas data file. Once the cell is Page 104 Solving Accounting Principles Problems Using Excel for Windows active, click on the Omega symbol (Σ) for quick sum and Excel looks over to column A and generates a formula of =SUM(A38:G38) while you would have expected Excel to generate a value of 28 (1+2+3+4+5+6+7) the response is only 17. A check of the formula in cell H38 shows a correct formula =SUM(A38:G38), the sum is obviously wrong. Click into cell H39 and click the Omega symbol (Σ) for quick sum and Excel looks over to column A and generates a formula of =SUM(A39:G39). And the response is 28, the correct sum of 1+2+3+4+5+6+7. Now click into cell A38. Once the cell is active, left mouse button down and begin to sweep right on row 38, when cells A38 and B38 are highlighted, the sum at the bottom, right side of Excel shows “3”, the correct answer. As you sweep right along row 38 the sum increases to 6, and then 10. When you sweep into the cell containing the value of 5, there is no increase, there is no increase when you sweep into the cell with the value of 6, but sweeping into the cell with the value of 7 results in a sum of 17 appearing at the bottom of the screen. Time to troubleshoot and the focus has been identified. Click into cell D38. Then right click the cell and read the formatting – it is “General.” Click into cell E38 and check its formatting – it is “Text.” In the “Format Cells” dialog box Excel tells you that even though a numerical value may appear in this cell, it is text and will not function as a number or value. The same for cell F38. Reformat the cells as “General” or a numerically acceptable format – number, currency, or accounting, and then reenter your data and the sum is correct – as shown in line 39. Excel had the numbers 5 and 6 entered after the cells were formatted to text so even reformatting the cells to “General” does not change the data – text images of 5 and 6, to numerical data until the cells are reformatted and the data is reentered. Scrolling through the “All” formulas and functions category of Excel will reveal many useful formulas and functions applicable to the exercises and problems of the textbook. However, Excel is a tool and the results of every Excel formula and function needs to be evaluated for reasonableness. When working on extensive and intricate data proof the concept with basic data and simple values. For example, with the “DDB” function to handle double-declining depreciation of an asset, verify it with a $10,000 asset, $0.00 salvage value, 5-year life at 200%. This should return a value of 40% of $10,000 (100% / 5 year life X 200%) or $4,000 for the first period. If another value is returned, something is wrong with the formula selection, the data input, or formatting. In this formula, Excel is not expecting 200 for 200%, it is expecting either “2” for twice straight-line or “200%”. Excel also is not expecting commas to be entered into the values. Tip: While working on formulas and functions, you can insert a single text character in front of the equal sign and the formula or function becomes a text string and will not process. This does not reformat the cell and preclude later mathematical operations. This is a handy “escape” while troubleshooting problems and situations that may arise during entry. Excel does not want you to leave the cell with a formula or function it cannot handle. Excel may present you with proposed resolutions and trouble screens. Just because Excel presented it, does not mean it is correct or acceptable. By inserting a character in front of the equal sign this can be stopped. As a matter of presentation, if the character is a single or double quote, Excel will not show that character in the display of the cell data. This makes the single or double quote presentation neat and clean. You can see many of these inserts through the data files. Keystrokes & Shortcuts There are many keystrokes that work in a standard Windows format regardless of the program. Some are unique to the program. Here are some simple and useful keystrokes for Excel: The keystrokes of Ctrl-Home (key / keystrokes not type in entry) will take you to cell A1 unless the upper row(s) or left column(s) frozen through “Freeze Pane.” Then Ctrl-Home will take you to the upper, left most cell not frozen. Ctrl-Home will not take you into frozen columns or rows. The keystroke of Home will take you to column A of the row with the currently active cell. It will not take you into frozen columns. Chapter 16, Page 105 Page up will change the screen displayed up one “pane” of cells. If the top row is row 128, page up will make the bottom row on the new screen 127. If there is not enough rows available because of the upper or lower limit of Excel, nothing will happen. It will not take you into frozen rows. Page down will change the screen displayed down one “pane” of cells. If the bottom row is row 128, page down will make the top row on the new screen 129. If there is not enough rows available because of the upper or lower limit of Excel, nothing will happen. Arrow keys will move you one cell up, right, down, or left for each striking, if held, they will scroll you in the selected direction. If you have edited the data within a cell, the use of the arrow keys will not let you leave the cell. “Tab” or “Enter” must be struck or you must click into another cell with the mouse. Ctrl-A selects all cells on the worksheet. Ctrl-B toggles the selected cell or range of cells into and out of bold text mode. Ctrl-C copies the selected cell or range of cells. Ctrl-D is fill down – starting with the source cell, highlight the range you want filled in and press and release Ctrl-D for the fill to occur. Ctrl-F brings up the “Find” and “Find and Replace” dialog box. Ctrl-G brings up the “Go to” dialog box. Ctrl- H brings up the “Find” and “Find and Replace” dialog box. Ctrl-I toggles the selected cell or range of cells into and out of italics text mode. Ctrl-K brings up the “Hyper link” dialog box. Ctrl-N opens a new worksheet. Ctrl-O brings up the “Open Workbook” dialog box. Ctrl-P brings up the “Print” dialog box. Ctrl-R is fill right– starting with the source cell, highlight the range you want filled in and press and release Ctrl-R for the fill to occur. Ctrl-S saves the file if it has been saved before, it brings up the “Save” dialog box if this is the first save operation. Ctrl-X is cut the cell or range of cells. Ctrl-Z is “Undo.” Ctrl-` - the accent character on usually to the left of the 1/! Key brings up the audit tool bar. Ctrl-1 (The number one) is format cell or cells. F1 (The number 1) brings up the Help screen. F5 is refresh. F7 starts spell-check. F12 brings up the “Save As” dialog box. Linking Worksheets Excel can utilize the “Look to” function as a quick link between worksheets within the same workbook and link between worksheets in difference workbooks. This is different then a link or hyperlink which provides the path which opens a target document. If you are working within the same workbook but different worksheets within Excel simply enter the equal sign into the target cell, select the source worksheet and click into the source cell and press and release the “Enter” key. This is demonstrated in the “Linking” data file on the data disk. On the “Links” worksheet, the “=” (equal sign) was entered into cell A1. Then the Source worksheet was selected and cell A1 was clicked into to make it the source cell and “Enter” was struck. This returns you to the “Links” worksheet as the function is complete. Then cell A1 as selected and the formula within cell A1 was dragged down through cell A5. The dragging operation is relational as explained under Absolute References so the cells A1 through A5 of the Links worksheet are now “children” or “dependents” of the “parent” or “source” cells of the Source worksheet. Notice the special format and sequence of the linking formula, =Source!A1. This can be typed in manually if desired but it is quicker and less troublesome to let Excel write the formula. You can utilize Page 106 Solving Accounting Principles Problems Using Excel for Windows these cells for data within other formulas and you can add functions and formulas to the linking formula. In cells D4 through D7 additional math actions are added to the linking function. Linking can also be accomplished through Copy then Paste Special > Paste Link. When done with this method the references are created as Absolute References by Excel, the absolute reference function can be inserted on the “Look to” method as well. This allows the “dependent” or “child” cell to be moved, copied and pasted, or dragged to another location without losing its reference. When Excel links between two workbooks the formula becomes slightly more complicated. The formula “=[Book3]Sheet1!$A$1” inserted into a cell in a workbook other than Book3 would look to Book3 and return the data entered in cell A1. In this formula, the parent and child workbooks are within the same directory. On your data disk is a “Linking Source” data file, another file, “Linking Dependent”, looks to this file to attain data. Ensure that both these files are in the same directory and open the “Linking Dependent” workbook. Upon opening Excel will ask if you want to update the link. Select “Update” and Excel will “read” the “Linking Source” workbook for changes. It will not open the “Linking Source” workbook and it does not need to open the workbook to read the linking data. Assume that the child or dependent file is closed and the source or parent file, “Linking Source” is open and being edited. The child or dependent will not be updated upon saving the source or parent file. However, when the child or dependent file, “Linking Dependent”, is opened, Excel will ask if you want it updated and check for changes if you do. Tip: You have a big, dependent or child file that is critical and numerous individuals can make changes to the parent or source file. Upon opening the file you can select not to update the file and, once open, use File > Save As to save the file as a new name. If you desire you can Copy and Paste Special > Values the entire worksheet to remove the links and turn all the formulas into hard values. Then reopen the (real) child or dependent file, update it, and inspect it. If unacceptable changes have been made to the parent or source data file you have the child saved in a previous, unaffected condition through this technique. Excel will allow you to link between directories as long as you can define the path or generate the path through Windows movements. With the ability to link the worksheets and workbooks you can build subsidiary ledgers containing accounts receivable and accounts payable data and then link it to the general ledger of accounts receivable and accounts payable in another directory. The subsidiary ledgers would be the source or parent with the general ledger being the child or dependent cells. You would not have to have the general ledger open to make entries into the subsidiary ledgers. But when the dependent or child workbook, the general ledger file is opened, Excel would ask if you would like to check for changes and update the file. This capability is demonstrated with the data disk directories “Linked Source” and “Linked Child.” The Linked Source directory contains a data file titled “Linking Source - Directory.xls” which is looked to by the child or dependent file titled “Linking Dependent - Directory.xls” in the “Linking Child” directory. The formula in cell A1 of the “Linking Dependent – Directory.xls” file is long and specific to how you copied the files to your directory. If the links have difficulty in reestablishing upon opening, you can reinstate them by opening both files, clicking into cell A1 of the “Linking Dependent - Directory.xls” entering an equal sign, then clicking into cell A1 of the “Linking Source Directory.xls” data file and striking the Enter key. Now that the formula is in cell A1, remove the absolute reference dollar signs from the cell reference of “$A$1” to make it a relational “A1” and drag it down 35 or more rows to bring in the populated cell range of the “Linking Source - Directory.xls” data file. The links are reestablished. Clue: The cell A1 to A1 relationship is not required. You can link any cell to any cell through this process. The “Linking” and “Look to” capabilities makes Excel a powerful tool for data management. There is a danger created in this manner that the data being utilized to generate values on your worksheets and in Chapter 16, Page 107 your workbooks may not be easily reviewed or visible for review and audit. And, as a professional, you are responsible for the work you provide, regardless of the validity of the source. Chapter 17 MICROSOFT WORD Chapter Outline Word File Storage Word Upon Opening Word Tables Word Table Formulas Excel Tables Into Word Word File Storage During an initial installation Word will bring in the defaults of the installation process for file storage and document setting. If the installation is an upgrade installation the installation process will incorporate the previous file storage and document settings. The location of Word files can be left at “Default” which may be in the “My Documents and Settings” directory. You can determine the actual storage location of your will be Word Upon Opening Word is a complete word processing application and more. Upon opening Word, after registration is complete, you will be presented with a blank page usually titled “Document 1” by default. The cursor will be flashing in the upper left corner of the “sheet” waiting for text to be entered and, depending on the default settings of the installation process or as established by prior users. To enter text During an initial installation Word will bring in the defaults of the installation process for file storage and document setting. If the installation is an upgrade installation the installation process will incorporate the previous file storage and document settings. The location of Word files can be left at “Default” which may be in the “My Documents and Settings” directory. You can determine the actual storage location of your will be Word Tables Word has the ability to generate basic tables on its own through the path Table > Insert. The table inserted below was created with this function. While it does not have all of the features of Excel, it is a good tool for the presentation of limited material or materials not found elsewhere and not necessarily needed later. By following the path Table > Insert > Table and telling Word through the dialog box that you want a table with 5 rows and 5 columns the result is: Section 2, Page 108 Chapter 17, Page 109 1/A B C D E 2 10 15 20 25 3 11 22 33 44 4 12 144 1728 20736 5 33 60.33 By default, Word will make these columns fill the width of the page. You can select the table by clicking into the table and then moving your cursor to the upper left corner area. While moving your cursor in that area, you will see a four-headed arrow appear. Click on that arrow and you will have selected the entire table. You can also sweep and highlight the table from one corner to another if desired. When the table is selected, you will activate the tab markers in the ruler/measurement bars at the top and side of the page. By moving these tab Microsoft Word Table Tabs in the Ruler markers with your cursor (select them by left mouse click and hold the left button down) you can change the column width and row height by dragging them. You can also change row height and column width by selecting the row(s) or column(s) by highlighting them, then right clicking on the highlighted cells and following the path to Table Properties and selecting the Row or Column tabs and changing the values there. The borders on the table can be formatted by highlighting the specific cell, cells, or the entire table and using the Borders icon on the tool bar or right clicking and selecting Borders and Shading. The borders of row 3 have been removed through this process and the double lines under row 5 were placed there by the same process. Under this tab, the line weight and characteristics can be modified as desired to include dashes, dots, and mixes of other symbols. Row 2 was modified in this manner. The background and text coloring of the cells and their data are controlled as other formatting features that are in Word. You can select the table, the row, the column, or the cell and invoke the colors and features through the tool bar icons or by following the path Format > Font while the cells are highlighted. Once a table has been inserted you can select a row or column and follow the path Tables > Insert and increase the number of rows or columns as needed by instructing Word to place the inserted rows or columns into the existing table above, below, to the right, or to the left of the cursor placement. To insert a specific number of rows or columns, select/highlight that number of rows or columns and then follow the path Table > Insert and select rows or columns from the selection options and inform Word if you want them above, below, to the left, or to the right of the highlighted cells. Rows and columns can be deleted through the path Table > Delete after the row(s) or column(s) are highlighted. Tip: The entire row or column need not be selected to insert or delete rows or columns, only cells representing the number of rows or columns you desire to have Word insert or delete. However, the technique / habit of highlight all of the cells reinforces what will be inserted or deleted before the action is invoked. Page 110 Solving Accounting Principles Problems Using Excel for Windows Word Table Formulas Microsoft Word tables have their own formulas under the path Table > Formulas. It is through this path that you establish and modify formulas within Word tables. Formulas in Word are not “read” in a formula or data entry window like Excel. Word table formulas are done similar to Excel formulas in that they use cell references of columns and rows. The upper left most cell is cell A1, the cell to its right is B1, the cell below cell A1 is A2 and so on. In a Word table though the formula to add the contents of cells B2, B3, and B4 is “=SUM(b2,b3,b4)” and the formula to multiply the contents of cells C2, C3, and C4 is =PRODUCT(d2,d3,d4). Notice that the operation is controlled by the leader – Sum or Product and that the cell references are separated by commas, not attached to each other by math operatives such as the + or * symbols. The following table contains the formula =SUM(b2,b3,b4) in cell B5 and it adds the rows 2 through 4 into cell B5, the formula =AVERAGE(c2,c3,c4) in cell C5 and it calculates the average of the values within cells C2, C3, and C4 into cell C5, and the formula =PRODUCT(d2,d3,d4) in cell D5 which multiplies the value in cell D2 by the value in cell D3 and the product of that operation by the value in cell D4 and places the final product in cell D5. A1 B1 C1 D1 A2 2 3 4 A3 3 4 5 A4 4 5 6 A5 9 4 120 A6 Sum Average Product Word formulas are not “live” like Excel when manual calculation is not invoked. That is, if you had access to this table and changed the value in cell B2 from 2 to 20, the value shown in cell B5 would not change. Note: It is frequently easier to create the table within Excel and then copy and paste it into Word. Excel Tables Into Word While Excel may be a major tool for accountants, communication of information is the key to success. Once an Excel worksheet is populated with data its presentation can be adjusted through hidden columns and rows, paste special, and other tools such as “look to” formulas from a specific area of the worksheet or a new worksheet to give clean presentation. However, this may not suit the complete requirement and the constraints of Excel may hinder the presentation. Much of the work presented within this text was accomplished by entering the data, such as an exercise or problem, into Excel, then highlighting the data, copying it through the keystrokes Ctrl-C, clicking the copy icon on the tool bar, or following the path Edit > Copy. Then the data is pasted into Microsoft Word by selecting Word, placing the cursor into the appropriate area, the target zone, and using the keystrokes Ctrl-V, clicking the paste icon on the tool bar, or following the path Edit > Paste. Once placed in this manner, the data is “static” in nature. If the source within Excel changes, the data within Word will retain its original value without update. From this point, the data is normally contained within a table which can be formatted as many other Word objects can be with borders, fill colors, fonts and sizing. To move this inserted object place your cursor near the top of the table and a square with a four headed arrow, as shown in the screen print will appear. When it does, click on it and the table will be selected. While the table is selected, it will highlight to emphasize Positioning blocks or cubes the fact that it is selected. By right clicking on the Chapter 17, Page 111 highlighted table, you will be presented with a pop-up menu which has formatting options on it. While it is highlighted, you will see a new set of “blocks” or “cubes” appear in the ruler bar of Word. These blocks or cubes, as shown in the screen print, are the margin tools for the table. You can justify the table by clicking on the Word left, right, center, or fill justify buttons. You can also reposition and resize the table by dragging these margin tools left or right with your mouse. Additionally, Excel and Word have the ability to paste the data as “linked” information from Excel into Word. With this feature, when the source data in Excel is updated, the Word data will be updated upon the next opening of the file Word file. This feature makes your data dynamic. Word of caution, while being a “really neat” feature, it may not be appropriate for all situations so its use needs to be appropriate. To link the data in Word back to the source in Excel, copy the data as previously explained from Excel, open Word and locate the target area within Word and follow the path Edit > Paste Special, then select Paste Link from the left side of the pop-up dialog box and select Microsoft Excel Worksheet Object in the “As” window. The data, as copied, is pasted into the document and linked back to its parent or source. Reading the information below it becomes immediately apparent that its presentation is unclear and does not convey the complete data as words are chopped and lost. 40, Shares 000 $1 pur par value $50 p Amount ,000$12 co rate per flig If the cells in5Excel’s source data are reformatted to increase their width, the “trimming” problem is resolved. But this may not be practical as resizing cells would affect the rest of the parent or source worksheet presentation. This trimming is because the cells are shown as they are in Excel and numerous empty cells were copied and pasted, that is, while all of the data has been entered into the cells, the data exceeded the cell width of the column and the Word page and flowed into the areas of other columns. By copying and pasting the link with a smaller range of cells, the presentation is: 40,000 shares purchased $1 par value per share $50,000 amount contributed to RAS, Inc for the shares $125 rate per flight hour flown Once pasted through the linking operation you can select the data in Word and add additional formatting to it as mentioned earlier without it affecting the source data but that capability is limited. Fill, borders, and font coloring from the tool bar are the easiest to invoke. This is done below. 40, 000 shares purchased $1 $50 ,000 par value per share amount contributed to RAS, Inc for the shares $12 5 rate per flight hour flown The copied and linked blocks can be moved around once placed by selecting the matrix of linked cells and then moving the tab markers on the index markers. Chapter 18 HEWLETT-PACKARD HP-12C Chapter Outline Hewlett-Packard HP-12C Basics Days Between Dates With The HP-12C Storing Values In The HP-12C Balloon Payments With The HP-12C Time Value Of Money With The HP-12C Present Value Of A $1 With The HP-12C Present Value Of An Annuity Of $1 With The HP-12C Future Value Of An Annuity Of $1 With The HP-12C Other HP-12C Functions Hewlett-Packard HP-12C Basics Hewlett-Packard produces the HP-12C Programmable Financial Calculator, a calculator for the professional accountant, financial analysts, and business users. This calculator uses “RPN” or “Reverse Polish Notation” logic. With this logic as an operating system to add 2 and 2 together and get the result of 4, the keystrokes are: “2” – The display shows “2”. “Enter” – The display shows 2.0000. (If the display is set to 4 significant digits.) “2” – The display shows “2”. “Plus” – The display shows 4.000. (If the display is set to 4 significant digits.) This is slightly different than the “2” – The display shows “2”. “+” – The display shows 2.0000. (If the display is set to 4 significant digits.) “2” – The display shows “2”. “=” – The display shows 4.000. (If the display is set to 4 significant digits.) of calculators like the BA II+ of Texas Instruments. When mastered, the HP-12C can greatly assist you in your academic endeavors, accounting, financial, and business career. The HP-12C has an extensive manual and many features, only a few will be addressed here. The same basic features will be addressed for the Texas Instrument BA II+ (Business Analyst II+) in other chapters. Hint: There is no “=” (equal sign) on the HP-12C calculator. First, the HP-12C default for significant digits displayed is four shown as “0.0000”. In most academic, accounting, financial, and business situations four or five significant digits are appropriate. And, when the requirement is less, the few extra digits displayed can easily be ignored. They will also be hidden if the HP-12C needs more display room for whole numbers. To set or change the number of decimal places displayed in the HP-12C’s display follow these steps: 1. Turn the HP-12C on by pressing and releasing the black “ON” button on the lower left corner of the key pad. This is a “toggle” switch, pressing it while the HP-12C is on will turn the calculator off. Section 2, Page 112 Chapter 18, Page 113 2. Press and release the yellow “F” (Function) key once. This key is just to the right of the “ON” on the bottom row. 3. Press and release any of the numerical numbers from “0” through “9”. The display changes to the requested display immediately. Any values current in the display are redisplayed in the new format. 4. Now test the calculator, if you chose “4” as the significant digits, dividing 0.025 by 30 is actually 0.000833333….. This is done by the keystrokes “0.025”, “ENTER”, “30”, “÷” and the display should show “0.0008” when the HP-12C is asked for this calculation while set to four significant digits. The trailing 3s round down and therefore, “off” the display. This complete value to some significant number of digits is still held in memory, without rounding, for future calculations. Multiply the display by 100,000 through the keystrokes “100000” and then “X” (Multiply) and the results should be “83.3333” – demonstrating that the non-displayed values are not lost. 5. Now divide 0.025 by 26 with the keystrokes “0.025”, “ENTER”, “26”, “÷” and the display should show “0.0010” when the HP-12C is asked for this calculation while set to four significant digits. The HP-12C should return “0.0010” while the actual value is 0.00096154… and it has been rounded up to 0.0010. Multiply the display by 100,000 through the keystrokes “100000” and then “X” (Multiply) and the result should be 96.1538 – again demonstrating that the nondisplayed values are not lost. Second, the HP-12C gives you a quick way to support the time value of money calculations in numerous periodicities as well as annual statements and monthly interest and payments. No adjustments or setting need be made. These will be discussed in detail later. Tip: When using the time value of money function on the calculator develop the habit of entering values in all “fields” from left to right or right to left even if that value is “0” (zero). This should preclude any old values from entering the equation unexpectedly. Enter the following data through the keystrokes – enter “60” in the “n” field of the time value of money register of the HP-12C by typing in “60” into the display and pressing and releasing the “n” key in the upper left corner of the HP-12C. Pressing the “ENTER” key is not required since you will not be using it for a “keyboard” operation. Type in “6.9” and press and release the “ENTER” key, the type in “12” followed by the “÷” (Division) key. The display should read “0.5750”. Press and release the “i” (Interest) key to record this value as the period interest. Type in “35000” and press the “PV” (Present value) key to enter the loan value into the present value field. Type in “0” and press and release the “PMT” (Payment) field. As a matter of discipline, entering a value, even “0” in the fields or register from left to right or from left to right will preclude an unintended value from entering the calculation. Type in “0” and press and release the “FV” (Future value) field. Now press the “PMT” key and the HP-12C will calculate the payment for the loan as “-691.3918”. The HP-12C will respect the presentation of cash flow and value flow within the time value of money (TVM) calculations. If the “PV” (Present value or principle value) is entered as a positive number indicating cash flows in or value flows in, the “PMT” (Payment) and / or “FV” (Future value” will be negative indicating cash flows out or value flows out. If “PMT” (Payment) is entered as a positive value indicating cash flows in or value flows in, then computed “PV” and / or “FV” (Future value) will be negative indicating cash or value flows in the opposite direction - out. If you wanted true discipline, you can enter the present value as positive since it was value inflow to you and cash outflow in payments will be negative. However, if you were the used car salesman and wanted discipline, in the data enter, type in “35000” and then press the “CHS” (Change sign) key in the fifth column of the first row. This will convert a positive display to a negative value (or a negative value to a positive value). Rerun the calculation simply by placing a “-35000” (negative) into the present value field and recompute the Page 114 Solving Accounting Principles Problems Using Excel for Windows payment. This time the HP-12C should show “691.3918”, a positive value indicating cash flows or value flows in since the present value is negative, representing cash flows or value flows out. Annuities can be paid at the beginning or the end of the payment period To make this change there are two buttons on the HP-12C. The “Beginning” button labeled “BEG” in blue font on the slanted face of the “7” and the “End” button labeled “END” in blue font on the slant face of the “8”. To change to “Annuity Due” or “Beginning”, press and release the blue “g” key in the lower left corner of the HP-12C and then press the “BEG” (Beginning / 7) key since you are in the “Blue text options” after pressing and releasing the blue “g” key. At this time the HP-12C will display “BEGIN” in the center, bottom of its display. To enter an “Ordinary Annuity” where payments are made at the end of the period, press and release the blue “g” key in the lower left corner of the HP-12C and then press the “END” (Ending / 8) key since you are in the “Blue text options” after pressing and releasing the blue “g” key. At this time the HP12C display of “BEGIN” in the center, bottom of its display will disappear indicating that it is in the default mode of “Ordinary Annuity.” Clue: While the HP-12C is in the “Annuity Due” mode – “BEGIN” indicating that the annuity payment is due at the beginning of the period there will be displayed in the center, bottom of the display. If the HP-12C is in the “END” there will be no additional display items as this is the default mode. Days Between Dates With The HP-12C The Hewlett-Packard HP-12C will calculate the days between dates. A very nice function for accounts receivable and accounts payable aging as well as determining issues such as interest payments made or received every 30 days. To determine the days between the invoice date of January 24, 2008 and the date of March 15, 2008: 1. Turn the HP-12C on by pressing and releasing the “ON” button on the bottom left corner of the key pad. 2. Press the blue “g” key in the third column of the bottom row followed by the “M.DY” (“5” / “M.DY”) key, in the eighth column of the second row from the top. This sets the HP-12C to a date format of “01.242008” for January 24, 2008. Your instruction manual has other date formats available. 2. Type in the first date, January 24, 2008, as “01.242008” and press “Enter.” 3. Type in the second date of March 15, 2008 as “03.152008”. 4. Press and release the blue “g” key followed by the “▲DYS” (EEX / ▲DYS) in the sixth column of the second row from the top. This key reads as “Delta in days” with “▲” being an engineering symbol for “delta” or “Degree of change.” The “▲DYS” is in blue font on the slant face of the key since you are in the “g” (blue) key board options. The HP-12C should respond with “51” days between these two dates. You can also calculate the date a note, such as a 120-day note, issued January 24, 2008 is due. To do this: 1. Turn the HP-12C on by pressing and releasing the “ON” button on the lower left corner of the key pad. 2. Press the “g” (blue) key in the third column of the bottom row followed by the “M.DY” (“5” / “M.DY”) key, in the eighth column of the second row from the top. This sets the HP-12C to a date format of “01.242008” for January 24, 2008. Your instruction manual has other date formats available. 2. Type in January 24, 2008 as “01.242008” and press “Enter.” 3. Type in “120” into the HP-12C. Do not hit the “ENTER” key. 4. Press the “g” (blue) key in the third column of the bottom row followed by the “DATE” (“CHS” / “DATE”) key, in the sixth column of the top row. The HP-12C should respond with “5,23,2008 5” indicating the note will be due on May 23, 2008, which is a Friday as indicated by the trailing “5”. Chapter 18, Page 115 Tip: Entering the days between dates value as a negative will generate a valid response for a earlier date. If you wanted a 180-day note payable on June 30, 2008, enter “06.302008” and press “ENTER.” Then enter “120” and press the “CHS” (change sign) key in the sixth column of the top row. The display will show “-120”. Press the blue “g” key followed by the “Date” key (“CHS” / “DATE”) again. The HP12C will respond with “3,02,2008 7” indicating that the note should be issued Sunday, March 2, 2008. Storing Values In The HP-12C A major asset of a professional calculator like the HP-12C is the ability to store data. In the example you are issuing 10 year bonds. The bonds carry a face interest rate of 8% while the market is 7%. Interest is paid semi-annually. The issue consists of 1,500 bonds with face values of $1,000 each. Your challenge is to identify the present value of the interest payments, the present value of the bonds, and the present value of the bond issue. The “Store” and “Recall” capability are major tools in this task. First, determine the face value of the bonds – 1,500 bonds X $1,000 = $1,500,000 by typing in “1500” and pressing and releasing the “Enter” key. Then type in “1000” and press and release the “X” (multiply) key. The display will show “1,500,000”. Now use the keystrokes “STO” (Store) near the bottom of the calculator in the fourth column, followed by pressing and releasing the “1” key. This will store $1,500,000 into the Storage Register 1. Now you will address the present value of the bonds. While the $1,500,000 is still in the display, press and release the “FV” (Future value) key to record this value into that field. Type in “0” (zero) and press and release the “PMT” (Payment) button. It is recommended that you develop a discipline of entering values in all the TVM (Time value of money) fields from left to right or right to left to ensure previous values do not enter your current calculation. This field will remain at “0” for this calculation. Type in “0” (zero) and press and release the “PV” button. It is recommended that you develop a discipline of entering values in all the TVM (Time value of money) fields from left to right or right to left to ensure previous values do not enter your current calculation. This will be your “solve” value in a couple of steps. Type in the market interest rate as a whole number – “7” then press and release “ENTER” key, enter “2” followed by the ÷ (division) key. The display will properly state the annual (stated) interest rate a period of interest rate of 3 ½%. Press and release the “i” (Interest) key to enter the value into the interest field. Type in “10”, the life of the bonds in years, then press and release the “ENTER” key followed by “2” and the “X” (Multiply) key in the right most column of the calculator. The display should read “20”. Press and release the “n” (Period) key to enter this value into the “Number of periods” or “life” register. Now press and release the “PV”(Present value) key in the third column of the top row. The HP-12C should respond with “-753,848.8266”. This is the present value of the bonds. Press and release the “STO” button in the fourth column of the bottom row followed by the “2”. You have now stored that value into the second storage register. To solve the present value of the interest payments, enter “0” (zero) into the HP-12C and press and release the “FV” (Future value) key. Press and release the “RCL” (Recall) key, in the fourth column of the bottom row of the key pad, followed by the “1” key. The value stored in the first memory register, “1,500,000” should appear in the display. Page 116 Solving Accounting Principles Problems Using Excel for Windows Press and release the “ENTER” key then enter “8” and press and release the “%” for the face interest rate in the fifth column of the second row from the top. The display should show “120,000.0000”. Now enter “0.5” indicating semi-annual interest payments. Press and release the “X” (multiply) key on the right side of the HP-12C. The display should show the cash interest payments of “60,000.0000”. Press and release the “STO” (Store) key and then the “3” key to place this value in the third storage register. The “60,000.0000” value should still be in the display. Press and release the “PMT” (Payment) key. Press and release the “PV” (Present value) key and the HP-12C should display “-852,744.1981”, the present value of the interest payments. The “N” (Periods) and “I/Y” fields were entered in your first calculation for the present value of the bonds and you have not changed them in this short period of time so reentry was not necessary. Press and release the “STO” (Store) key followed by the “4” to store this value in the fourth memory register. The present value of the bonds is the sum of: “RCL” “2”, “ENTER”, and “-753,848.8266” should appear. Press and release the “RCL” (Recall) key followed by pressing and releasing the “4” key and the display should read “-852,744.1981”. Press the “+” (Plus) sign to display the sum of these two numbers – “-1,606,593.025”. Since your display was set to four significant digits but the HP-12C needed one of those places to display a whole number it took it away. This value is the present value of the bonds. While the “-1,606,593.025” is in the display, press and release the “STO” (Store) button followed by the “5” to store this value. To determine the premium or discount, recall the value in memory register 1 by “RCL” “1” and ADD these numbers as one is a positive value while the other is a negative value, the premium of “106,593.025” on this bond issue. Store this value in memory register by pressing “STO” followed by “6”. Now, the bond issue is stored in “1”, the present value of the bonds is stored in “2”, the cash interest payments are stored in “3”, the present value of the interest payments is stored in “4”, the present value of the bond issue is stored in “5”, and the premium is stored in “6”. The memory registers can be “overwritten” or “rewritten” by simply “STO” and the specific registry number. All the memory registers can be cleared at one time by the keystrokes the yellow “f” key followed by the “Clear – Reg” key which is in the fifth column and the third row for the top which has white text of “CLX and orange/yellow text above it labeled “REG” which is under the “Gang title” of “Clear.” The valid registers are from “0” (zero) through “9” (nine). Balloon Payments With The HP-12C The Hewlett-Packard HP-12C (HP-12C) respects cash and value inflows and outflows. If the present value (PV) (or principle) entered is positive indicating cash inflows or value inflows to you, then the payment (PMT) or future value (FV) will be an outflow or a negative value. However, for most uses you realize that you are getting a loan or making a loan and enter the present value (PV) as positive to save keystrokes and simply read “-691.3918” as “$691.39” as your payment or cash outflow. However, assume that in this loan example, you are buying a $35,000 vehicle, the loan term is 60 months, the interest is 6.9% annually, and you are going to make a balloon payment of $5,000 at the end of the loan with and payments are made monthly. “N” or number of periods is (5 years X 12 months) “60”, “i” or interest is “6.9 ENTER 12 ÷” or “0.5750”, “PV” or present value is “35000”, and “PMT” or payment is 0 as a matter of discipline. Now cash flow representation is important. Since the balloon payment is cash flow out and the principle is cash flow in, if the principle is positive, then the balloon payment or “FV” – future Chapter 18, Page 117 value, must be negative. If you had entered the 35000 as a negative number representing cash flow or value flow out, then the balloon payment entered in “FV” must be positive. When “5000” is entered into the HP-12C you will need to press the “CHS” key in the sixth column of the top row of the key pad to convert the positive value to negative “-5000”. Now press and release the “FV” key to enter the negative “5000” into that field. Now press “PMT” (Payment) the key in the fourth column of the top row. The HP12C should return -621.13716. Without the balloon payment your payment was $691.3918, with the balloon payment your payment is only $621.3716. Had the balloon payment been put in as a positive number matching the “PV” or present value field, the payment would be -761.4121 or $761.4121. Which fails a basic logic evaluation of lower your payment level by the balloon payment. Time Value Of Money With The HP-12C The Hewlett-Packard HP-12C (HP-12C) utilizes the same field identities as the accounting and financial professionals in the calculation of the time values of money. On the top row of keys of the HP-12C from left to right (column 1 through column 5) you are presented with: “n” for the number of periods for the event. There is a way to turn years into months with the HP12C. In the loan example the term is 5 years. Enter “5” into the HP-12C, then press the blue “g” key followed by the “12X”, which is the “n” key in the upper left corner of the key pad. By pressing the blue “g” key first you enter a secondary key pad which states “Take the input value and multiply it by 12” when you press the “n” key. The display will show “60”, the result of 5 years multiplied by 12 months per year. No additional settings or actions are required to take advantage of this function. If you press “RCL” (Recall) in the bottom row of the key pad followed by the “n” key you will see the value of “60”. “i” is for interest for the event. The HP-12C is expecting interest in whole values so 6.9% is entered as “6.9”. If this is an annual interest rate and interest is compounded monthly you can enter the “6.9” followed by blue “g” key followed by the “12÷”, which is the “i” key in the second column of the upper row of the key pad. By pressing the blue “g” key first you enter a secondary key pad which states “Take the input value and divide it by 12” when you press the “i” key. The display will show “0.5750”, the result of 6.9(%) divided by 12 months per year. No additional settings or actions are required to take advantage of this function. “PV” is the present value field. For this example you will remain with the basic loan and conform to discipline. Since the cash flow or value is in, it will be positive, so enter 35000 into the HP-12C and press and release the “PV” key in the third column of the top row. “PMT” is the payment field. Since you are trying to solve for payment and want to ensure every field’s value, enter “0” (zero) into the HP-12C and press the “PMT” key. “FV” is the future value field. For this example you will retain your balloon payment issue. Since the “PV” or present or principle value is positive, this must be negative indicating that you are making the payment, representing cash flows out, at the end of the loan. Enter 5000 into the HP-12C then press and release the “CHS” (Change sign) key in the sixth column of the top row of the HP-12C. The display changes to -5,000. Press and release the “FV” future value key in the fifth column of the top row. Now solve for “PMT” or payment by pressing the “PMT” (Payment) button in the fourth column of the top row of the HP-12C. The HP-12C should respond with -621.3716 indicating our payment is $621.3716. Page 118 Solving Accounting Principles Problems Using Excel for Windows Present Value Of A $1 With The HP-12C You need the present value of $1 with 15 periods at 15% interest and no payments, the fields would be: n = 15 i = 15 PV = 0 as a matter of discipline to ensure the values in the HP-12C. PMT = 0 as a matter of discipline to ensure the values in the HP-12C. FV = 1 When “PV” (Present value) is pressed and released the HP-12C should respond with 0.1229 which can be checked with printed present value table for validity. To find the present value of $150 with 15 periods at 15% interest and no payments, the fields would be: n = 15 i = 15 PV = 0 as a matter of discipline to ensure the values in the HP-12C. PMT = 0 as a matter of discipline to ensure the values in the HP-12C. FV = 150 When “PV” (Present value) is pressed and released the HP-12C should respond with -18.4342 which can be checked with printed present value table for validity by multiplying $150 X 0.1229 = $18.4350 and the difference is a rounding factor. Future Value Of A $1 With The HP-12C You need the future value of $1 with 15 periods at 15% interest and no payments, the fields would be: n = 15 i = 15 PV = 1 PMT = 0 as a matter of discipline to ensure the values in the HP-12C. FV = 0 as a matter of discipline to ensure the values in the HP-12C. When “FV” is pressed and released. The response should be -8.1371 which can be checked with printed future value table for validity. To find the future value of $150 with 15 periods at 15% interest and no payments, the fields would be: n = 15 i = 15 PV = 150 PMT = 0 as a matter of discipline to ensure the values in the HP-12C. FV =0 as a matter of discipline to ensure the values in the HP-12C. When “FV” (Future value) is pressed and released the HP-12C response should be -1,220.5592 which can be checked with printed future value table for validity by multiplying $150 X 8.1371 = $1,220.5650 and the difference is a rounding factor. Chapter 18, Page 119 Present Value Of An Annuity Of $1 With The HP-12C You need the present value of an annuity of $1 with 15 periods at 15% interest and no future value. The payments are to be made at the end of the period. The fields would be: n = 15 i = 15 PV = 0 as a matter of discipline to ensure the values in the HP-12C. PMT = 1 FV = 0 as a matter of discipline to ensure the values in the HP-12C. When “PV” is pressed and released the response should be -5.8474 which can be checked with printed present value of an annuity table for validity. To find the present value of an annuity of $150 with 15 periods at 15% interest and no future value. The payments are to me made at the end of the period. The fields would be: n = 15 i = 15 PV = 0 as a matter of discipline to ensure the values in the HP-12C. PMT = 150 FV = 0 as a matter of discipline to ensure the values in the HP-12C. When “PV” is pressed and released the HP-12C should respond with -877.1055 which can be checked with printed present value of an annuity table for validity by multiplying $150 X 5.8474 = 877.1100 and the difference is a rounding factor. For the present value of an annuity due, change the HP-12C into “BEGIN” or “Beginning” mode (due) by utilizing the keystrokes: 1. 2. 3. Press and release the blue “g” key once. This key is in the third column of the bottom row. Press and release the blue font “BEG” (7 / BEG) key, this text is shown below the “7” on the slant face in the top row of the key pad, the seventh column. The blue text on the slant face of the key is used since you are in the “blue” or “g” keyboard options. The display should show “BEGIN in the bottom, center of the display. Recomputing the example as an annuity due by pressing the “PV” (Present value) key should result in a value -1,008.6713. To change to “ordinary annuity” or an annuity paying at the end of the period: 1. 2. 3. Press and release the blue “g” key once. This key is in the third column of the bottom row. Press and release the blue font “END” (8 / END) key, this text is shown below the “8” on the slant face in the top row of the key pad in the eighth column. The blue text on the slant face of the key is used since you are in the “blue” or “g” keyboard options. The display of “BEGIN in the bottom, center of the display will disappear as this is the default mode. Future Value Of An Annuity Of $1 With The HP-12C You need the future value of an annuity of $1 with 15 periods at 15% interest and payments at the end of the periods – ordinary annuity, the fields would be: n = 15 i = 15 PV = 0 as a matter of discipline to ensure the values in the HP-12C. Page 120 Solving Accounting Principles Problems Using Excel for Windows PMT = 1 FV = 0 as a matter of discipline to ensure the values in the HP-12C. When “FV” (Future value) key in the fifth column of the top row is pressed and released the HP-12C response should be -47.5804 which can be checked with printed future value of an annuity table for validity. To find the future value of an annuity of $150 with 15 periods at 15% interest and payments made at the end of the periods, the fields would be: n = 15 i = 15 PV = 0 as a matter of discipline to ensure the values in the HP-12C. PMT = 150 FV = 0 Then press and release the “FV” (Future value) key. The response should be -7,137.0616 which can be checked with printed future value of an annuity table for validity by multiplying $150 X 47.5804 = 7,137.0616 and the difference is a rounding factor. For the present value of an annuity due, change the HP-12C into “BEGIN” or “Beginning” mode (due) by utilizing the keystrokes: 1. 2. Press and release the blue “g” key once. This key is in the third column of the bottom row. Press and release the blue font “BEG” (7 / BEG) key, this text is shown below the “7” on the slant face in the top row of the key pad, the seventh column. The blue text on the slant face of the key is used since you are in the “blue” or “g” keyboard options. Recomputing the example as an annuity due by pressing and releasing the “FV” (Future value) key. The HP-12C should respond with a value of “-8,207.6209”. To change to “ordinary annuity” or an annuity paying at the end of the period: 1. Press and release the blue “g” key once. This key is in the third column of the bottom row. 2. Press and release the blue font “END” (8 / END) key, this text is shown below the “8” on the slant face in the top row of the key pad in the eighth column. The blue text on the slant face of the key is used since you are in the “blue” or “g” keyboard options. 3. The display of “BEGIN in the bottom, center of the display will disappear as this is the default mode. Other HP-12C Functions There are other HP-12C functions explained in the Hewlett-Packard HP-12C manuals and documents. Some of these include the determination of the present value of a bond issue through a single math function, net present value, internal rate of return, and depreciation functions. Chapter 19 TEXAS INSTRUMENTS BA II+ Chapter Outline Texas Instruments BA II+ Basics Days Between Dates With The BA II+ Storing Values In The BA II+ Balloon Payments With The BA II+ Time Value Of Money With The BA II+ Present Value Of A $1 With The BA II+ Present Value Of An Annuity Of $1 With The BA II+ Future Value Of An Annuity Of $1 With The BA II+ Other BA II+ Functions Texas Instruments BA II+ Basics The Texas Instruments TI BA II Plus Advanced Business Analyst Calculator is a powerful tool that, if learned, can greatly assist you in your academic endeavors, accounting, financial, and business career. The BA II+ has an extensive manual and many features, only a few will be addressed here. First, the BA II+ default for significant digits displayed is two shown as “0.00”. In most academic, accounting, financial, and business situations four or five significant digits are appropriate. And, when the requirement is less, the few extra digits displayed can easily be ignored. They will also be hidden if the BA II+ needs more display room for whole numbers. To set or change the number of decimal places displayed in the BA II+’s display follow these steps: 1. 2. 3. 4. 5. 6. 7. 8. Turn the BA II+ on by pressing and releasing the “On/Off” button on the upper right corner of the key pad. Press and release the yellow “2nd” key once. This key is in the first column of the second row. Press and release the “Format” key. This is in yellow text above the “.” (period) key in the center of the bottom row. The yellow text above the key is used since you are in the “2nd” keyboard option as selected in step 2. Press and release the “4” key, if four significant digits are desired, or, press any number from 0 through 8. Press and release the “Enter” key on the first row, second column. Press “CE/C” on the lower left corner of the keyboard to clear the display and return to normal operations. Now test the calculator, if you chose “4” as the significant digits, dividing 0.025 by 30 is actually 0.000833333….. When the BA II+ is asked for this calculation while set to four significant digits the response should be “0.0008” since the trailing 3s round down and therefore, “off” the display. This value is still held in memory, without rounding, for future calculations. Multiply the display by 100,000 and the result should be 83.3333 – demonstrating that the non-displayed values are not lost. Now divide 0.025 by 26. The BA II+ should return “0.0010” while the actual value is 0.00096154 and it has been rounded up to “0.0010”. Multiply the display by 100,000 and the result should be “96.1538” – again demonstrating that the non-displayed values are not lost. Section 2, Page 121 Page 122 Solving Accounting Principles Problems Using Excel for Windows Second, the BA II+ comes defaulted to compute the time value of money with monthly payments and interest compounding – set for car dealers and real estate agents. In the academic, accounting, financial, and business world, interest is frequently compounded in daily, monthly, quarterly, semi-annual, and annual terms. To insure that the BA II+ will handle most of these options without getting them confused it is recommended that you set the payments and compounding periods (P/Y = Payments per year and C/Y = Compounding periods per year) to “1” so that you can control the payment and compounding periods as follows: 1. 2. 3. 4. 5. 6. 7. Turn the BA II+ on by pressing and releasing the “On/Off” button on the upper right corner of the key pad. Press and release the yellow “2nd” key once. This key is in the first column of the second row. Press and release the “P/Y” key, this text is shown above the “I/Y” key on the third row in the second column. The yellow text above the key is used since you are in the “2nd” keyboard options as selected in step 2. The display should show “P/Y=” and a value such as “12”. Enter “1” and then press the “Enter” key. Press the up arrow or down arrow on the first row in the third or fourth column. Check the value of “C/Y” and insure that it is also “1”. If it is not, enter “1” and then press the “Enter” key. Press “Clear” to return to the normal operations mode of the calculator. As a test, you are going to determine the payment for a $35,000 vehicle, the loan term is 60 months, the interest is 6.9% annually and payments are made monthly. The BA II+ time value of money keys are the five keys in the third row labeled “N” for number of periods, “I/Y” for the interest rate, “PV” for the present value, “PMT” for payments, and “FV” for future value. Tip: When using the time value of money function on the calculator develop the habit of entering values in all “fields” from left to right or right to left even if that value is “0” (zero). This should preclude any old values from entering the equation unexpectedly. For the example loan, enter 60 into the keypad and press and release the “N” key in the first column of the third row. Then enter 6.9% by typing in 6.9 then press and release the division (÷) key and type in 12 followed by the equal sign. This provides the period interest rate (6.9 / 12) of 0.5750. Enter this into the interest field by pressing the “I/Y” key in the second key in the third row. Clue: “I/Y” converts the value input into a percentage so had you entered 6.9%/12 or 0.00575, the final calculation will be off. Type in the $35,000 as 35000 and press the “PV” key, the center key in the third row. Since the object is to compute payment (PMT), type in “0” (zero) and press the “PMT” or payment key, the key in the fourth column of the third row. This will preclude any residual value from carrying over. Since there is no balloon payment on this loan, type in “0” (zero) and press the “FV” or future value key. Now press and release the “CPT” (compute) in the upper left corner of the keyboard. Now press and release the “PMT” or payment key. The BA II+ should calculate -691.3918 indicating that for only $691.39 a month you can have that used, minimum option, SUV hidden on the back lot. Clue: The asterisks appearing above the value of the payment indicates that this value is computed, not inputted by you. The payment is negative since the present value or principle was entered as a positive indicating a cash or value in to you. If you wanted true discipline, you would enter the present value (in this example) as positive number since it was value inflow to you and cash outflow in payments will be negative. However, if you were the used car salesman and wanted discipline, in the data enter, type in 35000 and then press the +/- key just inside the bottom right corner of the keyboard. This will convert a positive Chapter 19, Page 123 display to a negative value (or a negative value to a positive value). Rerun the calculation simply by placing a -35000 (negative) into the present value field and recompute the payment. This time the BA II+ should show 691.3918, a positive value since the present value is negative and it indicates the payments will be cash in flows or value inflows. Annuities can be paid at the beginning or the end of the payment period. To make this change there is a “Toggle” function – the same keystrokes once will place it in the opposite mode of what it was in. So if you run this set of keystrokes from the default of “END”, the BA II+ will be switched to “BGN” or “Beginning” for annuity due. If it was in “BGN” for annuity due, the process will switch it to an ordinary annuity or one paid at the end of the period. The keystrokes for this toggle on the BA II+ are: 1. 2. 3. 4. 5. 6. 7. Turn the BA II+ on by pressing and releasing the On/Off button on the upper right corner of the key pad. Press and release the yellow “2nd” key once. This key is in the first column of the second row. Press and release the “BGN” key, this text is shown above the “PMT” key on the third row in the fourth column. The yellow text above the key is used since you are in the “2nd” keyboard options as selected in step 2. The display should show “END” or “BGN” to indicate which mode you are currently in. If this is the mode you desire, press and release the “CE/C” key on the lower left corner of the key pad to cancel the toggle process. If you want to change from “End” to “Beginning” or from “Beginning”” to “End, press and release the “2nd” key once, from this intermediate position. Then press and release the “SET” key once. This key is in the second column of the first row. The display will change from “END” to “BGN” or from “BGN” to “END.” Press and release the “C/CE” key to return to the normal operations of the BA II+. Clue: While the BA II+ is in the “Annuity Due” mode – “BGN” indicating that the annuity payment is due at the beginning of the period there will be displayed near the top and towards the right side of the display. If the BA II+ is in the “END” there will be no additional display items as this is the default mode. Days Between Dates With The BA II+ The Texas Instrument BA II+ will calculate the days between dates. A very nice function for accounts receivable and accounts payable aging as well as determining issues such as interest payments made or received every 30 days. To determine the days between the invoice date of January 24, 2008 and the date of March 15, 2008: 1. 2. 3. 4. 5. 6. Turn the BA II+ on by pressing and releasing the On/Off button on the upper right corner of the key pad. Press and release the yellow “2nd” key once. This key is in the first column of the second row. Press and release the “DATE” key, this text is shown above the “1” key on the key pad. The yellow text above the key is used since you are in the “2nd” keyboard options as selected in step 2. The display should show “DT1=” for Date 1. To enter the date of January 24, 2008, enter “01.2408” and press and release the “ENTER” in the second column of the first row. The display should show “DT1= 1 - 24 - 2008”. Use the up or down arrows in the third and fourth columns of row one to display the “DT2=” screen. This screen may contain a date from previous work or a default date. Enter March 15, 2008 as “03.1508” and press and release the “ENTER” key. The display should show “DT2= 3 15 – 2008”. Use the up and down arrows in the third and fourth columns of row one to display the “DBD=” screen. This screen may show a value from a previous computation. Press the “CPT” button in Page 124 Solving Accounting Principles Problems Using Excel for Windows the first column of the first row. The BA II+ should display “DBD= 51.000”, days between dates is 51. Hint: Each time a new date is entered the “CPT” process must be run again to generate the correct number of days between dates. You can also calculate the date a note, such as a 120-day note, issued January 24, 2008 is due. To do this: 1. Turn the BA II+ on by pressing and releasing the On/Off button on the upper right corner of the key pad. 2. Press and release the yellow “2nd” key once. This key is in the first column of the second row. 3. Press and release the “DATE” key, this text is shown above the “1” key on the key pad. The yellow text above the key is used since you are in the “2nd” keyboard options as selected in step 2. 4. The display should show “DT1=” for Date 1. To enter the date of January 24, 2008, enter “01.2408” and press and release the “ENTER” in the second column of the first row. The display should show “DT1= 1 - 24 - 2008”. 5. Use the up or down arrows in the third and fourth columns of row one to display the “DBD=” screen. This screen may contain a value from previous work or a default value. Enter “120” and then press and release the “ENTER” key. The display should show “DBD= 120”. 6. Use the up and down arrows in the third and fourth columns of row one to display the “DT2=” screen. This screen may show a date from a previous computation or a default date. Press the “CPT” button in the first column of the first row. The BA II+ should display “DT2= 5 – 23 – 2008”. The date a 120-day note issued on January 24, 2008 would be due. Tip: Entering the “DBD” (Days between dates) value as a negative will generate a valid response for a earlier date. If you wanted a 180-day note payable on June 30, 2008, enter “DT1” as “06.3008” and “DBD” as “-180” and then compute “DT2” to determine that the note should be issued January 02, 2008, displayed as “Tue = 1-02-2008”. Storing Values In The BA II+ A major asset of a professional calculator like the BA II+ is the ability to store data. In the example you are issuing 10 year bonds. The bonds carry a face interest rate of 8% while the market is 7%. Interest is paid semi-annually. The issue consists of 1,500 bonds with face values of $1,000 each. Your challenge is to identify the present value of the interest payments, the present value of the bonds, and the present value of the bond issue. The “Store” and “Recall” capability are major tools in this task. First, determine the face value of the bonds – 1,500 bonds X $1,000 = $1,500,000 and use the keystrokes “STO” (Store) near the bottom of the calculator in the first column, followed by pressing and releasing the “1” key. This will store $1,500,000 into the Storage Register 1. Now you will address the present value of the bonds. While the $1,500,000 is still in the display, press and release the “FV” (Future value) key to record this value into that field. Type in “0” (zero) and press and release the “PMT” (Payment) button. It is recommended that you develop a discipline of entering values in all the TVM (Time value of money) fields from left to right or right to left to ensure previous values do not enter your current calculation. This field will remain at “0” for this calculation. Type in “0” (zero) and press and release the “PV” button. It is recommended that you develop a discipline of entering values in all the TVM (Time value of money) fields from left to right or right to left to ensure previous values do not enter your current calculation. This will be your solve value in a couple of steps. Chapter 19, Page 125 Type in the market interest rate as a whole number – “7” then press and release the ÷ (division) key, enter “2” and press and release the equal sign to properly state the annual (stated) interest rate a period of interest rate of 3 ½%. Press and release the “I/Y” (Interest) key to enter the value into the interest field. Type in “10”, the life of the bonds in years, then press and release the “X” (Multiply) key in the right most column of the calculator followed “2”, the number of interest periods per year by the “=” (Equal) sign. The display should read “20”. Press and release the “N” (Period) key to enter this value into the “Number of periods” or “life” register. Now press and release the “CPT” (Compute) key in the upper, left corner of the calculator followed by the “PV”(Present value) key in the center of the TVM row. The BA II+ should respond with “PV= 753,848.8266”. This is the present value of the bonds. Press and release the “STO” button in the first column near the left, bottom corner followed by the “2”. You have now stored that value into the second storage register. To solve the present value of the interest payments, enter “0” (zero) into the BA II+ and press and release the “FV” (Future value) key. Press and release the “RCL” (Recall) key, near the left bottom corner of the key pad, followed by the “1” key. The value stored in the first memory register, “1,500,000” should appear in the display. Press and release the “X” (Multiply) key followed by the “(“ (Open parenthesis) then press and release the “8”, the face interest rate, press and release the “%” (Percentage) key, followed by the ÷ (Division) followed by “2” and then the “)” parenthesis. The display will show “0.04”. This is the period interest rate of the face or stated interest rate (8% / 2). Since you already have “1,500,000” and “X” hovering in cyberspace from the earlier step, press “=” (Equal) and the BA II+ should respond with 60,000.00. This is your period interest payment in cash. Press and release the “STO” (Store) key and then the “3” key to place this value in the third storage register. The $60,000.0000 value should still be in the display. Press and release the “PMT” (Payment) key. Press and release the “CPT” key followed by the “PV” (Present value) key and the BA II+ should display “PV= -852,744.1981”, the present value of the interest payments. The “N” (Periods) and “I/Y” fields were entered in your first calculation for the present value of the bonds and you have not changed them in this short period of time so reentry was not necessary. Press and release the “STO” (Store) key followed by the “4” to store this value in the fourth memory register. The present value of the bonds is the sum of: “CE/C” to clear the display and working register to preclude bring in an unintended value, “RCL” “2”, and “-753,848.8266” should appear. Press and release the “+” (Plus) key followed by “RCL” (Recall) and then the “4” key and the display should read “852,744.1981”. Press the “=” (Equal) sign to display the sum of these two numbers – “-1,606,593.025”. Since your display was set to four significant digits but the BA II+ needed one of those places to display a whole number it took it. This value is the present value of the bonds. While the “-1,606,593.025” is in the display, press and release the “STO” (Store) button followed by the “5” to store this value. To determine the discount, recall the value in memory register 1 by “RCL” “1” and ADD these numbers as one is a positive value while the other is a negative value, the premium of “$106,593.0248” on this bond issue. Store this value in memory register by pressing “STO” followed by “6”. Page 126 Solving Accounting Principles Problems Using Excel for Windows Now, the bond issue is stored in “1”, the present value of the bonds is stored in “2”, the cash interest payments are stored in “3”, the present value of the interest payments is stored in “4”, the present value of the bond issue is stored in “5”, and the premium is stored in “6”. The memory registers can be “overwritten” or “rewritten” by simply “STO” and the specific registry number. All the memory registers can be cleared at one time by the keystrokes “2ND” then “MEM” (the text above the “0” key), the “2ND” again followed by the “CE/C” key. The valid registers are from “0” (zero) through “9” (nine). Balloon Payments With The BA II+ The Texas Instruments BA II Plus (BA II+) respects cash and value inflows and outflows. If the present value (PV) (or principle) entered is positive indicating cash inflows or value inflows to you, then the payment (PMT) or future value (FV) will be an outflow or a negative value. However, for most uses you realize that you are getting a loan or making a loan and enter the present value (PV) as positive to save keystrokes and simply read “-691.3918” as “$691.39” as your payment or cash outflow. However, assume that in this loan example, you are buying a $35,000 vehicle, the loan term is 60 months, the interest is 6.9% annually, and you are going to make a balloon payment of $5,000 at the end of the loan with and payments are made monthly. “N” or number of periods is (5 years X 12 months) 60, “I/Y” or interest is 6.9 /12 months or 0.5750, “PV” or present value is 35000, and “PMT” or payment is 0 as a matter of discipline. Now cash flow representation is important. Since the balloon payment is cash flow out and the principle is cash flow in, if the principle is positive, then the balloon payment or “FV” – future value, must be negative. If you had entered the 35000 as a negative number representing cash flow or value flow out, then the balloon payment entered in “FV” must be positive. When 5000 is entered into the BA II+ you will need to press the “+/-” key near the bottom right corner of the key pad to convert the positive value to negative -5000. Now press and release the “FV” key to enter the negative 5000 into that field. Now press “CPT” the key in the first column of the first row and then the “PMT” or payment key. The BA II+ should return -621.13716. Without the balloon payment your payment was $691.3918, with the balloon payment your payment is only $621.3716. Had the balloon payment been put in as a positive number matching the “PV” or present value field, the payment would be -761.4121 or $761.4121. Which fails a basic logic evaluation of lower your payment level by the balloon payment. Time Value Of Money With The BA II+ The Texas Instrument BA II Plus (BA II+) utilizes the same field identities as the accounting and financial professionals in the calculation of the time values of money. On the third row of keys of the BA II+ from left to right (column 1 through column 5) you are presented with: “N” for the number of periods for the event. If you set your BA II+ up as earlier discussed with “1” in the “P/Y” and “C/Y” parameters, this value is easy to determine. If the loan is for 5 years and payments as well as interest is compounded monthly, enter the value of 5 into the BA II+ then press the “X” (times) key in the right column of the BA II+, then press “12” followed by the “=” (equal sign). The display will now indicate “60.000” representing the periods of the loan, in this example, months. Now press and release the “N” or number of periods key in the first column of the second row. “I/Y” is interest per year and, if the BA II+ has been set up as previously discussed, this is now interest per period. Since it is interest per period and the interest rate is provided as annually, enter the interest rate of 6.9 and DO NOT PRESS THE “%” KEY.. Now divide this value by the same factor as you multiplied the term by – 12. So press and release the “÷” (divide) key followed by the “=” (equal sign). The display should show 0.5750. Press and release the “I/Y” key to enter the value into the “I” or interest field. Chapter 19, Page 127 “PV” is the present value field. For this example you will remain with the basic loan and conform to discipline. Since the cash flow or value is in, it will be positive, so enter 35000 into the BA II+ and press and release the “PV” key in the third column of row three. “PMT” is the payment field. Since you are trying to solve for payment and want to ensure every field’s value, enter “0” (zero) into the BA II+ and press the “PMT” key. “FV” is the future value field. For this example you will retain your balloon payment issue. Since the “PV” or present or principle value is positive, this must be negative indicating that you are making the payment, representing cash flows out, at the end of the loan. Enter 5000 into the BA II+ then press and release the “+/-“ key near the bottom right corner of the BA II+. The display changes to -5,000. Press and release the “FV” future value key in the fifth column of the third row. Now solve for “PMT” or payment by pressing the “CPT” (Compute) button in the upper left corner of the BA II+ followed by pressing and releasing the “PMT” (payment) key and the BA II+ should respond with -621.3716 indicating our payment is $621.3716. Present Value Of A $1 With The BA II+ You need the present value of $1 with 15 periods at 15% interest and no payments, the fields would be: N = 15 I/Y = 15 PV = 0 as a matter of discipline to ensure the values in the BA II+. PMT = 0 as a matter of discipline to ensure the values in the BA II+. FV = 1 Then “CPT” and “PV.” The response should be -0.1229 which can be checked with printed present value table for validity. To find the present value of $150 with 15 periods at 15% interest and no payments, the fields would be: N = 15 I/Y = 15 PV = 0 as a matter of discipline to ensure the values in the BA II+. PMT = 0 as a matter of discipline to ensure the values in the BA II+. FV = 150 Then “CPT” and “PV.” The response should be -18.4342 which can be checked with printed present value table for validity by multiplying $150 X 0.1229 = $18.4350 and the difference is a rounding factor. Future Value Of A $1 With The BA II+ You need the future value of $1 with 15 periods at 15% interest and no payments, the fields would be: N = 15 I/Y = 15 PV = 1 PMT = 0 as a matter of discipline to ensure the values in the BA II+. FV = 0 as a matter of discipline to ensure the values in the BA II+. Then “CPT” and “FV.” The response should be -8.1371 which can be checked with printed future value table for validity. To find the future value of $150 with 15 periods at 15% interest and no payments, the fields would be: N = 15 I/Y = 15 PV = 150 PMT = 0 as a matter of discipline to ensure the values in the BA II+. FV =0 as a matter of discipline to ensure the values in the BA II+. Page 128 Solving Accounting Principles Problems Using Excel for Windows Then “CPT” and “FV.” The response should be -1,220.5592 which can be checked with printed future value table for validity by multiplying $150 X 8.1371 = $1,220.5650 and the difference is a rounding factor. Present Value Of An Annuity of $1 With The BA II+ You need the present value of an annuity of $1 with 15 periods at 15% interest and no future value. The payments are to be made at the end of the period. The fields would be: N = 15 I/Y = 15 PV = 0 as a matter of discipline to ensure the values in the BA II+. PMT = 1 FV = 0 as a matter of discipline to ensure the values in the BA II+. Then “CPT” and “PV.” The response should be -5.8474 which can be checked with printed present value of an annuity table for validity. To find the present value of an annuity of $150 with 15 periods at 15% interest and no future value. The payments are to me made at the end of the period. The fields would be: N = 15 I/Y = 15 PV = 0 as a matter of discipline to ensure the values in the BA II+. PMT = 150 FV = 0 as a matter of discipline to ensure the values in the BA II+. Then “CPT” and “PV.” The response should be -877.1055 which can be checked with printed present value of an annuity table for validity by multiplying $150 X 5.8474 = 877.1100 and the difference is a rounding factor. For the present value of an annuity due, change the BA II+ into “BGN” or “Beginning” mode (due) by utilizing the keystrokes: 1. Press and release the yellow “2nd” key once. This key is in the first column of the second row. 2. Press and release the “BGN” key, this text is shown above the “PMT” key on the third row in the fourth column. The yellow text above the key is used since you are in the “2nd” keyboard options as selected in step 2. 3. The display should show “END” or “BGN” to indicate which mode you are currently in. If this is the mode you desire, press and release the “CE/C” key on the lower left corner of the key pad to cancel the toggle process. 4. If you want to change from “End” to “Beginning”, press and release the “2nd” key once, from this intermediate position. 5. Then press and release the “SET” key once. This key is in the second column of the first row. The display will change from “END” to “BGN.” 6. Press and release the “C/CE” key to return to the normal operations of the BA II+. 7. You will have a “BGN” text string in the upper right area of the display while in this mode. Recomputing the example as an annuity due should result in a value -1,008.6713. The steps of 1 through 6 are “toggles.” Accomplishing them again should result in a “BGN” in step 2 and “END” in step 5. Chapter 19, Page 129 Future Value Of An Annuity Of $1 With The BA II+ You need the future value of an annuity of $1 with 15 periods at 15% interest and payments made at the end of the periods – ordinary annuity, the fields would be: N = 15 I/Y = 15 PV = 0 as a matter of discipline to ensure the values in the BA II+. PMT = 1 FV = 0 Then “CPT” and “FV.” The response should be -47.5804 which can be checked with printed future value of an annuity table for validity. To find the future value of an annuity of $150 with 15 periods at 15% interest and payments are made at the end of the periods, the fields would be: N = 15 I/Y = 15 PV = 0 as a matter of discipline to ensure the values in the BA II+. PMT = 150 FV = 0 Then “CPT” and “FV.” The response should be -7,137.0616 which can be checked with printed future value of an annuity table for validity by multiplying $150 X 47.5804 = 7,137.0600 and the difference is a rounding factor. For the future value of an annuity due, change the BA II+ into “BGN” or “Beginning” mode (due) by utilizing the keystrokes: 1. Press and release the yellow “2nd” key once. This key is in the first column of the second row. 2. Press and release the “BGN” key, this text is shown above the “PMT” key on the third row in the fourth column. The yellow text above the key is used since you are in the “2nd” keyboard options as selected in step 2. 3. The display should show “END” or “BGN” to indicate which mode you are currently in. If this is the mode you desire, press and release the “CE/C” key on the lower left corner of the key pad to cancel the toggle process. 4. If you want to change from “End” to “Beginning”, press and release the “2nd” key once, from this intermediate position. 5. Then press and release the “SET” key once. This key is in the second column of the first row. The display will change from “END” to “BGN.” 6. Press and release the “C/CE” key to return to the normal operations of the BA II+. 7. You will have a “BGN” text string in the upper right area of the display while in this mode. Recomputing the example as an annuity due should result in a value -8,207.6209. The steps of 1 through 6 are “toggles.” Accomplishing them again should result in a “BGN” in step 2 and “END” in step 5. Other BA II+ Functions There are other BA II+ functions explained in the Texas Instrument BA II Plus manuals and documents. Some of these include the determination of the present value of a bond issue through a single math function, net present value, internal rate of return, depreciation functions, and breakeven points. ...
View Full Document

This note was uploaded on 09/19/2010 for the course ACCT 220 taught by Professor Ullmann during the Fall '10 term at University of Nebraska Kearney.

Ask a homework question - tutors are online