EXERCISE 1 – GENERAL SKILLS

Warning: When you click on a spreadsheet file in BlackBoard, you will want to first save it to your computer and then open the saved file with Excel. If you work on a spreadsheet file after opening it directly from BlackBoard, you will be unable to save your work.

If you are new to Excel 2007, you’ll notice that the toolbars at the top have been replaced by what Microsoft calls “ribbons”. In earlier versions, you would set Excel to display the toolbars that you used most often. In Excel 2007, the toolbars have been replaced with tabs at the top of the opening ribbon which display different ribbons corresponding to different sets of functions. We will be going through the various ribbons as we go through the many different exercises in the course.

For this exercise, download the file named Exercise1-GeneralSkills from the website. The first thing we’ll do is to format the entire worksheet for how the cells will display numbers.

Click in the cell at the intersection of the A column and Row 1 in the upper left-hand corner of the spreadsheet. This will highlight the entire row of column letters and row numbers. Now click on the Home tab at the top-left of the tool ribbon if it is not already opened, followed by the General drop-down box above the Number section of the ribbon at the top. Select More Number Formats at the bottom of the drop-down box. Click on Currency and set the decimal places to zero and click the box that says use 1000s separator. Now click on the negative numbers selection of (1234.10) and then click OK. This has set the format of all cells in the worksheet.

Now click on cell A1. While holding the Shift key down, click on cell H1 to highlight everything from cell A1 to H1. Now click on the Merge & Center icon in the Alignment section of the selection ribbon. It shows a little “a” with arrows on both sides. If you want to change the merged cells, you will need to undo the current merge by clicking on the Merge & Center icon again, and then create the new Merge & Center that you want. Now click on the bold B button in the Font section to highlight the text (or Ctrl-B using the keyboard).

Rather than repeating this procedure for cell A3 which contains SAMPLE CORPORATION text, do the following: Click on the INCOME STATEMENTS cell that we just created. Now click on the Format Painter tool (with a paintbrush icon) over in the Clipboard section on the far left. Then, click on cell A3.

Click on E7 and drag to G7 to highlight the three cells. Now click on the General drop-down box above the Number section, followed by More Number Formats: Number and click off the check-mark in the box that says use 1000s separator and click OK. Click the bold tool button. Click on the underline tool button U (or Ctrl-U).

Click on cell E9. Click on the grid icon next to the underline tool button in the middle of the Font selection. Note that if you hover the cursor over the icon, it says “bottom border”. If you click on the arrow immediately adjacent to the right, you’ll see all of the border options available. Use the paintbrush to copy E9 to G9. Highlight the range E9:G9. Click the paintbrush and click on cell E13. Click the paintbrush again and click on cell E18. Click on E19 and use the drop-down underline to make a double -underline and then “paint” it to cell G19.

Click on cell A8 and then click on the Format icon in the Cells section of the ribbon and select Row Height. Type in 18 then hit OK. Click on cell A10 and press the F4 button on the keyboard. Click on cell A14 and press the F4 button and then cell A19 followed by the F4 button. The F4 button is a “repeat” command that will duplicate your last action as many times as you want. We will use the F4 button for another purpose when we start writing equations in cells.

You should format the Balance Sheets and Statement of Cash Flows in a similar manner to the way we have formatted the income statement.

Now, click on cell A28. In the Name Box at the upper left, where the cell name A28 shows, type in Balance. This renames the cell as Balance indicating that it is the beginning of the Balance Sheets. You can now move to the Balance Sheet from anyplace in spreadsheet simply by using the drop-down box and selecting “Balance Sheet”.

Click on cell A29. Click on the Insert icon in the Cells section of the ribbon. If you click on the arrow at the bottom of the icon, you’ll see all of the insert options available. We want to select Insert Sheet Rows. If you got something other than what you intended, just click on the counter-clockwise arrow at the very top-left of the screen and Undo it. Once you have inserted a row, press the F4 key four times. Now click on cell A1 and click on the paintbrush tool button. Click next on cell A28. Click on cell A3. Press Ctrl-C and then click on cell A30 and press Enter. As you can see, when you copy a cell to another location, it copies the formatting as well. You can also use the Copy and Paste icons at the far left of the ribbon, but you’ll find using the “hot-keys” easier since the Copy and Paste icons only appear on the Home tab of the tool ribbon.

