Follow the exact instructions and use the excel file already attached for the assignment
M I S 2 0 0 0 ( M a r . 2 0 2 0 ) Assignment 2 Excel - 1 - Introduction You can do this assignment by yourself or in a team of 2 or 3 . No more than 3 students in a team. I strongly encourage you to do the assignment by yourself, if you choose to do it in a team, please interact with each other electronically to avoid the chance of coronavirus infection. In other words, you should not use face -to -face meetings/interactions to get the work done. Due date and time: March 31 ( 11:00pm ). To start the assignment, down the Excel data file named MIS2000 -MainClass -A2 -DataFile.xlsx . Rename this file to MIS2000 -MainClass -A1 -LastName1 -LastName2 -LastName3 .xlsx . Replace Last names with your real names. Upload the completed Excel file into Digital Dropbox named Assignment2 by the due date and time. Please upload only one copy for each team. If you upload multiple copies to your dropbox , the one with the most current time stamp will be marked. - 2 - Case 1: Making an investment (FV Function) : monthly deposit You recently started a part -time job and are earning some money, you did some calculation and figure out that you can manage to save $100 each month for the next two years. A bank name d BankingForStudents offers an investment /saving option that sounds good: The bank offers 2 .5% as an annual interest /return rate, and you plan to deposit $100 for each month. You want to calculate how much mon ey you would get on this saving plan at the end of two years. You must use the FV function to do the calculation. • Task 1: C alculate how much money that you will have at the end of the two years , if you put the deposit at the beginning of each month . • Task 2: Calculate how much money that you will ha ve at the end of the two years, if you put the deposit at the end of each month. Please read the explanation of FV function at the following link : • https://support.office.com/en -us/article/fv -function -2eef9f44 -a084 -4c61 -bdd8 - 4fe4bb1b71b3 Table 1 - FV function parameters from Microsoft support Parameter Description Rate Required. The interest rate per period. Nper Required. The total number of payment periods in an annuity. Pmt Required. The payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you mu st include the pv argument. Pv Optional. The present value, or the lump -sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument. Type Optional. The number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0. Worksheets for case s 1, 2 and 3 ha ve no data . You need to figure out how to set up and enter data into your worksheet, and then calculate. Your worksheet should be set up in such a way that parameters of the FV function is clearly labeled (e.g., Rate, Nper) , and value for these parameters are entered in cells; the two solution cells should be properly labeled as well. You must use cell reference in your FV function. For example, you cannot use the annual interest 3.5% directly in the FV function, you must enter it in a cell, and reference that cell inside the FV function. In sum, you Excel set up should be easy to understand. After you enter everything, you will get a number! but it is a negative number. FV is to calculate the future debt! If you use it to calculate the saving, or investment, you will need to add a ne gative sign before Pmt. All your worksheets should be properly formatted, for example, nu mber for money with “$” sign, thousand separators . The dollar amount should have two decimal places. - 3 - Case 2: Making an investment (FV Function) : lump sum deposit Your cousin Jane is surprised to see that you have an investment plan while you are still in Asper school. She already started t o work two year s ago and she usually spends all her money without saving . She plans to have a vacation this summer, thus has managed to save $2,000. But due to the current virus situation , she deci des not to go anywhere, and put the money in a certified saving account (a fixed two - year plan). Jane asks you to do the calculation for her. The annual interest /return rate for her saving is 2.75%. Use the FV function to calculate the future value of the inv estment (how much money that she will have at the end of two years). - 4 - Case 3: Making an investment (FV Function) : long term You told Jane about your calculation result and Jane is very impressed by your ability of using the FV function. She says that she would like to save more . Now she want s to know if she is able to deposit $3 ,000.00 in a mutual fund account each year as a lump sum, and let it grow for 30 years, how much money would she receive at the end of 30 years. Before she makes a decision ab out how much she would deposit for each year, s he wants to do a comparison about how the initial deposit amount affect the end result. She figures out that she would be able to manage make the deposit between $2000 and $5000 each year .She will make the deposit at the beginning of each year. She ask s you calculate this for her. Since the return (rate) of the mutual fund varies , sometimes it could be negative, we will just assume that the rate varies from 3.5% to 8%. Therefore , in your calculati on, you need to include the following annual interest/return rate: 3.5%, 4%, 4.5%, 5%, 5.5%, 6%, 6.5%, 7%, 7.5%, 8%. For the principal/deposit for each year, you will need to include: $2,000, $3,000, $4,000, $5000. Use a table to calculate how much Jane would receive at the end of 30 years for different deposit amount and rates. You must use the FV function. You must use proper cell reference. You will set up the formula in such a way that you can drag and fill out the rest of the table. - 5 - Case 4 : Pivot Tables and Charts You will need to figure out how to do Pivot Tables and creating charts for the pivoted table by yourself . This one is about money too, but not about saving money, it is about spending money. The Case #4 data set lists expenses f or five parks. Part 1: Creating two pivot tables and corresponding charts for Case#4 Park Expenses • Pivot Table 1: Summary of park expenses for the entire Quarter 2: List the total expenses of each park and the total expense for all parks. o Put this table in a new work sheet, name the worksheet: Pivot1. o Create an Excel chart (Bar or Column chart) for the pivot table. Make sure you label the charts clearly so that a reader can understand the chart without reading the pivot table.(e.g., clear and descriptiv e title, label all parks, the expenses need to be in dollar sign with thousand separators). Put the chart in the same worksheet as the pivot table. • Pivot Table 2: Summary of Expenses expense categories : summarize the tot al expense by categories from all parks for the entire Quarter 2. o Put this table in a new work sheet, name the worksheet: Pivot2. o Create an Excel chart (Bar or Column chart) for the pivot table. Make sure you label the charts clearly so that a reader can understand the chart without readi ng the pivot table. o Put the chart in the same worksheet as the pivot table. Part 2 : Making up one question and answer it with one additional pivot table . • You need to make up one additional question and create one pivot table to answer the question. • The question cannot be answered by Pivot Table 1 or 2. • Putting this pivot table in a new worksheet, label it Pivot3. • Create a chart for this pivot table, make sure you clearly label the chart. Type your question in this work sheet. --- End of the assig nment ---
