ch10_12 - 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 10 INVENTORY Chapter Outline Filter Sort Master Sort Column SumIf CountIf Subtotal Vlookup Filter Another excellent Excel tool is the “Filter” function. When a worksheet is selected or when specific columns are selected and the path Data > Filter > AutoFilter is followed filters will be imposed on the worksheet by Excel. A filter is a tool to screen and restrict the presentation of data. In the “Filters” worksheet of the Filter data file on the data disk “Filter” has been established. For this worksheet, columns A and B have had the “Filter” function imposed on them. Unless restricted, through “AutoFilter”, Excel will put a filter on every column, why this may be a bad idea is shown in the filter selections of column B where every individual quantity has its own selection criteria. While this does not hurt, even if not used or appropriate, only Column A is really relevant or usable. When filters are imposed on a worksheet, Excel puts an additional button on the top of each column with a filter. This button has an arrow pointing down on it and is contained in the first row of the column. By clicking on this arrow, you activate a drop-down menu showing a complete listing of every text and numerical data entry in the column. In the Filter data file, click on the drop down arrow in column A and click on “Apples.” All of the items not meeting the criteria of “Apples” in column A are hidden and every row meeting the criteria is moved to the top of the worksheet. This compresses the data and hides the nonrelevant data. By clicking on the drop-down arrow again and selecting “All”, all of the data is presented in the order that as held before the filtering action was invoked. Utilize the filter arrow for column B and see the number of options available due to the large number of unique values contained in the column. The filter database is automatically created by Excel. As noted earlier, this may be unnecessary. Filters can be placed on every column in the worksheet and multiple criteria filtering is possible. In a worksheet containing parts locations by state, county, city, warehouse, row and rack, you could filter column A to only your part number, then filter column B to the State you wanted, column C to the county, column D to the city, column E to the warehouse. That could easily and quickly take thousands of rows down to one or two. With only one or several columns containing active filters Excel will maintain row integrity of data. That is, Excel moves the entire row when a filtering action is imposed on one column of the worksheet. The “Multiple Filters” worksheet on the “Filter” data file is an example of this capability. Filters are removed in the same manner they are invoked. Follow the path Data > Filter > AutoFilter and they will all be removed. This is called a “Toggled” function – selecting it while it is active turns it off, selecting it while it is off turns it on. Section 2, Page 59 Page 60 Solving Accounting Principles Problems Using Excel for Windows Sort Excel will also allow multiple layer sorts. In a “Sort” function no data is hidden and the order of presentation has several options. In the Sort data file there is a Sort worksheet to show this function. With Sort it is necessary select the worksheet, the columns, the rows, or the cells that you would like sorted. A caution that Excel will usually provide for you is that if selecting less than entire rows, Excel will sort only the selected cells. This may destroy the integrity of the data if completed. The “Undo” arrows may recover your data if a “Save” operation has not been initiated. The most convenient way to utilize “Sort” is with titles in row 1 and row 1 frozen through Window > Freeze Pane while row two is highlighted. This will cause Excel to freeze row 1 and help row 1 to be identified as labels. The safest way to utilize the sort function is by selecting all cells within the worksheet by clicking on the “Select All” button above the row 1 number and to the left of the column A letter. After the worksheet is selected follow the path Data > Sort and a pop-up menu will appear. Excel will usually assume that the first row of a frozen pane display is titles so titles should appear as the selection options. If your presentation shows column letter identification, simply select the “My List Has - Header Rows” option on the lower left corner of the dialog box. From this menu select Sort by > Part Nbr (Number) through the drop-down selection pane, Then by > State through the second, or middle, drop-down pane, Then by > County through the third, or lowest, drop-down pane. You can select whether you want them in ascending or descending order by clicking on the buttons to the right of each sort level as you desire. Clicking on “OK” will result in a sorted worksheet by your selected criteria. Worksheets can be sorted time and time again in different manners without restrictions. This will change the order of the rows, which, if important may not be recoverable unless a technique such as “Master Sort Column”, discussed elsewhere, is used. If the worksheet is not saved upon exiting, it will be in the same configuration and data arrangement as the last save operation point. Master Sort Column The “Master Sort Column” is a technique rather than an Excel function or formula. If you are going to sort worksheets you may be required to return them to their original order for a function such as auditing. For example, you have entered 41 inventory items into an Excel titled Master Sort Column (this is done for you on the Master Sort Column data file) worksheet for data manipulation. In the process of working with the data you discover what appears to be data entry errors that you want to verify. Once sort has been invoked on a worksheet it is not easy to restore the input or original order. So try putting a “Master Sort Column” on the worksheet and possibly additional sort columns as required for other reports, sort preferences, or users. To do this, insert a column at column A by clicking on the Column A identifier. Then right click on the highlighted column and select “Insert” from the options. This will insert a new column and “push” the highlighted column one column to the right. You can also insert a column, once a column is highlighted, through the path Insert > Columns. Excel will insert a new column and push the other columns to the right. The only time Excel will not do this is if every column to the limit of the worksheet has been utilized. Now title that new column “Master Sort” (or anything else you desire) and insert indexed numbers starting in the first data row. You can utilize the row number, such as “2”, as your first number to keep a match between worksheet row numbers to master sort numbers as shown in the Master Sort Column data file. Drag or fill this column through the data entry range. Now, whenever you need it back into data entry order simply select the worksheet and sort by the Master Sort column, column A, in ascending order, and it is back in data entry order. Suppose that the Director of Sales wants the inventory file in Part Nbr (Number) order frequently. Insert a new Column B pushing the existing column B one to the right, and label the new column “Sales Sort”, sort the worksheet by Part Nbr (Number). Now, while in sorted presentation, put sequential Chapter 10, Page 61 numbers into column B as you did in column A with the master sort. Now any time the Director of Sales wants his presentation, select the worksheet and sort by column B in ascending order. Hint: Remember that as the data within the worksheet changes, the sort may need to be run and the sort index may need to be refreshed. Any party requesting special presentation of the data can have their own sort row and these columns need not be printed by excluding them from the print area. This exclusion capability is discussed in the “Print Area” elsewhere in this book. SumIf Excel has many excellent tools to assist in mathematical valuations. Using inventory as an example, if the inventory count sheets are entered into Excel worksheets the “Sum If” (“=SUMIF( )”) function can be utilized to find the individual items and sum the total number of those parts that appears within the worksheet. This is not counting the number of times the item or value appears within the Excel worksheet, that function is accomplished through the “CountIf” function. The command string for this function is =SUMIF(Reference range, selection criteria, sum range). If the selection criteria is text rather than a number, it must be enclosed within quotation marks such as “Boxes–10”X10”X5”.” Note: Numbers such as “123-456-789” should be handled as text to assure the proper results. For example, the “SumIf” formula that would locate the number “4” within the rage of B2:B100 and then sum the values within the range of C2:C100 would be =SUMIF(B2:B100,4,C2:C100). The “SumIf” function to locate the text string of 123-456-789 within the same range would be =SUMIF(B2:B100,“123-456-789”,C2:C100). In the “Sum If” data file on the data disk there is an example of the “SumIf” function counting apples, berries, and cakes. The caution with this function is that “Apple” and “Apples” are two different text strings so data entry discipline is required. Within the “Sum If” data file is a sample of three “SumIf” formulas contained in cells G2, G3, and G4. These formulas look to column F to determine what they are each looking for – Apples, Berries, or Cakes. The range they are looking at is column A, rows 2 through 250. The values these formulas are summing are the corresponding values in column B, rows 2 through 250. Notice that there is one line identified as “Apple.” This entry, row 26, is filled and font colored for easy locating. If you sum the individual quantities and include this line in the grouping of “Apples”, you will find that your result and Excel’s results do not agree. To Excel, “Apples” and “Apple” are two distinctly different items. This is verified with other formulas on the page and subtotals for your own verification. These formulas, as provided, all utilize “Look to” formula capabilities as shown in the text presentation of the formulas in column H. This capability reduces typing, increases speed, and improves accuracy. In rows 15 through 22 of the formulas columns the text values have been entered with the required “Apples” type formatting. Excel is not case sensitive as to “Apples” or “apples” with the “Sum If” formula. Hint: When using “Sum If” for formula work where all of the items of a column will be placed and counted into one or more categories make sure that all values are counted with a technique similar to that used in cells G5 and G9. This reduces the probably of missed data. CountIf The “Count If” function of Excel will count the number of occurrences of the specified value. In the “Count If” worksheet within the “Count If” data file the function is shown in operation. The formula is “=COUNTIF(range for the count, reference value).” The Count If range can be cells, single a column, multiple columns, a single row, or multiple rows. This is shown on the “Extended Count If” worksheet of the “Count If” workbook. Page 62 Solving Accounting Principles Problems Using Excel for Windows Subtotal “Subtotal” is a tool that requires more show than tell to explain. What does “Subtotal” do? It tells you, for example, all of the flight time Adam, Ann, and the rest of the pilots have flown, and what airplanes they flew in as well, and the flight time for the aircraft and the total flight time. “Subtotal” requires that the worksheet be sorted by the basic order for the subtotaling operation since “Subtotals” only works on the rows format and has no sorting capability within itself. The “Subtotal” workbook on the data disk was built for this purpose. With the “Subtotal” data file you will need to first sort the data by “Charged Pilot”, then by “Aircraft”, then by “Date.” This will structure and sequence the data to be “subtotaled” by Excel. Hint: If you click and highlight your data area to confine the process and avoid taking in extra rows and columns, Excel may give you an advisory about header rows when you initiate “Subtotal.” Usually simply acknowledge the message if you know what caused it. If you simply click on a cell and then follow the path Data > Subtotal this advisory message will usually not appear, but you may end up with extra rows in your data matrix. Tip: Identify the data rows by highlighting them and accept / acknowledge the message. Highlight the rows from row 1 through and including row 147. (This will cause an advisory message about header rows that is not a factor in this data structure.) Then follow the path Data > Subtotals and the “Subtotal” dialog box will pop up. Within this dialog box, set the “At Each Change In” window to “Charged Pilot” through the drop down menu list accessed by clicking on the drop down arrow to the right side of the window. Other options are available but the data file must be sorted to handle the selection sequence – if you sort the data file by “Date” and then subtotal by changes in “Charged Pilot” the resulting matrix will be useless. In the “Use Function” window ensure that it reads “Sum” There are other mathematical operations available and you should check these to see your options but you will be using “Sum” in this example. In the “Add Subtotals To” window scroll up or down to see and select “Time” from the selection. Ensure that all other items are clear of checkmarks. Then ensure that “Summary Below Data” is checked. Since this is the first subtotal process on the worksheet, “Replace Current Subtotals” can be checked or unchecked. This, while not important now, will be important shortly. Subtotal Dialog Box Selections Before you click “OK”, what has been done is, you have sorted (earlier) a data matrix into the order of “Charged Pilot”, “Aircraft”, and “Date.” Now you are telling Excel to determine the amount of hours each pilot has flown for this data period. Click “OK” on the dialog box and watch the worksheet change. The worksheet has new “scroll” bars (discussed soon) to the left and new rows inserted. These rows contain the data you asked for in the first run of the “Subtotal” New Scroll dialog box. Each pilot’s total flight bars time is now in the “Time” column. Looking at the screen print of the New Rows results you can see that “Adam” Chapter 10, Page 63 flew a total of 2.0 hours and Ann flew 7.4 hours. Now, add a data management feature to the worksheet. In the “First Subtotal” column enter a “2” in row 2, in row 3 enter a “3”, in row four enter a “4” and then highlight these three cells and drag and fill the cells down by grabbing the bottom right corner of the highlighted box when your cursor becomes a heavy dark cross. You should be dragging down through the bottom of the data matrix, now row 210. You may use this later. The next subtotal process will address the next issue. Each aircraft has its own billing rate and numerous pilots flew several aircraft during the period. So, select the rows from 1 through 210 – the subtotal process increased the number of rows as it added Adam’s and Ann’s subtotals as well as everyone else’s. Follow the path Data > Subtotal again. This time in the “At Each Change In” window select “Change in Aircraft”, ensure that the function is still “Sum”, and the “Add Subtotals To” is “Time.” This time, you do not want to remove the previous subtotals so ensure at there is no checkmark in the “Replace Current Subtotals” box. If there is or if there is a faded one there, mouse click it until the box is clear. Now click “OK” and watch the worksheet change again. Now put a “2” in row 2 of the “Second Subtotal” column, a “3” in the third row, and a “4” in the fourth row. Then highlight these three cells and drag and fill the column through the data matrix and down through row 303. Excel and Subtotal has added numerous rows to the basic file. Use any of the “Save” procedures to save the workbook. Now the worksheet has more “scroll” bars to the left and more inserted lines. In this process, use the scroll bars to the right and scroll through the data. You will see that, where a pilot has flown more than one aircraft in the period, such as Deanna, Excel has subtotaled her flight time in N72RAS as 4.4 hours, N78CHS as 1.5 hours, and N81JLS as 0.9 hours. Excel also tells you that Deanna flew a total of 6.8 hours. Another data management issue, column J is titled “Names.” In the J1 cell, enter the formula “=IF(D2="",J1,D2)” and drag it down through row 301 of the matrix. If you drag it into row 302 in this example you will “credit” Zoë with the grand totals. Not harmful, just incorrect. This column will be discussed shortly. Save the workbook to protect and preserve your work. Now, those new “scroll” bars to the left. At the top of those bars are some new blocks with numbers indicating levels of data that can be presented. In this example, level 4 is “Full Detail Shown”, the default screen. Click on the level 3 box in the data file and you have a good presentation to do billing from. Detail is gone but subtotals are clean and crisply presented so you do not need to find the subtotal within the detail. Printing at this point will be what you see – summary down to pilot and aircraft. Make sure you define the print area as you do not need sort columns and unnecessary data. Click on the level 2 box and you can verify the total hours flown by each pilot, and you are not looking at the subtotals by aircraft detail. Click on the level 1 box and you are told that your total billing for this period should be 183.7 hours. A nice cross check. Click back into the level 4 box to get full detail shown again. By clicking on the minus signs on those new scroll bars data will disappear through their associated rows and the minus sign will be replaced with a plus sign. The horizontal position or orientation in relation to the level boxes at the top clue you into which data will be hidden or exposed by your clicks. Click into the “Time” cells of any of the new rows where data appears and you will see new Excel formulas within the cells. In cell G3 Excel and the “Subtotal” has inserted the formula “=SUBTOTAL(9,G2:G2).” The “9” within the formula tells Excel this is a summing formula. The range is G2 through G2. If you open Excel help and type “Subtotal Worksheet Function” you will see the numerical drivers for the other subtotal functions – if you are interested. This worksheet contains a lot of formulas and dynamic worksheets can lead to problems. To address this minor issue, click into the Subtotal’s worksheet and select all cells by clicking on the “Select All” button or the keystrokes Ctrl (Control)-A. Then copy the worksheet through any of the numerous copy Page 64 Solving Accounting Principles Problems Using Excel for Windows methods such as Ctrl (Control)-C, the copy icon on the tool bar, or through the path Edit > Copy. Now click into the “Invoicing” worksheet of the Subtotal workbook. Make cell A1 the active cell. Right click cell A1 cell, select “Paste Special” from the options and select “Values” from the dialog box. Now the Subtotals worksheet is pasted into the “Invoicing” worksheet without the formulas. Select row 2 and then follow the path Window > Freeze Pane. If row 1 is already frozen through the paste process, this is not necessary. This will “freeze” row 1 as a header row. To remove subtotal bars from the “Invoicing” worksheet follow the path Data > Subtotal again for the pop-up dialog box. This time select the “Remove All” option and click “OK.” Excel removes the subtotal features. No values are lost since “Subtotals” will only remove formulas it placed and the new “Scroll Bars” it inserted and you removed those formulas with the Paste Special > Values operation just performed. Now the reason for that “Name” and those various “Sort” columns. Select the entire worksheet in any manner you like and follow the path Data > Sort. Ensure “Header Rows” is identified on the dialog box. Then select “Sort Column”, (column A of the worksheet) which represents the original data input sequence. Select “Names” in the “Then By” window. The last “Then By” window can be left blank. Then click “OK” and watch the results. You now have original data entry sequence in rows 2 through 147. In rows 148 through 303 you have pilot subtotals by aircraft and totals flight times – without formulas. However, notice that the presentation of data is not real good. The detail of aircraft is in column B and the hours are in column G. To solve this issue, write the following formula in cell K148 “=IF(C148="",CONCATENATE(D148," ",G148),CONCATENATE(J148," ",C148," ",G148))” Remember that “” is the null value while “ ” is a space. This formula will look at cell D148 to see if there is anything there. If there is not the formula will “Concatenate” or make a text string out of the contents of cells D148 and G148 separated by a space. These cells contain the pilot’s name with the word “total” inserted by the subtotal operation and the total flight time. If there is anything in cell D148, this formula will “Concatenate” or make a text string out of the contents of cells J148, C148, and G148 (in that order) and separate them by a space. These cells contain the pilot’s name, the aircraft identification, and the flight time for that aircraft by that pilot. Since the formula is relational, it can be dragged down through row 301. Since it is a formula, copy column K and utilize Paste Special > Values to paste it back in as values. Now the text strings can be moved or copied and they will retain their value. This gives nice summary presentation for invoicing and records. Save the data file in its current format to protect your work to this point. Now that you have pilot invoicing detail on the pilots, you need activity by aircraft. Click into the “Subtotals” worksheet and follow the path “Data > Subtotals to the “Subtotal” dialog box. Click on the “Remove All” button. None of your data is lost since you copied it as “hard values” to the “Invoicing” worksheet. Select all the cells and sort the “Subtotal” worksheet by “Aircraft” and “Then By” “Tach Out.” This will sort the data by the aircraft and by the sequence of utilization in the event that the data entry order was not sequential. Once again populate the “First Subtotal” column’s row two with “2”, row three with “3”, and row four with “4” replacing any data there and then drag down the column through row 147 (in this example) to populate the cells. Select the entire worksheet or all the rows of the data matrix. Then utilize the “Subtotal” function with “Change In” “Aircraft”, “Use Function” of “Sum”, and “Add Subtotal To” of “Time” and remove all other checkmarks in this category. Then click on “OK.” Subtotal will again insert new features on the worksheet. In the “Second Subtotal” column, once again populate row two with “2”, row three with “3”, and row four with “4” and then drag down the column through row 154 (in this example) to populate the Chapter 10, Page 65 cells, replacing any data existing there. If there is data below the current data matrix from an earlier process, delete or erase it. Select the entire worksheet and “Copy” the worksheet. Now select the “Aircraft Activities” worksheet. Utilize Paste Special > Values in cell A1 to place the data and remove the dynamic or live formulas into the worksheet. Then remove the “Subtotal” function through Data > Subtotals > Remove All. Now, ensure all cells are selected and resort the worksheet with “Sort By” set to “First Subtotal” and “Then By” set to “Second Subtotal.” The second “Then By” can be set to “None.” The result will be a sequential presentation of aircraft utilization by aircraft in rows 2 through 147 and a nice summary of aircraft utilization in rows 148 through 153. You could place the formula “=CONCATENATE(C148," ",G148," hours")” in cell J148 (of this example) to make a nice text string presentation and drag that formula through row 153 if desired. If you do, consider Copy > Paste Special > Values to remove the formulas. Save your data again to protect your efforts. You can return to the “Subtotal” worksheet and removal all the “Subtotal” functions and resort by the “Sort Column” which will return your data matrix to input order. No data will be lost since the aircraft activity data is now on the “Aircraft Activities” worksheet as “hard values.” Vlookup Vlookup is another powerful presentation tool of Excel that takes more show than tell to explain. There is a “Vlookup” data file for this explanation. The “Vlookup” formula is located under the “Lookup and Reference” category. In cell G5 of the “Vlookup” worksheet the formula looks like this: =VLOOKUP($F5,$A$5:$E$6,2,FALSE) The “decode” of this formula is that cell F2 contains the “Seeking value” that Excel and “Vlookup” is seeking in the data matrix. The range $A$5:$E$6 defines where Excel and “Vlookup” are seeking a match. Excel will only look DOWN the first column, the A column in this case, to find the match even though the matrix extends to column E. Hint: Excel does not put absolute references into the formula so these were inserted so the formula could be dragged down the “Vlookup” worksheet. The absolute reference on the “seeking value”, the $F5 was manually absolute referenced by column so the formula could be copied right into then next two columns. The “2” is telling Excel to return the value found in the second column of the data matrix on the same line if it finds a match to the “seeking value”, the value contained in cell F5 in this example. This is not column B of the worksheet except by coincidence. Excel counts columns from the left most column of the data matrix towards the right. This value will not change as the formula is copied or dragged from cell to cell since it is not “relational.” The “False” tells Excel and “Vlookup” that if you do not find an exact match to the “seeking value”, return nothing other than an “Alert” message. If this field is left blank Excel and “Vlookup” will return the preceding value to the seeking value not to exceed the seeking value. This can be dangerous and, as an accountant, do you want formulas returning unknown assumptions? The “false” declaration will return a #N/A# if no exact match to the “Seeking value” is found in the data matrix. On the “Vlookup” worksheet there is are several data matrices so the data matrix identification changes with the “Vlookup” formula location and purpose. In cells G5 through H14 “Vlookup” is looking at the matrix defined by “$A$5:$E$6” which allows the part to be identified as a pen or pencil part. In cells J5 through K14 “Vlookup” is looking at a matrix defined by “$A$9:$E$12” which defines the finish of the pen or pencil. In cells M5 through N14 “Vlookup” is looking at a matrix defined by “$A$15:$D$16” which informs the part reader if the part is an upper or lower assembly of the pen or pencil. In cells P5 through Q14 “Vlookup” is looking at a matrix defined by “$A$19:$E$27” which defines the actual pen or pencil part. Page 66 Solving Accounting Principles Problems Using Excel for Windows When the formula is written in cell G5 as “=VLOOKUP($F5,$A$5:$E$6,2,FALSE)” it can be dragged down the rows through 14 and maintain correct orientation and alignment. If you had not made data matrix absolute reference, when you dragged the formula from cell G5 down to G14, the data matrix rows would have indexed accordingly and alignment would have been lost. Now you can copy the formula in cell G5 to cell H5. If the column reference of F had not been absolute, it would have incremented to the right by one and that integrity would have been lost since you are not seeking a match to the value in cell G5. Now you can change column value within the “Vlookup” formula to return from “2” to “4” and the formula will return the value in the fourth column of the matrix when it finds a match. Since this matrix contains merged cells – columns B and C were merged into one column, the next column over is considered column number 4. When this change is made, the formula can be dragged from row 5 down through row 14 and it will maintain proper alignment. Now the formula can be written into cell J5 as “=VLOOKUP($I5,$A$9:$E$12,2,FALSE)” and dragged down and then copied from cell J5 into K5 and edited to seek the fourth column and dragged down through row 14. Since the formulas are “live” or “dynamic”, you can change any of the values that the “Vlookup” formula is seeking and you can see the results change almost instantly. Written into the matrix defined by G2 through I20 is a grouping of “Vlookup” formulas looking at this feeder matrix. Replacing a number in column F will cause the “Vlookup” formulas in columns G through I to scan the first column and first column only of the table defined in the formula as A1 through D71 for that number. If the function finds an exact match, as controlled by the true/false statement, Excel will return the values of the second column of the table in column G, the value found in the third column in column H, and the value found in the fourth column in column I. Tip: Important issue with “Vlookup.” If you are seeking a value that appears in the data matrix more than once “Vlookup” will complete its search on the first value found. If you are using the “True” in the formula instead of “False”, make sure your data is sorted in ascending order since “Vlookup” will complete its search upon finding the first value equal to or exceeding its “seeking value” and return the (first) the exact match value or (second) the highest value not to exceed the seeking value. Even if an exact match is lower in the data matrix. There is an area on the “Vlookup” worksheet that shows how “Vlookup” can be used to assemble a mailing label by entering one number in cell G37. Reading the formula will tell you where the “seeking value” is, the data matrix, and verification that you only want exact matches. Hint: “Vlookup” is structured for data in a vertical format. If your data is in a horizontal format, utilize “Hlookup” which works in the same manner for data in a horizontal format. Chapter 11 DISPLAY Chapter Outline Freeze Panes Split Panes Conditional Formatting Displaying Zero Values Gridlines On The Screen Hiding Columns And Rows Hidden Indent Within A Cell Truncate Workspaces Freeze Panes Freeze panes is actually “freeze rows or columns.” What this process does is keeps a specified set of rows, columns or both rows and columns available and viewable at the top for rows, at the left for columns, or at the top left for rows and columns while you move throughout the worksheet. Open the Freeze Panes data file. In this workbook there are three tabs. On the “Rows” tab, the rows 1 through 3 were “frozen” in view by clicking onto the row 4 identifier then following the path Window > Freeze Panes. At this point Excel will “freeze” all of the rows above the line highlighted since there were no columns selected. As you move throughout the workbook you will notice that the first three rows are always in visible. This is excellent for titles. On the “Columns” tab, the “B” column was highlighted by clicking on the column identifier. Then the “Freeze panes” was invoked through the path Window > Freeze panes. Since there were no rows identified the column or columns left of the highlighted column will remain visible as you move throughout the worksheet. On the Rows and Columns tab the cell B5 clicked into and made active. Then the freeze pane was invoked through the path Window > Freeze panes. Since a cell with both column and row identity was active at the time the process was invoked, the rows above row 5 and the columns left of the B column are now “frozen” into place. As you move left, column A will always remain in view. As you move down, rows 1 through 4 will always remain in view. This provides you with column and row identity. This capability is excellent for large worksheets where columns and rows are both elements of identity such as multiple salesmen in rows and multiple sales items in the columns. By freezing the columns, the salesman’s identity will always be visible, by freezing the rows, the title of the sold items will always be visible. While Freeze Pane is invoked on a worksheet Ctrl-Home will normally take you just below the frozen rows and just to the right of the frozen columns as the frozen columns and rows are considered title areas not normally used for data. You can still enter these areas by simply mouse clicking into them. Section 2, Page 67 Page 68 Solving Accounting Principles Problems Using Excel for Windows Split Pane The “Split Pane” function is invoked on the Demo File upon opening. This function splits the single worksheet into two or four separately displayed panes. This view is controlled through the path Window > Split and, if invoked, Windows > Remove Split. To invoke a horizontally split screen presentation, place your cursor on the row numbers at the left of the display pane and click into it to make it active. Excel will highlight the entire row. Then follow the path Window > Split from the menu bar. Excel will split the screen horizontally and provide a scroll bar for each pane. In this manner the data from the exercise or problem can be read in one pane while accomplishing tasks in the other pane. To demonstrate a further enhancement of this function, set your upper pane to show rows 9 through 15 of the demo file while showing rows 39 through 42 in the lower pane. You can recreate the journal entry by clicking into cell B39 of the lower pane and typing the “=” (Equal sign) (without the quotation marks), then click your cursor onto cell B9 of the upper pane. When you press and release the “Enter” key, “Tab”, or click into another cell, the date of “Jan 2” appears in the date cell of the journal entry, saving you time and reducing the risk of data error through typing. Since the account to be debited is Cash, you can do one of several things, you can type “Cash” into the debit account title area or you can do another “Look to” formula for the account title in the general ledger. Examination of the general ledger will show that the “Cash” account title is contained in cell B77 while the cash account number, used when posting, is in cell J77. So try the formulas “=B77” and “=J77” in the appropriate cells of the journal entry to preclude typing in those values. You can use the “=” sign and then simply click on the cell B77 or J77 to complete the formula if you desire. This capability of split screens is handy and precludes excessive scrolling and losing your place while working within the templates. If you place your cursor near to the center of the display and make a cell the active cell and then invoke split screen, you will end up with four panes to deal with. In this case, the control of the display areas is interactive between the left & right and upper & lower panes through the scroll bars. Becoming comfortable with the appearance and proficient with the use of split screens greatly reduces your time to accomplish the tasks and reduces scrolling. Once split screens are invoked, you can drag the border identifiers around to change their position and sizes. Conditional Formatting Excel will accept several levels of conditional formatting for a cell under the path of Format > Conditional Format. On the Conditional Formatting data file there is an example in cell A1. If the value is less then 1 the cell appears normal, if the cell contains a value between one and ten, the formatting is horrible, if the value is greater than 10 the formatting gets worse. While this was done for effect, if you were inputting mark-ups for retail and wanted no percentage less than 50% nor more than 150%, you could format the cell to go red if the percentage is not within this range as cell A3 is formatted to. By examining the drop-down menu selection choices of the operator, defaulted to “between” you will see that you have a wide range of operators to work with. You can impose up to three levels of conditional formatting on a cell by clicking on the Add button at the bottom of the dialog box. If you have imposed conditional formatting on a cell you can remove one or more levels of formatting by following the path Format > Conditional Format while the target cell is selected and clicking delete on which level of formatting you want removed. Displaying Zero Values The “Zero values” option on the “View” tab controls whether or not a zero (0) will be displayed whether the zero is entered as a “hard value”, one entered directly by keying in “0” (the numeral zero), or it is a “soft value” of “0” (the numeral zero) as a result of a formula action. If selected, checkmark in the box, zeros will be shown, if not selected (no checkmark in the box), zeros will not be shown. An interesting, and sometimes, confusing event occurs when “Zero values” are not selected for display. The user will see Chapter 11, Page 69 a “0” (the numeral zero) when he expected to eliminate these values from the presentation through his “Options” setting. This happens when the actual value in the cell is below the level of presentation but not equal to zero. If data entry or a formula calculation results in a value below the displayed decimal places Excel will display the “0” or “0.00”(the numeral zero) value – not because it is zero but because it is smaller than displayable. If the decimal setting is set to 2 significant digits such as 0.00, the value of 0.00 would not result in a presentation if the “Zero values” option is deselected. However, if the value in the cell were 0.001, the presentation of Excel would be “0” or “0.00” since this is a value not equal to zero and it is below the displayed value setting. If Excel is set to two decimal places and the actual value is 0.005, then Excel will show 0.01 as it rounds up the value for presentation. However, it will utilize the real value of 0.005 in any calculations. Gridlines On The Screen The gridlines seen on the computer presentation screen are controlled through the “View” tab. These gridlines are the vertical and horizontal lines on the screen that create the cells. These gridlines are those seen only on the screen presentation of Excel and greatly aid you in maintaining a positional reference within the rows and columns of Excel, not those on the printout. Gridlines on the printout are controlled through “Page Setup” dialog box. Below the selection for gridlines is the ability to change the gridline colors through the “Gridlines colors” dropdown window. This selection will usually remain selected except when using Excel for a screen presentation. Hiding Columns And Rows Excel will allow you to hide a column, columns, a row or rows. The methodology is to highlight the column, columns, row, or rows you want hidden. (For this operation, these areas must be adjacent and continuous.) Then follow the path Edit > Hide from the tool bar or right click the highlighted area and select “Hide” from the pop-up menu. Once the cell or cells are hidden you will see that the column identifiers and row numbers do not appear to follow the A, B, C, 1, 2, 3 sequence. As shown on the “Hiding Columns And Rows” data file you can see that columns B, D, and F as well as rows such as 5 and 6 are hidden. If you examine the borderlines between the column identifiers and the row identifiers you will see a slight difference in their presentation if there are hidden columns or rows there. These cells remain active in all formula and will still be active for functions like Go to, Find, and Find and Replace. This technique is excellent for extended calculations that are not desired in the presentation. However, copy and paste commands are active on these cells and caution needs to be exercised when a cell or cell is hidden. To unhide a column(s) or row(s) you have several options. Highlight the predecessor through successor columns or rows that border the hidden columns or rows through a sweep operation and follow the path Edit > Unhide from the menu bar or right click the highlighted area and select “Unhide” from the pop-up menu. There is also “Hide” and “Unhide” options through Format > Column or Format > Row from the menu bar. In the “Hiding Columns And Rows” data file there are numerous areas for you to hide and unhide. Hidden The “Hidden” capability within Excel is controlled through the “Protection” tab of the “Format Cells” dialog box. The path to this tab is right click on a cell and select “Format Cells” from the pop-up menu then the “Protection” tab on the “Format Cells” dialog box or follow the path Format > Cells to the “Format Cells” dialog box and select the “Protection” tab. The “Protection” data file can be utilized for this section. Page 70 Solving Accounting Principles Problems Using Excel for Windows Assume you are giving a presentation and using Excel as a presentation tool. The worksheet is extensive and has numerous large formulas contained within it. The object of the presentation is to convey the information, not have the audience to stare in awe of your formula construction while you are trying to convey your message. So you can make the formula bar and formula input window disappear by following the path Tools > Options > View and remove the checkmark from “Formula Bar.” You can right click the menu bars and uncheck all of the unwanted and unneeded menu and tool bars and make them disappear. You can format your cells to “Hidden” by highlighting an area of cells or the entire worksheet and right clicking to get the pop-up menu and select “Format Cells” or follow the path Format > Cells and then format the cells to “Hidden” by insuring a non-faded checkmark is in the “Hidden” box. Now invoking worksheet protection through Tools > Protection > Protect Sheet will minimize the appearance of formulas as you click on the worksheet. The results of the formula are still shown. By formatting any unnecessary for presentation intermediate formulas and unwanted text and cells to a text coloring the same as the background or fill coloring before protecting the worksheet these unnecessary items will cause less distraction as they will be hidden - white ink on white paper type printing. To remove this later simply select the entire worksheet and format all text coloring to “Automatic” or whatever color you choose through the “Format Cells” dialog box. Tip: You can also use “Full Screen” through the path View > Full Screen. There is an icon to return to “Normal” view. If you close that icon off your screen for viewing purposes, you still have the main menu bar at the top of the screen and can follow the path View > Full Screen, a toggle switch, to get back to your normal default view. Clue: The Excel scroll bars will tell you the general data area of a worksheet. If you open a worksheet and the scroll bars fill the slide area, you are generally looking at the total data entry area. If the scroll bars are little bars in a large slide area, the data area is rather large. Excel remembers where the data is and allows you to use the scroll bars to get to it. And, even a “ ” (space) inserted 250 rows below the last data row and 50 columns to the right of the last data column will cause the scroll bars to change and represent this placement. Indent Within A Cell To indent within a cell without using spaces use the Indent icon on the tool Increase and Decrease Indent bar. Each click on the icon moves the start of text and numerical data about 2 spaces. Remember that by default text is left justified and numerical data is right justified. Asking Excel to indent numerical data to the right in a default formatted cell may result in an interesting move. The Indent Within A Cell data file is available for you to check the operation of the Increase and Decrease Indent buttons. Column A is default justified left because it contains text. Column B is specifically formatted as right justified even though it contains text. Column D is specifically left justified even though it contains numerical data while column E is default right justified as numeral data. Click into various cells in these columns and click on various combinations of the Increase and Decrease Indent buttons and watch the Excel response. Hint: Excel will indent a cell that contains a formula looking to another cell or computing a value. The exercise and problem templates provided on the data disk are set up to have the “Look to” formula or function such as “=F12” used extensively to save keystrokes and reduce errors. However, how do you handle journal entries with a “Look to” reference? On the “Indent Within A Cell” data file in rows 2 through 5 of column H is a mini chart of accounts. The task is to purchase $800 in supplies for cash and Chapter 11, Page 71 make the journal entry in cells H8 and H9 with the values in columns M and N. Utilizing the “Look to” formula of =H4 in cell H8 you get the presentation of “Supplies” as the debit. In cell H9 you enter =H2 for Cash but it is flush with the left side of the cell while as a credit account it should be indented. Click on the Increase Indent button and it is. Now enter the value of $800 in cell M8 and have N8 look to cell M8 to ensure that the cells are always the same. Now, starting at row 14, purchase $1,000 in supplies by paying cash of $250 and the balance on account. Utilize Increase Indent to get the proper presentation of credit entries and formulas to have the cash paid subtracted from the total purchase for the value on account. Many of the cells within the exercises and problems supplied on the data disk have indent functions imposed on them in their construction for proper presentation. Truncate Truncate reduces the number of significant digits from one calculation to another. Within the “Truncate” data file on the data disk, is a math computation that was accomplished to determine the flight time for each flight by each pilot. This formula took a value such as (Tach In – column E) tachometer (engine) hours of 553.0 as recorded at the completion of the flight and subtracted the (Tach Out – column D) beginning tachometer (engine) hours of 551.4 to determine that Calvin should be charged with 1.6 hours for this flight as shown in cell F2. This same formula is also in G2. The formulas in column G was then copied and with Paste Special > Values turned into hard numbers. When those hard numbers are looked at, the values don’t make sense. The formula values are 551.4 hours subtracted from 553.0 hours and the results should be 1.6 hours but the answer as shown in Excel to fourteen significant digits is “1.60000000000002”. To avoid passing values that appear this way you can use the “Truncate” formula as written in cell I2, “=TRUNC(G2,1).” This formula will take the value in cell G2 and reduce it to one significant digit as instructed by the “1” following the cell reference and comma, trimming those extra zeros and that inappropriate “2” from the number. However, this is once again, a formula so it needs to be copied and pasted through Paste Special > Values to eliminate this presentation. To save the steps, you can use an embedded formula such as shown in column J, “Calc Time 2” to do the job in one process. The embedded formula is “=TRUNC(E2-D2,1)” which truncates the results of subtracting the out time from the in time and reducing or trimming the significant digits to one in a single step. You now need to copy and “Paste Special > Values” only column J to complete the process of eliminating formulas for your worksheet. The balance of the worksheet in columns I, J, and K are available for your practice. Note: The use of “Precision as displayed” through the path Tools . Options > Calculations addressed part of this problem but removes non-displayed values and detail from the workbook. Workspaces A workspace within Excel is a structure of one or more workbooks within a preformatted presentation. The example of a workspace, shown below, is the “Workspace Example.xlw” that accompanies this text. Notice that the workspace extension is “xlw,” this extension is viewable in the default opening dialog box of Excel. By opening this single “structure” you actually open all five workbooks – Description.xls, Unit of Measure.xls, Department.xls, Agent.xls, and Workspace example formula.xls contained within the workspace in a single action. Since these files are closely related, this is very convenient. The workspace example formula contains a series of Vlookup formulas, covered in detail in the vlookup section, that operate in relation to the “A” column of the Workspace example formula workbook. This series of formulas looks at the other four workbooks to get information. Since they are all related, seeing them at one time is handy. And by opening the workspace – a special structure within Excel, you have access to all five workbooks. This workspace looks like this: Page 72 Solving Accounting Principles Problems Using Excel for Windows A workspace is created by sizing and positioning all of the applicable workbooks and worksheets in the manner you desire. In this presentation, the Description workbook was reduced in size and positioned in the upper left corner of the presentation by dragging it there with the mouse and changing its size by dragging its borders. Then the Units of Measure workbook was reduced in size to match the height of the Description workbook, a similar width, and then positioned next to the Description workbook through a dragging operation. After that the Department and Agent workbooks were reduced and dragged into place in a similar manner. Lastly, the Workspace example formula workbook was sized to fill in the rest of the space on the presentation window. Once all were positioned, the path File > Save Workspace was followed. The dialog box is very similar to the Save and Save As dialog boxes where you can change the save location and provide a name for the workspace. Under “Save as type” the only option is “xlw” since this is a special purpose extension for Excel. Once saved, the five individual files can still be opened individually through the normal opening procedures of Excel and can be updated or modified as appropriate and then saved. Their presence within a workspace does not preclude normal access and operation of the workbook. However, when all workbooks – i.e. the workspace is required, following the normal opening paths such as File > Open, the key strokes Ctrl-O, or clicking on the Open File icon on the tool bar will present you with the Open dialog box and the workspace name will be presented there. Double-clicking on the workspace name will open the workspace and all the contained files. While working in the workspace, you can maximize or minimize individual workbooks by clicking on the maximize or minimize buttons of that workbook. The active workbook is controlled by you clicking into the workbook you desire. If you desire to make an entry into the Description workbook, click anywhere on the Description workbook and the banner or title bar will become bright while the others will be faded. This indicates that the Description workbook is the active workbook. You can now click into the worksheet and maneuver around in the normal manner of movement. As stated, if the size is too small or it is simply easier to work with a larger Chapter 11, Page 73 workbook, use the maximize button in the upper right corner of that workbook to increase its size. while the workbook is active within the workspace, all of the menu and taskbar commands are applicable only to that workbook allowing you to save or format the workbook as required or appropriate. It is important to remember that a workspace is really a structure that contains other workbooks. There are not restrictions placed on the individual workbooks because they are part of a workspace. Upon closing a workspace Excel will normally ask you if you want to save any of the contained workbooks that you have changed since the last save or opening action. Chapter 12 ANSWERS Chapter Outline Formula Auditing Formatting Cells Merge Cells Formatting Within A Cell Goal Seek Null Value Or And If Statement Formula Auditing Excel has a very useful aid for assisting you with the checks of your formulas and logic. The “Formula Auditing” function is controlled through the “Formula Auditing” tool bar or menu. To display this tool bar, right click on the menu bars at the top of the Excel screen, from the pop-up menu select the “Formula Auditing” option from the lists. When this tool bar appears place and hold your cursor over the various icons and Windows will display the function of the icon. The icons of primary interest are “Trace Precedents” and “Trace Dependent.” Click into cell C3 of the “Formula Auditing” data file on the data disk to make cell C3 the active cell. Then click on the icon for “Trace Precedents” and Excel will display arrows from all of the cells that feed this cell data. This is shown on the screen print. If the source cell is a formula, Excel will not “trace through” to that cell’s source. To be effective, the “Trace Precedents” must be activated from a cell with a formula that references at least one other cell. To remove the audit arrows you can click the eraser on the tool bar and all audit arrows will disappear, regardless of where your active cell is. To make only one cell’s audit arrows disappear, click into that cell and click the icon with the negative signs for the action – dependent or precedent. Click into cell A1. While cell A1 is the active cell, click on the “Trace Dependent” icon on the “Formula Auditing” tool bar. A single arrow will appear from cell A1 to cell C3 showing that the data flow is from cell A1 to cell C3. To see that cell A3 is also a source cell for cell C3 you can either click into cell A3 and click onto the “Trace Dependent” icon or click into cell C3 and click on the “Trace Precedents” icon. Once again, the trace icons with negative signs make individual arrows go away, the eraser on the tool bar makes them all go way. Try this tool on some of the workbooks and worksheets provided on the data disk if the logic or flow of data is confusing. You can also access the “Formula Auditing” tools through the path Tools > Formula Auditing from the main menu bar. With this path you can select a single event or elect to display the tool bar. Section 2, Page 74 Chapter 12, Page 75 Formatting Cells Excel has several ways to format cells and the data within a cell. To format a single cell, click into the cell to make it the active cell. If the format is intended for a single range of cells, click into the upper left, upper right, bottom left, or bottom right cell and sweep the range with the left mouse button down, highlighting and selecting all of the cells in the range. Formatting can be applied to more than one cell or one range by holding the Ctrl (Control) key down while selecting the sequential cells or ranges. Formatting can also be applied to a single column by clicking on the column identifier, a single row by clicking on the row number, or on multiple columns or rows by clicking on the column or row and then sweeping to right, left, up, or down to highlight all the columns or rows or holding the Ctrl (Control) key down while selecting non adjacent columns. Once the cell or cells are selected, right click into the highlighted cell or cells and Excel will present you with the “Format Cells” pop-up dialog box with the formatting options available at the time. That dialog box appears below: Format used throughout most of the Excel templates: Number > Currency Decimals Ø (zero) or 2 as appropriate. $ English (United States) or None as appropriate Negative numbers as red and contained within parentheses. From this dialog box you can select any of the many formatting features available at the moment. If a feature is grayed out or faded, that feature is not available at the moment. For the Excel templates, the formatting is generally Currency with either Ø (zero) or 2 (two) decimal places as appropriate. Occasionally 4 or 5 significant digits is set as required by the exercise or problem. Dollar signs, controlled through the “Symbol” window, are only shown when needed. In selecting numerical formatting Excel positions the right hand number appropriate for the formatting selection as to how negative values are presented. Provided below is a display of numerous Excel formats in positive and negative numbers. A close examination of this screen print will reveal that not all of the numbers are aligned in the same vertical plane at the right side of the column. This occurs when Excel is formatted to display negative numbers with a preceding “-” negative sign in some cells while other cells display negative numbers contained within parenthesis “( )”, Excel will position the numbers farther to the right if a preceding negative sign is used since the space is not needed to the right in the event of the closing parentheses. The solution for a ragged display of numbers that do not right align correctly is to check that the formatting of the numbers is all the same by selecting and highlighting Page 76 Solving Accounting Principles Problems Using Excel for Windows the cells and right-clicking them followed by selecting the desired format even if it appears correct. Excel will occasionally only show the formatting of the upper left hand cell of a range or multiple selection. The “Format Cell” dialog box also contains the “Alignment” tab. From this tab, you can align the contents of the cell in numerous horizontal and vertical formats as well as change its orientation and stack it. Through the “Orientation” formatting function you can rotate your data through various angles. The insert, shown here, contains an orientation sample. Another mode shown here is referred to as “Hotel signage”, referring to the letters H-O-T-E-L will be printed vertically on the sign in front of the establishment. This is also shown in the insert. Text wrap will try to keep the preset column width while creating a second, third, and fourth row presentation within the cell of long data. Tip: You can force line breaks in a cell, as done here, by the keystrokes “Alt-Enter” within a cell rather than of simply Enter or a space. In the insert, “Alt-Enter” produces the same presentation as text wrap except that you control the break points of “Alt-Enter” whereas text wrap makes its own decisions. The functions of “Font” are as expected in any Windows standard application. You can change style, size, and color through this tab. It is on this tab that you will find “Superscript” and “Subscript” functions as well as “strike through.” You can also underline the text contained within a cell from this tab including single and double lines for accounting. However, if underlining is selected, it is only effective on cells with content in them, including spaces, and is only applicable to the cell. That is, if your entry covers two, three, or four columns, only the column or columns selected will be underlined and then, only if data actually appears within them. You can also highlight specific text within the Excel formula window and underline or format only that text such as shown here: (Addressed in length later.) The “Borders” tab allows you to place borders and intermediary lines to the highlighted cell or cells. You can put a thick line on the outer edges by selecting the heavier line first and then clicking the “Outline” icon on the tab. You can then select a thinner line and place lines within the range. Excel will allow you to place vertical, horizontal, and diagonal lines through a range. The functions available through this tab are directly associated with the cell or range selected at the moment. Excel will not allow you to place vertical or horizontal intermediary lines within a single cell through this method, you can draw a line through the cell as explained in the “Drawing” section of the book. Under the “Patterns” tab of the “Format Cells” dialog box you will find the colors to fill a cell and the available patterns for texture presentation of those colors. These functions were used on the templates Fill color icon Font color icon Chapter 12, Page 77 to define the work area, outlined in gray, and the student focus areas, filled in light yellow. To remove a color, simply select the cell or range of cells, right click the selected cell or range, select format cells, select “Pattern” and then select “No color.” This will remove both color and texture or pattern. Limited colors can be added to a cell or range of cells through the “Fill” button or icon on the tool bar. This is the “Paint bucket” as shown here. All colors can be removed through this button. Fonts can be colored, to limited number of colors, with the “Font color” button or icon. Next to both the “Fill” and “Font color” icons is a dropdown arrow to allow color selection. If the bar at the bottom of the icon is the desired color, simply clicking the icon will place that color in the active cell or cells. The last tab is the “Protection” tab. This tab allows you to invoke a minor level of protection on the worksheet or workbook. If you did not want another party to have the privilege of modifying your worksheet you can select the entire sheet by using the keystrokes Ctrl-A or by clicking on the cell above the row 1 number and to the left of the column A letter, then right click the worksheet and select “Format Cells.” After selecting the “Protection” tab, select the “Locked” option. Ensure that the appearing checkmark is clear and distinct. A faded checkmark indicates that the selected area contains more than one level of protection – some protected, some not protected. Also on this tab is the “Hidden” option. If “Hidden” is selected and activated any functions or formulas will be hidden from the viewer – that is, even if the cell contains a formula, the viewer will not see it in the formula window. Neither of these options are active until empowered by following the path Tools > Protection. From this point you can select “Protect Workbook” or “Protect Worksheet” as well as a couple of other options. When “Protect Worksheet” or “Protect Workbook” is selected you will be presented with a pop-up dialog box asking what privileges you want to grant the users and asking if a password will be utilized. You can leave the password blank if you desire and it will still be protected or hidden. If a password is provided and later forgotten, you may not be able to recover the workbook or worksheet. As a matter of technique, if distributing a password protected workbook or worksheet, you may want to preserve an unprotected copy in a secure location. None of these protection levels is sophisticated and they can be broken by an individual with intent, therefore, these levels of protection should not be relied upon to protect sensitive data. Merge Cells The “Merge Cells” command is also on the Alignment” tab. This function will create one cell that occupies two or more rows or two or more columns or, as one group, multiple rows and columns. This function is used in many of the templates to present instructions and text data in one, large, cell. For example, the “Name” area is one cell while occupying the range D2 through F2. To utilize the “Merge cells” function, click into the cell that you want to be the upper left most location of the merged cell. Then, with the left mouse button held down, sweep right, down, or down and right highlighting and selecting the cells you want to be replaced by the new, larger, merged cell. When the area is highlighted, right click the highlighted area, select “Format Cells”, select the “Alignment” tab, and select “Merge Cells.” The highlighted cells now become one single cell when the “OK” is clicked. Invoking the merge cell function will result the loss of all data but the data in the upper left most cell of the selection. Excel will usually advise you that this is going to happen but it is poor data management to rely on Excel’s protective features for this. If this function is invoked, it can be revoked by selecting the affected cell and then removing the check mark from the “Merge Cell” option. Selecting “Wrap Text” and / or “Shrink To Fit” are text functions for the merged cells. The “Wrap Text”, discussed elsewhere in this book, it will allow multiple lines of text for a single line of text entry. The “Shrink To Fit” selection will automatically change the size or “points” of font to present all of the text into the cell. Page 78 Solving Accounting Principles Problems Using Excel for Windows Formatting Within A Cell You can also invoke many of the formatting capabilities for cells onto the specific elements contained within a cell. For example, you want to enter into an Excel cell that the interest on the note payable is due on the 30th of each month. When the text is entered as “Interest on note payable is due on the 30th of each month” the text is not automatically superscripted. For this presentation, select the cell containing the text, then, inside the formula window, highlight with the mouse the two characters “th” within the text, then right click the highlighted selection and select “Format Cells”, then select “Superscript” on the “Font” tab. Notice that not all of the tabs that appeared when “Format Cells” was selected while entire cells were selected are visible now that you have only a portion of the text selected. Since the additional capabilities are not available, they were not presented, even grayed out or faded. Many of the formatting features can still be invoke upon the text within a cell. Formatting Within A Cell Goal Seek Goal Seek is a tools within Excel that will provide you the question if you know the answer. With Goal Seek, click into a cell containing a formula then follow the path Tools > Goal Seek and respond to the dialog box. The worksheet in the screen print is in the Goal Seek data file. The path is Tools > Goal Seek. The pop-up dialog box requires that you identify a cell with a formula in it, for the example that cell is C4. Then enter the value you want the formula to end up at, 50 as shown in the screen print. Then tell Goal Seek what cell you want the value inserted into to create the answer of 50. The cell identified as “By Changing Cell” must be referenced in the formula contained in cell C4. For the example cell C2 was chosen. When OK is clicked with the mouse or the enter key is struck, Excel will produce a pop-up text box stating that it found a solution to get the value of 20 and that that value is 47 and it asks if this answer is acceptable. Since it is, click on the OK and Excel will modify the value in cell C2 to 47 and close the dialog box. If the answer is unacceptable hit Cancel and the value in cell C2 will be left at 1. If Excel cannot compute the value it will error or fault out and tell you it cannot find a solution. This is usually because cell identified in the “By Changing Cell” window is not referenced within the formula contained within the “Set Cell” cell. Null Value The “Null Value” is a special term – it is not a space as imposed by pressing and releasing the space bar, it is not a letter or character such as a, b, or c, it is not a number such as 1, 2, or 3, nor is it a punctuation mark or symbol such as !, ?, @, or #. It is the double quote symbol followed immediately by the double quote symbol such as “” without anything separating them. The best way to define the “Null Value” is it is nothing and it is not anything else. It is used frequently within formulas to return a “clean and clear” presentation. On the “Null Value” data file the use of the Null value is demonstrated. The worksheet is defaulted to show “0” values through the path Tools > Options > View and then ensuring that there is a full color (not faded or blank) checkmark is within the “Zeros Values” box near the bottom of the screen. This is also the default setting for most of the exercise and problem templates. This option or selection will provide you with the presentation of: Chapter 12, Page 79 Sales: Less: Cost of goods sold: Gross profit: Vice: Sales: Less: Cost of goods sold: Gross profit: $100 $100 $0 $100 $100 In the first example it is clear that the value being presented is “0”, it the second example it is not clear if the formula has even been entered. The null value is frequently used in if statements as a response. In the “Null Value” data file, the worksheet determines and presents appropriate periods for depreciation. In cell B4 you enter the life of the asset in years. In cell B5 you enter the periodicity of depreciation – annual, semi-annual, quarterly, or monthly. The formulas in columns B and E starting on row 12 to determine if the period is applicable and enter a period number or return the null value, as instructed through cells B4 and B5. Based on the availability of depreciation periods, the concatenate formula in columns C returns a text string or the null value while column F returns choice of text strings. As you scroll down the columns to see the difference, where the periods are not available in the null value column, the columns are blank and appear to be empty. In the “zero value” column, the response of non-available periods is “0”, an unnecessary and cluttered display and an unnecessary text string. Assume that the “ ” (space) is utilized instead of the null value or a 0. Almost the same thing will happen. The appearance will be clean and crisp as shown in columns H and I. However, now Copy > Paste Special > Values all three examples and you will find the difference. Inside the rows that are not relevant to the “Null Value” columns, there is nothing – the value the if statements were returning was “” – Null – nothing. Inside the rows of the Zero Value columns there are a number of zeros since the if statements were returning zeros. Inside the space columns are a lot of cells with a bunch of spaces in them. Each unnecessary zero and space adding size to your file. Tip: These issues affect default page size in printing. Or The “Or” function of Excel will return a “True” response if any one or more of up to thirty conditions specified are true. This is shown on the “Or” workbook on the data disk. This data file is a copy of the “And” workbook with the “Or” function rather than the “And” function. Some of the results are the same, some are different. For example, now that the formula in cell D1 is saying if any one or more of the cells A1 through A5 are greater than “2”, return “True.” And, since cells A3 through A5 are all greater than “2”, the “Or” statement is returning the “True” value. However, cell G14 now has a minor problem in its resulting logic. Since any one of the conditions is true, that is, the number of apples equals or exceeds the minimum inventory level, the statement is returning a “True” to the “If” statement and the “Concatenate” function is telling you to go skiing when in reality you must order berries. And The “And” function is found under the Logical” category of Excel’s formulas and functions. It can compare up to 30 values against your specified requirements and return a “True”, “False”, numerical, or text string response depending on your requirements. In the “And” data file on the data disk you will find several examples of this formula in operation. In cell D1 the “And” function is being instructed to check cells A1 through A5 to ensure that they are all greater than 2. Since only cells A4 and A5 are greater than Page 80 Solving Accounting Principles Problems Using Excel for Windows 2 (cell A3 is equal to 2), the “And” function returns a “False.” In cell D2 the “And” function is being asked if A1 is equal to 1, if A2 is equal to 2, if A3 is equal to 3, if A4 is equal to 4, and if A5 is equal to 5. Since they are, as supplied on the data disk, the function returns a “True.” Since the “And” function by itself can only return a “True” or a “False” in cell D3 the “And” function is embedded within an “If” statement to return a text string – if all of the cells A1 through A5 are greater than 3, return a “Good”, if they are not all greater than 3, return a “Bad.” The “And” function is providing the “If” statement a “True” or “False” determination. And, since the “If” statement is limited to seven layers of parenthesis while the “And” statement can evaluate thirty values, you have greater latitude in your evaluations. In cells G10 through G12 the “And” function is being utilized with the “If” statement and the “Concatenate” function to make a determination and return a clean cell or a text string and a value. (This is being done for demonstration purposes as an “If” statement and “Concatenate” function can accomplish this as shown in cells G20 through G22.) The “And” function determines if it is a true statement that counted inventory is equal to or greater than the minimum level located by the “SumIf” formula. If is, the return is the “Null value”, if not, it utilizes the “Concatenate” function to state what has to be ordered and the amount that must be ordered. The “And” function does not have to see if all numbers or values are greater than a specific value or less than a specific value. You can mix them up as needed. Inside cell D5 the following formula is placed: “=AND(A1=1,A2<>3,A3>=3,A4<=4,A5<>A1).” This formula asks if cell A1 is equal to 1, if cell A2 is not equal to 3, if cell A3 is equal to or greater than 3, if cell A4 is less than or equal to 4, if cell A5 is not equal to cell A1. If ALL terms are true, the “And” function will return a “True”, if any one or more are false, the “And” function will return a “False” response. The “And” function will also compare text strings as required. Tip: Make sure the question you are asking will provide the answer you need. If Statement As shown throughout this text and as utilized extensively throughout the exercise and problem templates, the “If” statement is a very powerful and easy to use logic tool. The “If” statement works on the principle of comparing term one with term two and if the terms make the condition true, it replies with the first field after the comparison, if the comparison is false, it replies with the second. The first and/or second field can be another “If” statement value, formula, or text. The formula format is “=IF(2=2,4,0).” In this simple “If” statement if 2 equals 2 the formula will put 4 in the cell, if 2 does not equal 2 the formula will put 0 in the cell. Since 2 does equal 2 the response will be 4. This is shown in cell A1 of the “If Statement” data file. However, the statement “=IF(2=Two,4,0)” will result in an error presentation since text strings such as “Two” inside formulas must be contained within quotation marks such as “=IF(2=Two”,4,0).” This is shown in cell A2 of the “If Statement” data file. If the statement is entered into Excel as “=IF(2=“Two”,4,0)”, as it is on the “If Statement” data file in cell A3, the response will be 0 as the number “2” is not equal to a text string of the letters “T”, “w”, and “o”. As stated earlier, Excel does not read English, but it can compare text strings through spell check, F7, which is in reality, a big “If” statement – if the text string being evaluated matches text strings found in the reference file, look at the next text string, if the text string is not in the reference file, bring up the dialog box and ask for help. Excel will compare text strings and provide responses as instructed. For example, the “If” statement “=IF(“Two”=“Two”,4,0)” will return the value 4, as shown in cell A4, not because it is the sum of Two + Two, but because the two text strings are equal, as asked, so the first field or the “True” term following a true statement, is returned. Tip: There is a detail of Excel that needs to be appreciated in all of the comparative statements. If the value of 123.451 is entered into a cell with formatting to display two decimal places the entered value will display as 123.45 but Excel will remember and respond as if it is 123.451. If the value 123.451 displayed as 123.45 is compared to a displayed value of 123.45 which is in a cell formatted to display two decimal Chapter 12, Page 81 places but that value is actually 123.452, the values appear to be equal, as presented in the formatted cells, but they are not. Examine the data in the range of E1 through F2 on the “If Statement” data file. When Excel compares values and the formatting of numerical information is different, for example “Accounting” with 2 decimals and “Currency” or “General” to 4 decimal places, Excel will ignore the formatting and compare the values. However, it is possible to have what appears to be a numerical value such as the 25 contained in cell J1 not be equal to the 25 in J2 since cell J1 as formatted to text before the 25 was entered and, since cell J2 is formatted to General by default, that 25 is numerical and text 25s are not equal to numerical 25s. Hint: Try to think of questions that are answered True/False or Yes/No with “If” statements. The following is a table containing most of the comparison operators available to the “If” statement: = (equal sign) if the first comparative term is equal to the second comparative term, the first field or the true field will be returned, if the comparison is false, the second field or the false field will be returned. Your question is “Is it true that the first comparative term is equal to the second comparative term?” < > (not equal to sign) (The order of the operators is important!) If the first comparative term is NOT equal to the second comparative term, the first field or the true field will be returned, if the comparison is false, the second field or the false field will be returned. Your question is “Is it true that the first comparative term is NOT equal to the second comparative term?” > (greater than sign) If the first comparative term is greater than the second comparative term, the first field or the true field will be returned, if the comparison is false, the second field or the false field will be returned. Your question is “Is it true that the first comparative term is greater than the second comparative term?” < (less than sign) If the first comparative term is less than the second comparative term, the first field or the true field will be returned, if the comparison is false, the second field or the false field will be returned. Your question is “Is it true that the first comparative term is less than the second comparative term?” > = (greater than or equal to sign) (The order of the operators is important!) If the first comparative term is greater than or equal to the second comparative term, the first field or the true field will be returned, if the comparison is false, the second field or the false field will be returned. Your question is “Is it true that the first comparative term is greater than or equal to the second comparative term?” <= (less than or equal to sign) (The order of the operators is important!) If the first comparative term is less than or equal to the second comparative term, the first field or the true field will be returned, if the comparison is false, the second field or the false field will be returned. Your question is “Is it true that the first comparative term is less than or equal to the second comparative term?” “If” statements can be imbedded in other formulas and can be embedded within themselves. The maximum is seven layers deep. However, with the ability of Excel to run an “If” statement on the results of an “If” statement, the seven layer depth restriction is of little concern. If you examine the formulas in the range of D21 through M26, the “If” statements are embedded upon each other to respond to the input of cell D22. If the answer is not contained within E22’s string of “If” statements, then E22 looks to E23 for an answer. If E23 does not know the answer, E23 looks to cell E24 for the answer. And it continues down into cell E26. If all of the possibilities are exhausted, E26’s last option is to respond with the phrase "I asked you to pick a letter, not a number or a special character. Please try again.", which is the last false condition respond for the chain. Hint: Read the section on the “And” function as an additional capability for the “If” statement. ...
View Full Document

Ask a homework question - tutors are online