Click on cell F8. We are now going to create an equation. Press the “=” button on the keyboard and then click on cell E8, press the “/” keyboard button and click on E8 again, and then press the F4 button twice. Note that dollar signs ($) appear and disappear on both sides of the cell column designation. The dollar sign in front of the cell letter locks in the column. A dollar sign after the cell letter locks in the row. If $ appears both in front of and after the letter, you’ve lock in the cell itself. This is important when copying a formula. Now press the “Enter” key. Click on cell F8 and then click on the % icon in the Number section of the tool ribbon. Now click on the Increase Decimal icon two spaces to the right. Now press Ctrl-C and move to cell F9 and press Ctrl-V. Go to cell F10 and press Ctrl-V again. Then move to cell F12 and press Enter. Place the cursor over the lower right-hand corner of cell F12 where you see a solid cross (no arrows) and drag it down to cell F19. Click on cell F15 and press the “Delete” button. Click on cell F17 and press the “Delete” button again or press the F4 button. Click on cell F8 and highlight down to cell F19. Press Ctrl-C and click on cell H8 and press “Enter”. You have just made common-size income statements for years 2008 and 2009.

You can also make a cell’s format conditional upon the result of a calculation. Go to cell G10. We need to replace the number that is there with an equation. Type in “=g8-g9” and press Enter. Now the value will be negative since the Cost of Sales exceeds the Revenues. After clicking on cell G10 to make it the active cell again, click on the Conditional Formatting icon in the Styles section of the ribbon. Hover on the Highlight Cells Rules until a box appears to the right and select Less Than. Enter zero (0) in the left box and then click on the drop-down arrow of the right-hand box. Click on Custom Format. In the box that appears, select the Bold font style and use the drop-down box by Color: to select the red color. Click OK on the dialogue box and then again on the original dialogue box. Now, the number will appear in a bold red font anytime the result of the cell is negative. To see this, go to cell G8 and enter 129 million (129000000 – with or without commas).

You should now create common-size balance sheets for 2008 and 2009 by expressing every account on the balance sheet as a percentage of Total Assets.

Moving Around a Worksheet

Place your cursor on the spreadsheet that we’ve been using in cell H19. To get to the first column of that row, just press the Home button. Put the cursor back in H19. If you now press End – Cursor left you will move to the last column of contiguous cells that contain values within them. Thus, you will find yourself in cell E19. If you press End – Cursor left again, it will move you to cell B19 which is the next cell to the left that again has a value in it. The End – Cursor movement works for all directions.

If you ever want to get to the very beginning of your worksheet (upper left-had corner), simply press Ctrl – Home simultaneously. To find the very end of your worksheet (lower right-hand corner) you can press Ctrl – End simultaneously or End – Home sequentially. Note: If you have deleted some rows or columns, Excel’s End – Home technique will take you to the very end of the biggest spreadsheet that you created.

Miscellaneous

Although it is on a different ribbon, there are a couple of techniques that are useful learning up front to make working on a large spreadsheet easier. Click on the View tab at the top of the tool ribbon.

You can Freeze Panes in order to maintain headers or labels. For example, place your cursor in cell D8. In the ribbon, click on Freeze Panes and select the Freeze Panes option that appears. Now, as you scroll down the sheet or across the sheet, the top titles and the category listings will always be visible. Unlike earlier versions of Excel, you can even change cells in the frozen sections – but they will not move. To undo this, click on Freeze Panes again and select the Unfreeze Panes option.

The main limitation of the Freeze frames function is that it does not allow you to change the frame at all. Many times, particularly with large spreadsheets, you will want to split the screen so you can move around and edit/connect with cells in different parts of the spreadsheet. This can be achieved by using Split in the menu bar. After unfreezing the frames that we just did, go to cell E30 in the spreadsheet. Make sure that this cell now appears approximately in the middle of your monitor screen. Click on Split in the ribbon. Now, you can jump from one window to another and scroll any of them to any cell within the spreadsheet. This is very handy at times and yet still provides some of the attributes of the Freeze Panes function where you can maintain headers and labels. If the spreadsheet is already split, clicking on Freeze Panes will convert the Split line into a Freeze Panes line. Clicking on Unfreeze Panes will revert it back to a Split window.

Finally, you may want to change the visual size of the spreadsheet on the monitor screen. One way of doing this is to use the Zoom icon from the tool ribbon and select the size that you want. A shortcut is to simply hold down the Ctrl key while you use the scroll wheel on your mouse to increase/decrease the size of the view being displayed on the screen.