Hands-On Midterm – Online
Open the workbook named MISLabExamOnline
ASSUME THAT YOU WILL ENTER FORMULAS/FUNCTIONS AND COPY EITHER DOWN OR ACROSS
1. In the STUDENTS worksheet, merge and center the titles in cells A1 and A2 across columns A through H. Do each title individually. Format both titles using the Cambria font, Bold and 16 point size.
2. In the range G8:G17, calculate the total hours worked for each student. Apply green data bars to this range.
3. In the range H8:H17, calculate the salary earned for each student. The hourly rate is referenced in a cell at the top of the worksheet. Apply purple data bars to this range.
4. Format all numbers – when referring to hours worked, use a general number format with 2 decimals; when referring to salary information, use the currency format with 2 decimals. Adjust column widths for columns B through H – make all of these columns the same width, making sure all data is visible. Center the labels (both horizontally and vertically) in cells B7:H7. Center the label in cell A7 vertically (middle align).
5. Calculate totals in row 19 (in the range B19:F19, total hours per day; in cell G19, total hours for all students for the week; in cell H19, total salary earned for all employees).
6. Select the range B19:F19. Use conditional formatting to create a highlight rule to format cells with values greater than 32 in green fill with dark green text.
7. In the range B20:F20, calculate the percentage of total hours for each day of the week to the total number of hours for the entire week. Format these numbers as percentage with 1 decimal place.
8. In the range B22:H22, calculate the average across all students for each day. In the range B23:H23, calculate the highest value across all students for each day. In the range B24:H24, calculate the lowest value across all students for each day.
9. In cell B26, calculate the number of students on the work force.
10. Select the entire range (A1:H26). Place a thin black border around each cell in this range.
11. Create a 3-D clustered column chart that will show each day and the total number of hours worked per day. Add an appropriate title and make at least 2 additional adjustments that you think are helpful in understanding this chart. Position this chart in the range J7:P19.
12. Format page for printing – name in footer, set to print landscape, fit everything to 1 page.
1. In cell G3, write a formula using the IF function that will show whether or not a penalty is applied for each vendor. A penalty is applied if the total past due balance is at least $1000. Show “YES” if the vendor will have a penalty applied, show “NO” if the penalty should not be applied.
2. In cell H3, use the VLOOKUP function to determine the penalty amount. The penalty amount is based on the total past due balance. Look up the value of the total past due balance in the table found below the list of accounts payable. Apply the percentage of the penalty to the total past due balance to determine the penalty amount. Format values as Currency, no decimals.
3. In cell H13, use the COUNTIF function to calculate the number of vendors that have a penalty applied to their account.
4. In cell H16, for those vendors that have a penalty applied, use the SUMIF function to calculate the total amount of the past due balances for all vendors.
5. Format page for printing – name in footer, landscape, fit to 1 page.
1. In cell D10, use the PMT function to calculate the monthly payment required to repay a 200,000 loan in 10 years at 6.5% interest compounded monthly. Copy (or autofill) this formula to the entire table range to complete all payoff options. (HINT: mixed references used in PMT: reference to length of loan–D$9; reference to pv-$C10)
2. In cell E22, use the IF function to determine if you can afford a 20-year loan of $240,000. If the payment for a 20-year loan of $240,000 is less than the maximum affordable amount in cell E3, then you can afford it; otherwise you cannot afford it. Copy this formula to cell E23 to determine if a 20-year loan of $250,000 is affordable.
3. Format page for printing – name in footer, set to print landscape, fit everything to 1 page.
Submit completed Excel file for grading.