Lab 4: The Use of Spreadsheet

Manage your accounts in an excel sheet

  1. Define a few categories you spend daily 
  2. Define a few categories you spend weekly
  3. Define a few categories you spend monthly 

(The categories could be real or hypothetical. They may include your board-plan, your rent, gas-bills, etc.)

  1. Fill in expenses for all the selected categories in all 3 cases.
  2. Use different work-sheets for each – daily, weekly, and monthly – calculation.

(Remember to note expenses for all the seven days for each category on Sheet1. It will be $0 for lunch if you had to skip it, for instance.)

  1. Insert Charts for each of the work-sheets (category-wise distribution on expenses).
  2. Insert an additional work-sheet. Create a “Summary” chart, showing distribution of your expenses – Daily, Weekly, and Monthly.
  3. Please refer the example lab. Also follow some basic “introduction” to the Excel and some “specific” hints for the assignment.

Using Microsoft Excel

 This section briefly explains different features (or terms) of Microsoft Excel that shall be required for the above-mentioned exercise.

  1. Rows: Numbered 1, 2, 3, and so on; Columns: Named A, B, C, and so on;
  2. Cells: Intersection of Rows and Columns, smallest “addressable” entity. Named A1, B5, etc.
  3. Worksheet: Each of the spreadsheets may contain one or more of worksheets. Microsoft Excel provides you with 3 such work-sheets, and a flexibility of adding or renaming them.
  4. Different tabs could be seen in the bottom-left corner, named “Sheet 1”, “Sheet 2”, and “Sheet 3” by default. Tab “Sheet 1” is already selected.

a)      You could switch between them as and when required, by clicking on those tabs.

b)      To rename the tab right-click on it, select rename, and type the new name you shall want instead of “Sheet 1”, for instance.

c)      Insert another tab: right-click on of the available tabs, select “Insert”, from the options dialogue box select “Worksheet”

  1. Function: Just above the Column names, is the function bar “fx”.

a)      It displays the content of the cell.

b)      The content of the cell could be “constant”, i.e., typed directly.

c)      Or the content could be a complex calculation. In that case, the function bar, shows the calculation formula.

  1. Example: Select the cell C1 where you want to store the result of calculation, A1 + B1. Click on fx toolbar, and a dialogue box appears with the type of operation you may want to perform. Select the SUM operation from the standard list, select the cells A1 and B1 as operands.
  2. Chart: Go to the insert menu, select “Chart”. It helps you to plot the given information.  Follow the steps of the “Charts wizard”. This lab asks for 3 sheets with 3 charts built in and one separate chart showing the weekly expense distribution.

Choosing the Categories

If a category, say, “Rent” is selected. Since you pay your rent only monthly, this would be included in your “Monthly” expense only. Someone would pay for the Gas weekly once.  You may buy Lunch or Breakfast daily and so on…

Note that any category is usually not repeated in more than one tab.

Use the Correct Math

If you have a category “Lunch” on your week expense, you want to SUM this expense for all the seven days to calculate you weekly expense.

Similarly, for the Monthly expense like Rent you want to divide (use operator /) this by 4 (approximately 4 weeks make a month).

Writing the Lab Report

This document should be a brief description of the lab-exercise 4. Do describe your work in an excel sheet. It should be “what you did”, “how you did”, “some experiences”, etc… In essence, you should convey what you learned. Add a link inside your report to the .xls file you have done.

Please note I do not want a detailed description listing your decisions with regards to categories, or colours you used in your sheets, etc.

Submission

  1. NO hard copy! Upload the files to your personal website and add links to them, then email me with URL.
  2. I should receive your submission on or before Monday, Mar. 12.
  3. If you are uncomfortable with any part (or whole) of the assignment do talk to me after the class and/or schedule an appointment by an email.
  4. It would not be wise to start working a day-before and find yourself in troubles. You shall still be penalized.
  5. REMEMBER: “Better late than never”. Skipping one assignment would get you closer to one grade lower.