Reference no: EM132582201

LOOKUP WORKSHEET:
1. GRADES: In cell C4, use the IFS function to determine the final grade for the first student based on the grading scheme presented in E5:F9. Write the formula so it can be copied to all students.
2. COPY COSTS: In cell B17, use the VLOOKUP function to determine the total costs of making copies based on the number of copies in A17 and the pricing table in E17:F22. Create the formula so it can be copied down column B to cell B20.
3. Format the worksheet for a professional appearance; then format page for printing - add name and worksheet name to the footer, fit to 1 page.

SALES MTG WORKSHEET:
1. Write a formula in cell E3 that can be copied down the column to determine (TRUE or FALSE) if this item is within budget (an item is within budget if the actual amount is less than the budgeted amount).
2. In cell E10, use the OR function to determine if at least one item is within budget. (Answer should evaluate to TRUE or FALSE.)
3. In cell E11, use the AND function to determine if all of the items are within budget. (Answer should evaluate to TRUE or FALSE.)
4. Set conditional formatting for items with budget amounts over $200 - shade these values in yellow and italicize the cell contents.
5. In cell F3, use the IFS function to determine the result based on the following (copy the result to cell F8):
a. If this item has an actual cost of less than $100, then return the text "Low".
b. If this item has an actual cost between $100 and $200, return the text "Average"
c. If this item has an actual cost of more than $200, return the text "High".

6. In cell G3, use the IF function to calculate the cost of this component for a larger sales meeting based on the following (copy the result to cell G8):
a. If this item is required, as indicated in column B (required items are marked "R"; optional items are marked "N"), then the cost will be 2 times the original budgeted amount.
b. If this item is not required/optional, as indicated in column B, then the cost will be equal to the original budgeted amount.

7. Write a formula in cell H3 (using the AND function) that can be copied down the column to determine whether or not there is "cause for concern" per item. Results should evaluate to TRUE or FALSE. There is cause for concern if all of the following conditions are met:
• Item is not required;
• Budgeted amount is greater than $50;
• Actual amount is greater than the budgeted amount.

8. Write a formula in cell I3 (using the OR function) that can be copied down the column to determine whether or not there is "no concern" per item. Results should evaluate to TRUE or FALSE. There is no concern if any of the following conditions are met:
• Item is required;
• Budgeted amount is greater than $75;
• Actual amount is less than the budgeted amount.

9. Write a formula in cell C9 that can be copied to D9 that shows the totals for Budget and Actual amounts.
10. Determine the number of required and not required/optional items and the total of actual costs for each.
a. In cell E14, use the COUNTIF function to calculate the number of REQUIRED items.
b. In cell E15, use the COUNTIF function to calculate the number of OPTIONAL items.
c. In cell E16, use the SUMIF function to calculate the sum of actual costs for REQUIRED items.
d. In cell E17, use the SUMIF function to calculate the sum of actual costs for OPTIONAL items.

11. Format the worksheet for a professional appearance; then format page for printing - add name and worksheet name to the footer, fit to 1 page.

FINANCIAL OPTIONS WORKSHEET:
Using financial functions (RATE, PMT, FV, NPER, PV) complete the shaded cells to analyze 5 financial options for MIS, Inc. (HINT: Make sure your interest rate and number of payments are set to the correct terms)

SAM'S STUFF:
1. Group worksheets for Quarters 1-4 and calculate revenue under each pricing scenario for each quarter.
2. Group worksheets for Quarters 1-4 and calculate costs under each pricing scenario for each quarter. In cell C10, use the appropriate combination of mixed references so the formula can be copied across to column E and down to row 13.
3. Group worksheets for Quarters 1-4 and calculate projected earnings under each pricing scenario for each quarter. Projected earnings is equal to revenue minus the subtotal of cost of goods sold.
4. Make a copy of the Quarter4 sheet and name the new sheet "SamsSummary". Change the title to indicate that this sheet represents the yearly summary. Remove the sales volume data in cells C4:E4. Using static consolidation, complete the summary sheet to show sales volume totals for the entire year. Adjusting the sales volume should automatically calculate yearly totals for total revenue, cost of goods sold and projected earnings.
5. What pricing strategy seems to work best for Sam? Why? Type your answer at the bottom of your summary sheet.

BANK LOANS WORKSHEET:
1. Create an Excel table in the BankLoansworksheet, and rename the table as LoanData.
2. Format the Amount and Interest Rate fields so that it is clear that these fields contain dollars and percentages, respectively (no decimals).
3. Make a copy of the BankLoansworksheet, and name the new sheet SORT. Sort the loan data in ascending order by type, within type by city, and within city by last name. Use conditional formatting to display all loans in Taos using a format of your choice to highlight these loans.
4. Make another copy of the BankLoansworksheet, and name the new sheet FILTER. In the Filter worksheet, filter the LoanData table to display loans made during April and May 2016. Insert a TOTAL row and display the average amount of loans for the filtered data.
5. Make another copy of the BankLoansworksheet, and name the new sheet SUBTOTALS. Sort the loans in ascending order by city, then by type of loan, and then by amount of loan (largest loan first). Convert table to a range of data. Insert Subtotals (Average) for the loan amount by city.
6. Using the LoanData table, create a PivotTable that displays the number (Count) and average loan amount by type and city. Place the PivotTable in a new worksheet (named Pivot1). Format the loan amount field to be Currency format, no decimals. Name the CountofAmount field as Number of Loans and name the AverageofAmount field as Average Amount.
7. Using the LoanData table, create a PivotTable that displays the number (Count) and total loan amount categorized by type. Place the PivotTable in a new worksheet (named Pivot2). Format the loan amount field to be Currency format, no decimals. Add Loan Date to the pivot table. Rename CountofAmount field as Number of Loans, and rename SumofAmount as Total Amount. You can format your pivot table to improve its appearance.

Attachment:- Management Information Systems.rar