I need help in Excel

exam.xlsx

Instructions

1. Save this workbook As YourName-Exam1.

2. Each question is assigned with different points, allocate your time wisely.

3. Save after finishing each question to reduce the possibility of losing all your hard works due to computer problems.

4. The Test Time is 100 minutes

5. Submit the Finished workbook to Exam #1 - Workbook Submission Assignment on Canvas.

10 points. Write a formula in Cell B6 that multiply the value in the column header by the value in the row header. The formula in B6 will be copy to the rest of cells to complete this 10X10 multiplication table.
Multiplication Table
	2	4	6	8	10	12	14	16	18	20	Completed Table
2	4
4
6
8
10
12
14
16
18
20

25 Points. Write formulas in green shaded areas to fill the following summary reports. The formulas in G13 and J13 will be copy down the rows to complete the table.
Transaction Records
Date	Invoice Number	Sales Rep	Revenue	Summary of Sales		The number of Revenues between:
10/7/13	67002	John	$1,396.00	The Number of Revenues (count)		Lower	Upper	Count
10/8/13	67009	Frank	$1,084.00	Total Revenues		>0	<=1000
10/7/13	67005	Kevin	$1,245.00	The Lowes Revenue		>1000
10/7/13	67005	Kevin	$467.00	The Largest Revenue
10/7/13	67004	Eve	$1,279.00	The Average Revenue
10/8/13	67007	Peter	$507.00
10/8/13	67007	Peter	$819.00
10/7/13	67004	Eve	$1,160.00	Average Revenue by Invoice Number		Total Revenue by Sales Rep
10/7/13	67002	John	$1,694.00	Invoice Number	Total	Sales Rep	Total
10/7/13	67006	Zack	$1,372.00	67002		John
10/8/13	67008	Mary	$1,412.00	67003		Frank
10/7/13	67006	Zack	$1,799.00	67004		Kevin
10/8/13	67007	Peter	$1,395.00	67005		Kevin
10/8/13	67009	Frank	$1,466.00	67006		Eve
10/8/13	67007	Peter	$827.00	67007		Peter
10/8/13	67008	Mary	$753.00	67008		Peter
10/7/13	67003	Ada	$1,080.00	67009		Eve
						John
						Zack
						Mary
						Zack
						Peter
						Frank
						Peter
						Mary
						Ada

20 Points. 1. Enter a formula in D10 to calculate commission for Saleperson in A10. The commission is based on the Commission Table, if Total Sales is less than $50,000, the commission is 3% of the Total Sales, if Total Sales is greater than or equal to $50,000, the commission is 4% of the Total Sales. 2. Enter a formula in E10 to calculate Total Salary for the Saleperson in A10. Total Salary equals Base Salary + Commission. 3. Copy formulas in D10:E10 to D11:E23 to complete the calculation of Commission and Total Salary. 4. Use Conditional Formatting to highlight Salesperson records (entire row) with Total Salary > $50,000. You can pick any color for the highlighting.
Commission Percentages
Total Sales < $50,000	3%
Total Sales ≥ $50,000	4%
Commisson Threshold	$50,000
Expert Software Company
Salesperson	Total Sales	Base Salary	Commission	Total Salary
Adams, John	$98,000	$35,000
Barber, Maryann	$24,000	$35,000
Boone, Dan	$39,000	$50,000
Borow, Jeff	$56,000	$35,000
Brown, James	$81,000	$50,000
Carson, Kit	$17,000	$50,000
Coulter, Sara	$22,000	$35,000
Fegin, Richard	$72,000	$35,000
Ford, Judd	$64,000	$35,000
Glassman, Kris	$25,000	$35,000
Goodman, Neil	$70,000	$50,000
Milgrom, Marion	$11,000	$50,000
Moldof, Adam	$68,000	$50,000
Smith, Adam	$100,000	$50,000

15 Points. 1. Enter a 3D Reference formula in B6 to total the Earrings in 1 Qtr from 2013 and 2014. 2. Copy the formula in B6 to the rest of cells. 3. Make sure that the format of the report is kept without changes. (Single and double blue borders)
Baubles and Beads Jewelry Co-op
2013 and 2014 Sales Totals
	Qtr 1	Qtr 2	Qtr 3	Qtr 4	Qtr 1
Earrings
Necklaces
Bracelets
Other Jewelry Items
Total

Baubles and Beads Jewelry Co-op
2013 Sales
	Qtr 1	Qtr 2	Qtr 3	Qtr 4	Total
Earrings	$8,767.87	$10,202.13	$12,338.82	$14,499.30	$45,808.12
Necklaces	12,110.72	8,784.89	14,812.97	8,935.78	$44,644.36
Bracelets	3,838.01	4,354.47	5,121.39	4,517.63	$17,831.50
Other Jewelry Items	3,036.12	3,423.07	4,258.31	3,730.40	$14,447.90
Total	$27,752.72	$26,764.56	$36,531.49	$31,683.11	$122,731.88

J. Quasney Sales Analysis &D Page &P of &N

Baubles and Beads Jewelry Co-op
2014 Sales
	Qtr 1	Qtr 2	Qtr 3	Qtr 4	Total
Earrings	$6,691.02	$7,901.91	$6,171.20	$7,837.75	$28,601.88
Necklaces	9,742.16	10,700.82	9,255.51	7,889.48	$37,587.97
Bracelets	4,253.19	5,471.07	4,783.08	4,039.15	$18,546.49
Other Jewelry Items	3,341.40	2,859.08	2,845.78	2,309.39	$11,355.65
Total	$24,027.77	$26,932.88	$23,055.57	$22,075.77	$96,091.99

J. Quasney Sales Analysis &D Page &P of &N

15 Points. Part 1. We wish to purchase a new tractor for work on our family farm. We need to know that if interest rates fluctuate we can still afford to pay for the tractor. So we need to know what our loan repayments will be, what our total repayments will be and how much interest we are paying. We have received a loan quote for $30,000, the interest rate is 8.97% and the term of Loan is 10 years. Please create a Loan Calculator to calculate Monthly Payment, Total Amount Paid and Total Interest Paid for this loan.
Tractor Loan
Amount of loan	$30,000
Interest Rate	8.97%
Term of Loan (Years)	10
Number of Payments (per Year)	12
Monthly Payment
Total Loan Payment
Total Interest Payment
10 Points. Part 2. If we pay $500 per month to repay the loan, how many months are needed to pay off the loan?
Answer in months ----->
15 Points. Part 3. Create a one-way data table to show the impacts of varying interest rates (from 7.50% to 10.00% with increments of 0.50%) on Monthly Payment, Total Loan Payment, and total Interest Payment.
10 Points. Part 4. Create a two-way table to show the impacts of varying interest rates (from 7.5% to 10.00% with increments of 0.50%) and varying terms of loan (from 8 years to 16 years with increments of 2 years) on Monthly Payment.