Spreadsheets

Part 1: Basics

You are living on your own space. Your own car. Your own BILLS!!  Use Microsoft Excel to create your own budget as in Figure 1 below.

Figure 1

 

  • Open MS Excel
  • Create a blank workbook
  • Save your Excel workbook as “Homework 3”.
  • Create a worksheet exactly like in figure 1. Change the title of the sheet to “your name’s” Expenses.
  • Use Auto fit, Auto fill, and Auto complete features in Excel to create the sheet faster.
  • Rename sheet 1 to “Expenses”.
  • Add all borders to the range A2:M8. (Border options are located in the “Home” tab-font section)
  • Save, DO NOT close the workbook, and move on to part 2

 

 

Part 2: Formulas

  • Add a new Worksheet to your Homework 3 Workbook
  • Rename the new Worksheet to “Math operations”
  • Merge and Center cells A1:E1 and type “Working with Excel Math” in there
  • Apply Title style to cell A1(Home tabàStylesàcell stylesàTitle)
  • Type “Addition” in cell A2
  • Type “Subtraction” in cell A3
  • Type “Multiplication” in cell A4
  • Type “Division” in cell A5
  • Auto-fit the contents in column A
  • Insert a row above row #2
  • Type “Basic Math” in cell A2
  • Merge and Center the text in cell A2 to the range A2:E2
  • Apply Heading 4 to cell A2(Home tabàStylesàcell stylesàHeading 4)
  • Apply the Calculation Style to the range A3:A6(Home tabàStylesàcell stylesàCalculation)
  • Type = 4 + 6 / 2 in cell B3 then type = (4 + 6) / 2 in cell D3
  • Type = 6 – 6 * 2 in cell B4 then type = (6 – 6) * 2 in cell D4
  • Type = 2 * 2 + 6 in cell B5 then type = (2 * 2) + 6 in cell D5
  • Type = 9 / 3 + 4 in cell B6 then type = ( 9 / 3 ) + 4 in cell D6
  • Add all borders to the range A1:E6
  • Here is how your sheet would look like.

 

  • press Ctrl + ~  and this what would see
  • Save, DO NOT close the workbook, and move on to part 3

 

Part 3: Import and analyze data

 

  • Download the Expenses.txt file; it’s attached to Homework 3 on blackboard.
  • Back to Excel workbook “Homework 3”
  • Add a new worksheet to “Homework 3” workbook.
  • Name the added worksheet “My Budget”.
  • Import the data from Expenses.txt into “My Budget”
  • Insert a table in the Range A2:M11
  • Merge and center the cells A1:P1
  • Format cell A1: font color=red, font size=18, Fill color=yellow and make the text bold.
  • Rename sheet 1 to “Expenses”.
  • Insert the word “Total” in cells N2 and A12. In column N, insert formulas to find the total of each row.  In row 12, insert formulas to find the total of each column, including column N.  Once you have completed this sheet, cell N12 should show the grand total of all expenses for the year.
  • Insert the text “Monthly min” in cell A14. In row 14, insert formulas in cells B14:M14 to find the minimum expense of each month.
  • Insert the text “Monthly Max” in cell A15. In row 15, insert formulas in cells B15:M15 to find the Maximum expense of each month.
  • Insert the text “Occurrences” in cell A16. In cell B16, insert formulas to count the occurrences of the number 0 in range B3:M11(Use the countif function)
  • Insert the text “sum if value>800” in cell A17. In cell B17, insert formulas to find the total of all the cells in the range B3:M11 that contain values greater than or equal $800 (Use the sumif function)
  • In O2 add the text “Expense Ratio”
  • In cells O3:O11 insert formulas to insert “High Expense” if the total in column N is more than 400 and “Okay Expense” if the total is not greater than 400.(use if function)
  • In P2 add the text “Expense average”
  • In cells P3:P11 insert formulas to find the average of each expense.

 

  • Add all borders to the range A1:P15. (Border options are located in the “Home” tab-font section)
  • Create a 3-D Column chart that shows monthly expenses for: rent, utilities, food, travel, tuition, and car insurance for the month of June.
  • Type “Monthly Expenses for June” in the chart title.
  • Move the chart to a new worksheet titled “June”.
  • Save and submit your workbook under Homework3 on blackboard.