ASSIGNMENT: 1. If using the Business Division Computer lab, check in with a lab assistant. They will need your student ID to assign you to a computer. 2. You should be using a computer with Microsoft Excel 2019 installed. To access Microsoft Excel: Click the Start button on the bottom left corner of the Taskbar. Select "Excel" on the Start menu. Recently saved excel files are displayed on the left side of the window and template options are displayed on the right side of the window. Because this assignment requires you to create a new file you should select the Blank workbook option in the top-left corner of the template option area. 3. An example of the Excel interface is shown on the next page. Refer to it for the location of such items as the File tab, Quick Access Toolbar, Tabs, Name Box, Formula Bar, and Ribbon. If you have not yet completed the Word assignment, you may want to do so in order to become familiar with the design of the Office 2019 user interface. The Ribbon contains tool buttons that relate to the tab that has been selected. Select each of the tabs and review the tools that are available within each tab, then select the Home tab in order to display its corresponding Ribbon. Each Ribbon is separated into categories. These categories, located on the bottom row of the Ribbon, help organize the many tool buttons contained within each Ribbon. Review the categories displayed within the Home Ribbon. Page 2 Microsoft Excel 4. A worksheet consists of columns and rows. Columns run vertically and are identified by letters across the top of the worksheet. Rows run horizontally and are identified by numbers at the left edge of the worksheet. The intersection of a column and row is called a cell. Cells are named by their row and column position. The combination of the column letter and row number for a cell is called a cell address. For example, the intersection of the first column with the first row is a cell called A1. The cell one column to the right is called B1, and so on. The cell that is currently outlined with a thick black line is called the active cell. Look at cell A1 in the example. It is the active cell. When a cell is active, you can enter or modify data within that cell. You can always determine the address of the active cell by looking in the name box on the formula bar (located just above cell A1). Because A1 is the active cell, it currently reads A1. The right side of the formula bar displays the contents of the active cell. It is currently blank, because the active cell is empty at this time. 5. Use the arrow keys or the mouse to move around the worksheet. Notice that the cell address in the name box changes as you move around. Go back to cell A1 by holding down and pressing or by clicking cell A1 with the left mouse button. 6. Lets put your name on the worksheet. File Tab Quick Access Toolbar Tabs Ribbon Name Box Formula Bar Page 3 Microsoft Excel Type your first and last name in cell A1 and press . Notice that as you type, your name appears in both the formula bar and the active cell. Your name may overflow into the next cell (B1). This will not cause a problem. When you press the active cell automatically moves down one row. It assumes that you want to enter data from top to bottom. Type CISB11 in cell A2. If you make a mistake, you can simply type over the entry or use your mouse to position the cursor on the formula bar where you want to change the entry. The delete and backspace keys can be used to get rid of unwanted characters. 7. Lets give our worksheet a title. Use the arrow keys to move to cell D3. Type the following but DO NOT press : Monthly Budget. Instead, select the right arrow key. Notice that instead of pressing , you may use the arrow keys to move from an entry to another cell. 8. Lets label the months we are going to budget. In cell C5 type January. In cell D5 type February. 9. We are going to let Excel fill in the other two-month names for us, by using AutoFill. Using the mouse, click on cell C5, hold the mouse button down, and move the mouse to cell D5. Release the mouse button. Notice the border around both cells. Point the mouse at the small square (called the FILL HANDLE) in the lower right-hand side of cell D5. You will know when the mouse is positioned correctly because the mouse indicator will turn into a thin line plus sign. Holding the mouse button down, move the mouse straight across cells E5 and F5. When you are on F5, release the mouse button. The months March and April should appear in cells E5 and F5 respectively. The process of holding down the left mouse button and moving it to adjacent cells is called DRAGGING the mouse. 10. Now lets add our budget categories down the left side of our worksheet. Press the function key ; this is the Go To key. Type A7 and click on the OK button. This changes the active cell to the cell address you enter. In cell A7 type Total Income. In cell A9 type Rent. In cell A10 type Food. In cell A11 type Phone. In cell A12 type Car Insurance. Page 4 Microsoft Excel In cell A13 type Utilities. 11. Choose one additional expense category of your choice. Be unique and creative. For example, you may have a personal interest in the game of golf. You might want to budget some of your money each month for green fees. In cell A14 type the name of the category that you will include in your monthly budget. 12. Lets include some calculated fields. In cell A16 type Total Expenses. This will be the total of Rent, Food, Phone, Utilities, Car Insurance, and your expense category. In cell A18 type NET INCOME. Net income is the extra money you have left after paying all of your bills. For some people this may be a negative number!!! 13. It is now time to enter in the monthly amounts for each category. Lets assume that your income in January and February is $1,200. In March you get a raise of $200 per month. In cells C7 and D7 type 1200. In cells E7 and F7 type 1400. NOTE: DO NOT include dollar signs ($) when entering the numbers. We will format the numbers later to add dollar signs. 14. Your rent is $500 per month. In cells C9, D9, E9, and F9 type 500. 15. You spend an average of $200 a month for food. However, you are planning a Valentines Day Party in February. You should budget an extra $60 for food for the party. Type in the correct amounts in cells C10, D10, E10, and F10. 16. Your monthly phone bill averages $65. You have a close friend whose birthday is in March. Your friend is living in Canada and you will want to wish him/her a Happy Birthday and catch up on whats been happening. You should budget an extra $10 in March for that call. Type the correct amounts in cells C11, D11, E11, and F11. 17. Your car insurance averages $120 a month. Type the correct amounts in cells C12, D12, E12, and F12. 18. Your utilities average $85 a month. In April you are planning to take a 2-week vacation. Your utilities should drop about $30 during your absence. Type the correct amounts in cells, C13, D13, E13, and F13. Page 5 Microsoft Excel 19. You selected the next category so you must decide how much to budget each month for your special interest. Type your amounts in cells C14, D14, E14, and F14. 20. You want Excel to add your expenses for you. With cell C16 as the active cell, click the AutoSum button with your mouse. The AutoSum button has a picture of the Greek letter Sigma on it ( ) and is located toward the right end of the Ribbon (in the Editing section). Read Note 1 below. NOTE 1: When you press the AutoSum button, Excel tries to guess what series of numbers you wish to total. It puts the formula in the active cell and puts a moving dotted line around the series of numbers that it thinks you wish to total. By pressing you finalize the selection of cells to total. Press . Read Note 2 below. NOTE 2: Move the active cell back to cell C16. Notice that the total number is displayed in the cell, while in the formula bar, the actual formula used is displayed instead. Return to cell C16. With the mouse, point to the fill handle of the cell. See item number 9 for a review of the fill handle. Drag the fill handle over cells D16, E16, and F16 and then release the mouse button. The formulas have been copied over to cells D16, E16, and F16. Read Note 3 below. NOTE 3: When Excel copied the Sum formula to the other cells, it did not copy the same cell numbers. It assumed that you wanted to total the numbers directly above each total, so it changed the cell references appropriately. 21. To determine your net income, you must subtract your total expenses from your income. In cell C18 type the formula =C7-C16 and press . (This formula can be translated to take the contents of cell C7 and subtract the contents of cell C16.) Return to cell C18. Copy the formula in cell C18 to cells D18, E18, and F18. Refer to the third step in number 20 for a review of the copy procedure. 22. While working on your budget you decide to take a minute to open your mail. A letter from the landlord states that your rent will increase to $550 beginning in February. Change the amount in cell D9 to 550 and press . Watch what happens in cells D16 and D18. Change the rent amounts for E9 and F9 as well, remembering to press after each entry. Page 6 Microsoft Excel 23. Lets add some dollar signs to our data. This is called FORMATTING in spreadsheet language. Position the mouse pointer on cell C7. Holding the mouse button, drag the pointer down and over to cell F18. Release the mouse button. The rectangular area of C7 to F18 should be highlighted. If you highlighted the wrong area, just position the mouse pointer on cell C7 and try it again. This highlighted area is called a RANGE of cells in spreadsheet language. Press the Accounting Number Format Style ($) on the Ribbon. It is located in the Number category of the Ribbon. You may have # signs in some of the cells. This is because the numbers dont fit in the width of the column. We will correct that problem in the next step. Press the Decrease Decimal button (located in the Number category on the Ribbon) twice. This will get rid of the decimal part of the numbers and all of the numbers should now appear in the columns as whole numbers with dollar signs. 24. Lets add a little pizzazz to the worksheet. Well make the title Monthly Budget bold. With the mouse pointer, go to cell D3 and click the mouse pointer to select that cell. Press the Bold button (located in the Font category on the Ribbon). The title is now in bold print. 25. Next, save your worksheet with the filename "Budget" and close the document.