You have been asked by the Sales Manager to do analysis for a company that sells three products (JT1252, NX900, RJ52) in four regions (North, South, East, and West). The data you have been given is for 2021 and you must follow these instructions:

1. Use the file Pivot Data Data.txt posted on Blackboard as the datasource. You may assume the raw data is correct and it is not necessary to format this worksheet - just leave it as-is - but you must rename your worksheet to 'Raw Data' and make it the first one in your spreadsheet. (1 pt)

3. Create a worksheet labeled 'Pivot Table' and create a Pivot Table that has the Sales Person as the filter and shows product, region, and sales person by rows (in that order top to bottom) and the value for the column. Show the summation for the 1st and 4th quarters of 2021 for sales reps William and Ryan. Format the cells with no decimals but with commas in the appropriate places. (3 pts)

4. Create a worksheet labeled 'Pivot Chart' and create a Pivot Chart that shows the average sales of James and Ryan for 2021 for each region. Format the pivots chart so it displays the regions as: North, West, East and South while displaying the sales people (e.g. it should be region, sales person, in order top to bottom). It is not necessary to add a chart title because of the dynamic nature of the Pivot Chart. (3 pts)

Goal Seek (4 pts) 1. Name a worksheet as 'Goal Seek' and label cells as shown below:

A	B
Loan Repayment	Base Scenario
Interest Rate
# of Payments
Principal
Payment
Total Payment
Total Interest

Using the following values, calculate Payment, Total Payments, and Total Interest for your Base Scenario. Format all payment fields as currency ($) with appropriate decimals and format all other fields correctly. Payment is assumed at the beginning of the period.

Interest Rate 5.75% (annual interest rate)| # of Payments - 360 (30-year mortgage) | Principal - $925,999 (1 pt)

When you present these figures to your clients, they say the maximum monthly payment they can - afford is $4750. Using the same initial values for Interest Rate, # of Payments, and Principal, create three additional columns and use Goal Seek to adjust the following variables for the maximum payment the client can afford (i.e., you need to create three scenarios, label them 'Revised 1', 'Revised 2', 'Revised 3'):

Revised 1 - Principal (1 pt)

Revised 2 - # of Payments (1 pt) Use ROUNDUP to make it a whole number (will adjust the payment slightly)

Revised 3 - Interest Rate (1 pt) Format it to 2 decimal points Solver (5 pts) 1. Name a worksheet '

Solver 1'. Using the following data, use Solver to calculate weekly Maximum Revenue while staying within the following prices and constraints (4 pt):

Product	Price
Regular	$5.50
Cappuccino	$5.67
Premium Latte	$5.81
Premium Mocha	$6.17

Constraints:

Product	Cups/week
All	645
Max Cappuccino	195
Max Premium Cups	235
Max Premium Mocha	110

2. Name another worksheet 'Solver 2'. Using the data above, use Solver to calculate Revenue = $3,180. You will need to solve the problem and then make it into a integer using the constraint function (1 pt).