excel Homework
i want to see the fill the excel sheet also i need to see which formula you use for it
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 BankLoans worksheet, 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 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.
4. 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.
5. 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.
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.
>Lookup
I L G A ES
0
Grade 0
F 00-699
D randon
B A Cost
10 200 sMtg
R R 0
R N $ 100 5
R N $ 50 $ 40 4 5 2 20 0,000
12 6 12 4 $ 0 0 4 $ 1,000,000 $ 0 0 Sale Regular Sale Holiday Selling Price: $ 45 $ 39 $ 35 Cost of Goods Sold: Projected Earnings Regular Sale Holiday 2,100 Selling Price: $ 45 $ 39 $ 35 Cost of Goods Sold: Projected Earnings Regular Sale Holiday Selling Price: $ 45 $ 39 $ 35 Cost of Goods Sold: Projected Earnings Interest Rate 0
Eileen Cooper Santa Fe 0
15 Mortgage Eileen Cooper Santa Fe 0
0.0700000003 30 Mortgage 3 Scott Hunter Taos 10000 5 Automobile 200000 15 Mortgage Ellen Harper Albuquerque 15000 0.065 3 Ellen Harper Albuquerque 25000 4 Automobile Ellen Harper Albuquerque 15 Mortgage Santa Fe 0.0575 15 Mortgage Max Entermann Santa Fe 5 Automobile Max Entermann Santa Fe 25
10 Other Max Entermann Santa Fe 0.08 30 Mortgage Albuquerque 10000 3 Automobile Helen Rayus Albuquerque 3/1/16 20000 0.075 5 Other Santa Fe 0.065 30 Mortgage Albuquerque 5 Other Peter Sanger Albuquerque 0.06 15 Mortgage Taos 0.0575 30 Mortgage Rose Budnick Taos 5000 3 Other Rose Budnick Taos 0.075 4 Automobile Albuquerque 5 Automobile Taos 35000 0.06 5 Other Barbara Ringer Taos 0.0575 15 Mortgage Barbara Ringer Taos 350000 0.065 15 Mortgage
F
N
A
R
D
Name
Score
Final
Grade
Grade Distribution
Mary
9
3
(
Points
David
4
5
0-599
Tyler
880
6
B
750
700-799
C
Rebecca
3
20
800-899
Mallory
650
900+
Carrie
970
COPY COSTS
Copies Made
Total
Number of copies
Price per copy
3
1
0.07
10
0.06
200
100
0.05
1001
0.04
500
0.03
1000
0.02
Sale
Sales Meeting in NY
Item
Required/Optional
Budget
Actual
Within
BudgetComponent Cost
Large Meeting
Cause for Concern
No Concern
Food
$ 250
$ 185
Hotel
$ 50
$ 525
Transportation
$ 100
$ 40
Theatre Tickets
$
12
Airfare
$ 225
$ 199
Tour Package
Total
At least 1 item within budget
All items within budget
# of Required items
# of Optional items
Sum of Costs for Required Items
Sum of Costs for Optional Items
FinancialOptions
MIS, Inc.
OPTION
Periods per year
Annual
Interest Rate
Duration in years
Periodic Payment (PMT)
Present Value (PV)
Future Value (FV)
Annual Payments
(1) What will my payments be?
6.0%
$ 500,000
$ 0 0
$ – 0
(2) How much will my savings be worth?
5.3%
$ (50,000)
$
30
(3) How much do I need to start?
5.8%
$ (30,000)
$ 2,000,000
(4) What is the interest rate?
$ (20,000)
$ 1,000,000
$ (240,000)
(5) How long will it take to pay off?
6.5%
$ (35,000)
$ (140,000)
SamsStuffQtr1
Sam’s Stuff 1st Quarter Budget
Regular
Holiday
Sales Volume:
2,200
2,500
3,200
Selling Price:
$ 45
$ 39
$ 35
Item
Costs per item
Regular Price Total
Sale Price Total
Holiday Price Total
Revenue
Cost of Goods Sold:
Materials
25%
Labor
6%
Overhead
4%
Marketing
10%
Subtotal
Projected Earnings
SamsStuffQtr2
Sam’s Stuff 2nd Quarter Budget
Sales Volume:
1,950
2,250
2,100
Item Costs per item Regular Price Total Sale Price Total Holiday Price Total
Revenue
Materials 25%
Labor 6%
Overhead 4%
Marketing 10%
Subtotal
SamsStuffQtr3
Sam’s Stuff 3rd Quarter Budget
Sales Volume:
2,050
2,600
Item Costs per item Regular Price Total Sale Price Total Holiday Price Total
Revenue
Materials 25%
Labor 6%
Overhead 4%
Marketing 10%
Subtotal
SamsStuffQtr4
Sam’s Stuff 4th Quarter Budget
Sales Volume:
2,400
2,800
3,400
Item Costs per item Regular Price Total Sale Price Total Holiday Price Total
Revenue
Materials 25%
Labor 6%
Overhead 4%
Marketing 10%
Subtotal
BankLoans
LoanID
First Name
Last Name
City
Loan Date
Amount
Term
Type
1022
Eileen
Cooper
Santa Fe
2/28/16
20000
0.0700000003
15
Mortgage
1023
3/21/16
1
5000
0.075
1024
3/22/16
10000
1025
Scott
Hunter
Taos
3/1/16
25000
0.065
Automobile
1026
4/1/16
0.077
1027
Ellen
Harper
Albuquerque
3/11/16
0.0625
1028
3/31/16
Other
1029
4/15/16
0.0775
1030
5/1/16
150000
0.0575
1031
Max
Entermann
1/15/16
475000
1032
1/23/16
35000
0.0675
1033
1/31/16
12000
0.08
1034
5/3/16
350000
1035
Helen
Rayus
1/25/16
0.055
1036
1037
Ted
Myerson
2/8/16
525000
1038
Peter
Sanger
2/12/16
10500
0.06875
1039
5/8/16
275000
1040
Rose
Budnick
2/20/16
250000
1041
2/21/16
0.0875
1042
4/18/16
41000
1043
Austin
Powers
3/3/16
56000
0.0725
1044
Barbara
Ringer
2/15/16
1045
3/10/16
129000
1046
4/22/16