CIS 100 -- Excel Assignment 2

 

Data file(s) needed for this assignment: CIS100Budget.xls

Objective

Ashlee Williams is a graduate student, and has a teaching assistantship. Her college-related expenses, such as tuition, and fees, are covered through grants and scholarships, so the money she makes goes towards her personal expenses. In the summer she can take on other jobs for additional income.

Being on her own for the first time, Ashlee is finding it difficult to keep within a budget. She has asked you to look at her finances and help her figure out how her money is being spent. The values entered cover a 12-month span, so you can copy and paste the formulas from one month to another or auto fill the data series without retyping formulas or entering each month individually.

Ashlee wants to calculate how much money she brings in and spends, figure out her average/minimum/maximum expenses. She would also like to determine weather she can afford a mortgage payment on a town home, and move out of graduate housing on campus.

To Do

  1. Download the CIS100Budget.xls workbook, and save it as WebCTID-Budget.xls on your flash drive. Remember to save frequently as you work.
  2. Insert a new worksheet as the first sheet in the workbook and name it Documentation. Enter your name in cell B3, the current date in cell B4 using the TODAY() function, and the purpose of the workbook in cell B5.
  3. Switch to the Budget worksheet, and then enter the formulas in the ranges C7:N7 and C20:N20 to calculate the total income and expenses for each month.
  4. In range C22:N22, enter a formula to calculate Ashlee’s net income, or how much money is left over at the end of the month. For all the cells use the currency format with a $ symbol and zero decimal places. (Hint: subtract the total monthly expenses from the total monthly income)
  5. In the range D23:N23, enter a formula using the SUM function to calculate the running total for net income from February to December. (Hint: One way to calculate the running total is to add the net income values of consecutive months.)
  6. In the range C5:N5, use Auto Fill to fill in the month names Jan-06, Feb-06, Mar-06, and so forth.
  7. In cell O5, enter the column heading Total
  8. In the range O6:O22, enter a formula to calculate the total income and expenses for the year. Format the cells so that negative values will appear red, and show 2 decimal places.
  9. In the range C27:E43, enter formulas to calculate the average, minimum, and maximum values for income, expenses, and net income using the AVERAGE, MIN, MAX functions.
  10. Rename the last worksheet Loan Analysis. Enter the text Loan Analysis in cell A1.
  11. Switch to the Budget worksheet and copy the range A26:B43, then switch to the Loan Analysis worksheet and paste the values into the range A2:B19.
  12. In the cell C2, enter the column heading Average.
  13. In the range C3:C4, enter cell references to the values in C27:C28 of the Budget worksheet to reference Ashlee’s Average income.
  14. In the range C7:C16, enter cell references to the values in C31:C40 of the Budget worksheet to reference Ashlee’s Average expenses. In cell C17, enter a formula to calculate the Total Expences.
  15. In the cell C19, enter a formula to calculate the net income (Total Income - Total Expences).
  16. Enter the following row labels in the following cells:
  17. Widen column E to 21 characters
  18. In the range F4:F9, enter the values for the following loan and payment conditions
  19. In cell F10, enter a formula to calculate the total number of payments. In cell F11, enter a formula using the PMT function to calculate the monthly loan payment.
  20. In cell C7, enter a new formula to reference the mortgage payment calculation in cell F11 (Loan Analysis).
  21. Enter the following row labels in the following cells:
  22. Ashlee wants to save at lest $400 a month. Enter this value into cell F14, and then in cell F13 enter a formula using the IF function to display the text “Yes” if the average net income (C19) is greater than the minimum monthly savings (F14), and if not “No”.
  23. Preview the worksheet. Open the Page Setup dialog box, change the page orientation of the worksheet to landscape orientation, and then select the option so the worksheet will print on one page. Preview the worksheet again.
  24. Save and close the workbook.
  25. Print the Excel Assignment 2 Gradesheet, and fill in the information at the top.

Turn In

Upload and submit your workbook into WebCT Excel Assignment 2.

IBCK- 2005-10-14