You are responsible for tracking daily sales. The

  1. Formulas

You are responsible for tracking daily sales. The table on the formulas worksheet lists a few of the transactions for your company. Notice that the sales tax amount and transaction totals are not filled in. Complete the tasks to complete the table.

1.1

Construct a formula in cell D5 to calculate the sales tax amount for transaction 578. Be sure to appropriately reference the transaction amount in cell C5 and the sales tax rate in cell C2 so your formula can be reused for the remaining transactions.

1.2

Copy the formula you used in cell D5 down to calculate the sales tax amount for the remaining transactions.

1.3

Construct a formula in cell E5 to calculate the total amount for transaction 578. Be sure to appropriately reference the transaction amount in cell C5 and the sales tax amount in cell D5 so you can reuse your formula to calculate the total for the remaining transactions.

1.4

Copy the formula you used in cell E5 down to calculate the total for the remaining transactions.

1.5

Use the SUM function to calculate the grand total for all the transactions in cell E18.

2. Statistical Functions

There are 30 Major League Baseball (MLB) teams. The table on the statistical functions worksheet lists the 2019 and 1990 payroll and win totals for each team (notice that four teams were added after the 1990 season). Some MLB fans complain because the league does little to regulate the amount of money teams can pay for salaries. They argue that the teams that spend the most money will win the most games. This would put teams from small markets (that earn less revenue) at a disadvantage. Complete the tasks by inserting your formulas (or responses) in column I for each task to see if small market teams are at a disadvantage.

2.1

Use the COUNT function to calculate the number of MLB teams in 1990 (range G4:G33).

2.2

Use the COUNTA function to calculate the number of MLB teams (use range G4:G33).

2.3

Use the SUM function to calculate the total amount of salaries paid in 1990.

2.4

Use the SUM function to calculate the total amount of salaries paid in 2019.

2.5

Use the AVERAGE function to calculate the average salary for the teams in 1990.

2.6

Use the AVERAGE function to calculate the average salary for the teams in 2019.

2.7

Use the MAX function to determine the maximum team salary amount in 1990.

2.8

Use the MIN function to determine the minimum team salary amount in 2019.

3. Finance

Complete each task by inserting the appropriate function, referencing the appropriate cells in the task data, in the specified cells.

3.1

You are interested in purchasing a home. What will your monthly payment be if you take out a $175,000 mortgage for 30 years (360 months) at 4.25% interest?

· Reference the loan information in the "Task 1 Data" cells as the arguments for your function.

· Remember to divide the interest rate by 12 (to calculate the monthly interest rate) in your function.

3.2

You are interested in purchasing a home. You can afford $1200 a month as a mortgage payment. How much can you pay for a home assuming a 30 year (360 months) loan at 4.25% interest?

· Please reference the loan information in the "Task 2 Data" cells as the arguments for your function.

· Remember to divide the interest rate by 12 (to calculate the monthly interest rate) in your function.

3.3

You are interested in purchasing a home. You have been quoted monthly payments of $950 for a 30 year mortgage. Your original loan amount is $212,000. What is the interest rate you will pay on the loan?

· Use the RATE function.

· Reference the loan information in the "Task 3 & 4 Data" cells as the arguments for your function.

· Remember to multiply the computed nominal interest rate by 12 (to calculate the annual interest rate).

3.4

What is the effective annual rate of the loan you worked with in task 3?

· Reference the nominal rate you calculated in the previous task.

3.5

You are interested in saving for a trip when you graduate in three years. You can save $75 each of the next 36 months and earn 2.75% interest on your money. How much money will you have in your savings account in 36 months for your trip?

· Reference the loan information in the "Task 5 Data" cells as the arguments for your function.

3.6

You are interested in purchasing a home. You will take out a mortgage of $310,000 to pay for the home and pay 4.5% interest. What will your monthly payment be if you take 15 years to pay off the loan?

· Reference the loan information in the "Task 6-8 Data" cells as the arguments for your function.

3.7

What will your monthly payment be if you take 30 years to pay off the loan you worked with in task 6? (Please reference the loan information in the "Task 6-8 Data" cells as the arguments for your functions.)

· Reference the loan information in the "Task 6-8 Data" cells as the arguments for your function.

3.8

How much money will you save if you pay off the loan in 15 years instead of 30 years?

· Reference the total payment amounts in cells C38 and D38 to calculate the difference.