Using 4-dimensional cell references
As a result of this exercise you will turn in two spreadsheets in landscape view. You will also need to jot down the answers to questions in parts A, E and F either at the bottom of one of your spreadsheets or on a separate sheet stapled to them.
Marion manages a computer lab at a community college. She has four work-study students to assist her. The students are allowed to earn no more than $900 per semester at $4.75 per hour. Marion has created a spreadsheet that calculates the amount of money the students have left at the end of each week. By monitoring this amount, she can budget their work time so that each student earns enough without exceeding the allotted amount ($900).
You will need to download two Excel files. The files are Work Study Balance.xls and Work Study Hours.xls.
For use with Excel 2 Homework (Supplementary Exercises)
- Open the file Work Study Balance. The starting amount ($900) and the hourly rate ($4.75) have been entered in cells B4 and B5 respectively. In cell B10, enter a formula using a cell reference that will copy the starting amount for Ed. Keep in mind that you will need to copy that formula to obtain the same starting amount ($900) for Carrie Ann, Molly, and Jason. What formula did you use? Copy that formula to cells B11 through B13.
- There are 15 weeks in a semester. Week 1 has been entered for you in cell C9. Use Autofill to create the weekly headings in cells C9 through Q9.
- Open the file Work Study Hours on your data disk. This is a report of the number of hours the students have worked each week from the beginning of the semester through the last full week of classes.
- Use the logic in Step B to enter the column headings Week 1 through Week 15 in cells B5 through P5.
- Tile the windows so that you can see both of your open Excel files. In the Work Study Balance file, enter a formula in cell C10 that will calculate the amount of money Ed has left at the end of the first week. Do this for Ed and then use Autofill to copy the formula for the other students and across the weeks through week 14. {Hint: Since both sheets are visible you should use the mouse (point and click) to enter the cell references in the formula. These cell references will be 4-dimensional in nature.}
Note: Your formula in cell C10 for Ed will subtract his hours worked the first week * his hourly rate from his starting amount. It is very important that you use cell references and not numbers in the formula so that it will copy appropriately. Also pay careful attention to which references should be relative and which should be absolute. Some $ signs may need to be removed and some may need to be added after using the mouse to enter the initial formula. What formula did you enter in cell C10?
- After entering the above formula for Ed in cell C10, copy the formula down the column to see how much each student has left at the end of the first week. Now maximize the Work Study Balance window.
Now copy the Week 1 balances to Weeks 2-14. Who will be eligible to work finals week (Week 15)?
Hint: To keep the row headings (Student's Names) in view as you scroll the worksheet, go to column A so that you can see all the headings, then freeze panes at cell C10.
- Center the title across the columns. Select a different font and increase the font size to 16 point. Draw a bottom border under the column headings in row 9. Make any other formatting changes you desire.
- Print Preview the Work Study Balance worksheet. Add your name and date to the header. Print the worksheet on one page using landscape orientation. To do so you should go to Page Set-up and modify any or all of the following (fit-to-one-page, margin size, etc.). You may also decrease the font size of your entries if necessary. Save and close this file.
- The Work Study Hours file should now be active. Use whatever formatting you desire for this worksheet, add your name and date to the header and print it in landscape orientation on one page. Save and close this file. Turn in both spreadsheets along with answers to the three verbal questions asked in the exercise.
Using Financial and Logical Functions
You will use an Excel car loan file for this exercise Download the Excel file Car Loan.xls for this assignment.
Jot down your answers to the verbal questions in parts E, F and G as you did in the above exercise. You will turn in two spreadsheets for this exercise.
Open the file Car Loan.xls. Click on the 12th period sheet tab (the first one). The 12th period refers to the 12th in the series of 36 payments you are to make. You are purchasing a Mercedes CLK 320 Coupe at a total price of $45,000. Because you have been so frugal about saving your money you have put $25,000 down on the car and are now borrowing $20,000 from your bank to be paid off over 36 months at the APR shown in the spreadsheet.
- In cell B9, insert the PMT function (use the Paste Function/Function Wizard to assist you) to calculate a monthly payment based on the interest rate (remember to divide by 12 since these are monthly payments and the interest rate is annual), term, and loan value.
- In cell B10, insert the IPMT function so that it calculates a periodic (12th period) interest payment (how much goes to interest on the 12th payment) based on the interest rate, period, term and loan value.
- In cell B11, insert the PPMT function so that it calculates a periodic (12th period) principal payment (how much goes to principal on the 12th payment) based on the interest rate, period, term and loan value.
- Now print your spreadsheet using a customized header that says Car Loan Data and includes your name.
- Now you want to do a little What-if Analysis. What amount of your first payment went to principal? What amount went to interest?
- What amount of your last payment will go to principal? To interest?
- Use Goal Seek to determine what the term of your loan would need to be to reduce your payment to 500 per month? Remember that 500 (or any payment in this spreadsheet) will be designated as a negative value. Jot down the answer (and round up to the nearest number of months). Reset the changed values in your spreadsheet by clicking Cancel within Goal Seek.
- Now in cell B12, use the IF function to determine if you are to be charged a luxury tax on your vehicle. You will be charged a luxury tax if the total price of your vehicle is greater than $36,000. Therefore, if D4>36000, display "Pay Luxury Tax" in cell B12. Otherwise, display "No Luxury Tax" in cell B12. Change the period back to 12 and print your sheet once again showing the outcome of the IF function.
