Data Analysis paper|Human Resource Management
1. Watch Video 4 (below) that goes deeper on pivot tables and then introduces conditional formatting:
2. Next: Open a new excel workbook. In the first row (1), labels the columns as follows: first column cell (A1)= ‘description’, second column cell (B1)= ‘date’, then in the next columns have the months of the year (january in column cell C1, february in column cell D1, and so forth)
3. Next: In the second row, first column cell (A2): ‘starting balance’, second column (b2) = 1. The other columns (under each month header) values are equal to the subtotal from the previous month (see further below for calculating subtotals in Step 5)
4. Next: In the next few rows below, put in the labels for 2 pay days, 1 monthly rent, 1 monthly utilities, 1 monthly phone/internet, 4 weekly food, 1 fun, 1 saving, 1 car, and 1 misc expense in the first column. Arrange them in the order they would happen in a normal month and put in days of the month next to them in the second column (ex: pay dates usually are the 1 and 15, rent = 1, food = 1, 8, 15, 22, etc).
5. Next: Fill the the values associated with the labels from step 3 for each of the 12 months using realistic data from your current situation or that of what you think most college students are experiencing.
6. Next: Put in a subtotal field at the bottom. Call it ‘subtotal’ in column A. put in a formula that adds up the correct balance for each month.
7. Next: Now, highlight the cells in the starting balance row 2, columns 3 to 14. Then use conditional formatting (see the video) to change the background color from yellow (low) to blue (high). Now, highlight the cells in the dataset (starting from C3, going to column N whatever row is last of inputted data before the subtotal). Using conditional formatting again to change the background colors from low equal red to high equals green).
