Management Information Systems – LAB EXAM

FILE: MIS Lab Final Exam Online

**THERE ARE INSTRUCTIONS FOR 5 WORKSHEETS/SECTIONS. YOU MUST COMPLETE 4 OF THEM. IF YOU CHOOSE TO COMPLETE ALL 5, THE LAST ONE WILL BE CONSIDERED FOR EXTRA CREDIT.**

**LOOKUP WORKSHEET:**

- GRADES: In cell C4, use a nested IF 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.
- COPY COSTS: In cell B17, use a LOOKUP function to determine the total costs of making copies based on the number of copies in A17 and the pricing table in E17:F22. Copy this formula down column B to cell B20.
- 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:**

- 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).
- Write a formula in cell E10 to determine if at least one item is within budget. (Answer should evaluate to TRUE or FALSE.)
- Write a formula in cell E11 to determine if all of the items are within budget. (Answer should evaluate to TRUE or FALSE.)
- Set conditional formatting to the item names in column A so that the item name would be italicized and shaded in yellow if the item has a budget over $200.
- Write a formula in cell F3 that can be copied down the column to return the following:
- If this item has an actual cost of less than $100, then return the text “Low”.
- If this item has an actual cost between $100 and $200, return the text “Average”
- If this item has an actual cost of more than $200, return the text “High”.

- Write a formula in cell G3 that can be copied down the column to calculate the cost of this component for a larger sales meeting based on the following:
- 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.
- If this item is not required/optional, as indicated in column B, then the cost will be equal to the original budgeted amount.

- Write a formula in cell H3 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.

- Write a formula in cell I3 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.

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

- 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:**

- Group worksheets for Quarters 1-4 and calculate revenue under each pricing scenario for each quarter.
- 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.
- 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.
- Using static consolidation, dynamic consolidation, or formulas, complete the summary sheet to show totals for the entire year. Calculate yearly totals for number of units sold (sales volume), total revenue, cost of goods sold and projected earnings.
- What pricing strategy seems to work best for Sam? Why? Type your answer at the bottom of your summary sheet.

** **

**BANK LOANS WORKSHEET:**

- Create an Excel table in the BankLoans worksheet, and rename the table as LoanData.
- Format the Amount and Interest Rate fields so that it is clear that these fields contain dollars and percentages, respectively (no decimals).
- Make a copy of the BankLoans worksheet, 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.
- Make another copy of the BankLoans worksheet, 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.
- Make another copy of the BankLoans worksheet, 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.
- 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*. - 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.

** **

**Submit completed file for grading**