Management Information Systems
LOOKUP WORKSHEET
1. GRADES: 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.
2. COPY COSTS: 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.
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
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 FAL
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:
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".
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:
a. If this item is required, as indicated in column B, 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 amoun
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.
a. In cell E14, calculate the number of REQUIRED items.
b. In cell E15, calculate the number of OPTIONAL items.
c. In cell E16, calculate the sum of actual costs for REQUIRED items.
d. 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.
STATS/SUMMARY WORKSHEETS:
The STATS worksheet contains the results of a dining survey. Answer the questions in the SUMMARY worksheet by referring to the data in the STATS worksheet.
1. In cell B2, use the COUNT function to determine the total number of respondents.
2. In cell B3, use the COUNTIF function to determine the total number of respondents with incomes of at least $75,000.
3. In cell B4, use the COUNTIF function to determine the total number of respondents who had less than 5 desserts out per month.
4. In cell B5, use the SUMIF function to determine the total number of meals per month reported by respondents who earn less than $75,000.
5. BONUS: In cell B6, determine the total number of desserts per month reported by respondents who are in the 30-35 age category as well as respondents who are in the 26-30 age category.
6. Format page for printing - add name and worksheet name to the footer, fit to 1 page.