Introduction to Business Information Processing

You have to prepare the following worksheet using MS-Excel.

  1. Enter the title Global Company – Forecast of Retirement Funds in A1 and then center it in cells A1 through H1. Make the font size as 20 and font type as Bernard MT Condensed. Insert an appropriate fill color.
  2. Type column headings in row 3 as shown. Type Employee names and Status type in columns A and B.
  3. Enter in row 16 and columns A, B, and C the headings “Status”, “Annual Employee Contribution”, and “Ending Company Contribution”.
  4. Enter information under these columns (as shown) in rows 17, 18, and 19
  5. Click in cell C4 and enter the appropriate VLOOKUP function to determine the Ending Company Contribution. Your entry should contain an absolute reference to the table of Status and Ending Company Contribution that are contained in cells A17 through C19.
  6. Enter Interest Category values in column D.
  7. Enter “Low Interest Rate” and 5% in cells E16 and F16 respectively.
  8. Click in cell E4 and use an IF Function to determine the interest rate based on the Interest Category in cell D4. If the Interest Category is Low, interest rate is supposed to be picked up from F16, otherwise High interest rate is always 2% more than the Low interest rate.
  9. In column F, enter the number of Years of Contribution chosen by each employee.
  10. Click in G4 to compute the Annual Employee Contribution by using the VLOOKUP function using the table in cells A17 through B19.
  11. The Retirement Fund in cell H4 is the sum of accumulated value from Annual Employee Contribution for the Years of Contribution at the given Interest Rate (Use the FV function) PLUS Ending Company Contribution.
  12. Copy the formulas and functions from row 4 to the remaining rows.
  13. Write your name (instead of my name) and class information as indicated. Also enter Fall 2018 and today’s date. Set up today’s date for automatic update. Hint: Use the Now function.
  14. Save the file as HW#9-Your Name- MAN18- Section #-Fall 2018 and mail to Shailendra.palvia1or2@gmail.com depending on your section number.
  15. Your final results should look as on the worksheet below. A B C D E F G H
    1 Global Company – Forecast of Retirement Funds
    3 Employee Status Ending Company Contribution Interest Category Interest Rate Years of Contribution Annual Employee Contribution Retirement Fund
    4 Allen Clerical $ 2,500 Low 5% 10 $ 3,333 $44,422.12
    5 Arnold Officer $ 4,000 Low 5% 15 $ 4,444 $99,895.14
    6 Brill Manager $ 5,000 High 7% 22 $ 5,555 $277,226.88
    7 George Manager $ 5,000 Low 5% 27 $ 5,555 $308,687.00
    8 Grauer Clerical $ 2,500 High 7% 39 $ 3,333 $621,239.09
    9 Groves Clerical $ 2,500 Low 5% 42 $ 3,333 $453,227.43
    10 Paul Officer $ 4,000 High 7% 16 $ 4,444 $127,934.51
    11 Pinder Officer $ 4,000 High 7% 7 $ 4,444 $42,458.47
    12 Wiggins Clerical $ 2,500 High 7% 11 $ 3,333 $55,106.74
    14 Totals $ 32,000 $ 37,774 $2,030,197
    15
    16 Status Annual Employee Contribution Ending Company Contribution Low Interest Rate 5% Shailendra Palvia
    17 Clerical $ 3,333.00 $ 2,500.00 MAN18 Fall, 2018
    18 Officer $ 4,444.00 $ 4,000.00 Today’s
    Date 11/30/2018 15:43
    19 Manager $ 5,555.00 $ 5,000.00

It does not matter the subject area of your paper, we are here to help. Get in touch with us today and place your order…

Leave a Reply