Excel Assignment
This assignment demonstrates that the student can use the spreadsheet to analyze and organize data (make sure you save this sheet after each bullet).
Using the provided excel spreadsheet, apply the following by performing each bullet.
1. Using the pivot table sheet - create the table below the output from #1 - you want to group by region and show the sum of the # orders and total sales - the grand totals are 2490 and 613874
2. On the what-if sheet, create the following Mortgage Calculator using the following data cells
a. Loan Amount:160000
b. Term: 30
c. Int rate: 2.50
d. Calculate payment using the IFERROR function. PMT function in the true part and 0 in the false part.
e. Calculate the total paid using the IFERROR function with the total paid in the true part and 0 in the false part.
f. Create your what-if table below the above data with the following columns provided:
i. Interest Rate - fill with the data .025 through .075
ii. Monthly Payment
iii. Total Payments
iv. Total Interest
g. Using the appropriate data, fill what you need in the what-if data and then setup the what-if analysis properties. To check your work, the ending total interest paid for .075 should be 277919.28 (or close).
h. When you change the Loan Amount, the table should reflect the change.
3. Using the Goal function in the "what-if" scenario, what would you have to put in savings to accommodate your income of 5670 (1353 is the answer).
4. Open the "filter" sheet.
a. Using the Filter, which movie had the opening theater count between 4500 and 5000 - 1 movie and type your answer in "answer sheet"
b. How many movies had the smallest count of 4?
c. Which movie had the highest dollar amount in both domestic and weekend?
d. Using the advanced filter, how many movies can sit over 4300?
Attachment:- Assignment-Starter-File.rar