this the link you just have to copy and paste it.

https://1drv.ms/x/s!AkinDDYoZ7pLgVyZUF1Kr11YFtxY

this what you should do?

(Excel Table, Copy Worksheet, Sort, Filter, Subtotals, Pivot Table, Pivot Chart, Chart)

1.In the Cash Receipts worksheet, freeze the top row so the headers remain on the screen as you scroll.

2.Make a copy of the Cash Receipts worksheet, and then rename the copied worksheet as CoffeeHaus Date.

3.In the CoffeeHaus Data worksheet, unfreeze the top row.

4.Create Excel table for the CoffeeHaus Data and rename the table as CashData.

5.Format the Excel table with Light Grey Table Style Medium 25, and then change the Amount

field to the Accounting format with two decimal places.

6.Make a copy of the CoffeeHausData worksheet, and then rename the copied worksheet as

Sort Trn Date. In the Sort Trn Date worksheet, sort the cash receipts by Trn Date, displaying the newest receipts first, and then by Amount displaying the largest amounts first. 7. Make a copy of the CoffeeHausData worksheet, and then rename the copied worksheet as Sort By Day. In the Sort By Day worksheet, sort the cash receipts by Day (use the custom list order of Sunday, Monday, ...), then by Segment (A to Z), and then by Amount (smallest to largest).

8.Make a copy of the CoffeeHausData worksheet, and then rename the copied worksheet as Filter Omit Gifts. In the Filter Omit Gifts worksheet, filter the CashData table to display the cash receipts for all items except Gifts.

9.In the Filter Omit Gifts worksheet, insert the Total row to calculate the average amount of the cash receipts for the filtered data. Change the label in the Total row to Average. Sort the filtered data by descending order by Amount.

10.Make a copy of the CoffeeHausData worksheet, and then rename the copied worksheet as Subtotals. In the Subtotals worksheet, use the Subtotal command to calculate the total cash receipts for each segment in the Amount column.

11.Based on the data in the CoffeeHausData worksheet, make a PivotTable in a new worksheet that shows the total receipts by Day. Format the data area with the Currency format. Rename the worksheet with the PivotTable as PivotTable By Day.

12.In the PivotTable By Day worksheet, insert a Clustered Column PivotChart based on the PivotTable you created. Move the PivotChart to row 3. Remove the legend, and then change the PivotChart title to Receipts by Day of Week.

13.In the Market Summary worksheet, use the data in the range E21:F27 showing the types of grapes cultivated by Michigan wineries to make a pie chart comparing production rates. Embed the pie chart in the Market Summary worksheet covering the range B5:G18.

14.Format the pie chart by removing the chart title, applying the Style 11 chart style, and aligning the legend with the right edge of the chart area.

15.In the U.S. Wine Consumption worksheet, make a combination chart based on the data in the range B3:D15 showing how much wine Americans consume annually. Display the Gallons (millions) data series as a clustered column chart on the primary axis, and then display the Gallons (per Capita) data series as a line chart on the secondary axis.

16.Move the combination chart to the Market Summary worksheet; embed it over the range I30:O43.

17.Format the combination chart by making the following changes:

1.(i)Format the chart with the Style 4 chart style.

2.(ii)Change the chart title to U.S. Wine Consumption and set its font size to 14 points.

3.(iii)Add the primary axis title Gallons (millions) and the secondary axis title Gallons (per

Capita).

4.(iv)Change the font color of the axis titles and scales to match the column and line markers.

5.(v)Remove the horizontal axis title and chart legend.

6.(vi)Change the rotation of the secondary axis title to Rotate Text Down.

7.(vii)Change the primary axis scale to range from 650 to 950 in intervals of 50. Change

the scale of the secondary axis to range from 2.2 to 3.0 in steps of 0.1 units.

8.(viii)Change the fill color of the chart area to light blue and the plot area to white.

9.(ix)Add primary major vertical gridlines to the chart.

Part B Advanced Formulas

(If, And, Or, Not, Sumif, Countif, Averageif, Vlookup, Hlookup, PMT)

18.In the Employee Data worksheet, rename the Excel table as EmpData.

19.In the 401(k) Max Contrib column, use an IF function to determine the maximum 401(k)

contribution for each employee. Employees who are age 50 or older are allowed to contribute up to $22,000 during the year to their 401(k) plan; all employees under age 50 can contribute a maximum of $17,000 during the year.

20.Only full-time (Job Status) employees over the age of 30 (Age) with more than one year of service are eligible for the 401(k) benefit. In the 401(k) Company Match column, enter the IF and AND functions to calculate the 401(k) company match as a percentage of the employee's current salary; use a reference to cell X1 to obtain the 401(k) matching percent rate (3 percent). If the employee is not eligible, enter 0.

21.All full-time (FT) employees are eligible for a bonus. Pay Grade A employees receive $3,000 (cell X2), Pay Grade B employees receive $6,000 (cell X3), and Pay Grade C employees receive $8,000 (cell X4). In the Bonus Amount column, enter nested IF functions to calculate the bonus. For employees not eligible for a bonus, display the text NE.

22.In the Eligible Salary Increase column, enter IF and OR functions to insert the text Not Eligible if the employee's pay grade is C or the employee's job status is a consultant (CN).

Leave the cell blank if the individual is eligible for a salary increase.

23.In the Vision Plan Cost column, enter the LOOKUP function to make an exact match lookup to

calculate the annual vision plan cost. Use the VisionRates data (the range $H$3:$K$4 in the

Lookup Tables worksheet), which contains the monthly vision rates.

24.In the Medical Plan Cost column, enter the LOOKUP function to an exact match lookup to

calculate the annual medical plan cost. Use the Medical Rates data (the range $A$4:$B$10

in the Lookup Tables worksheet), which contains the monthly medical rates.

25.In the Years Service column, modify the formula to incorporate the IFERROR function and

display the message Invalid hire date if an error value occurs. Test the modified formula by changing the date in cell C2 from 8/28/2014 to 18/28/2014. Increase the column width as needed to display the entire message.

26.Edit the Duplicate Values conditional formatting rule applied to the Emp ID column so that the fill color of the duplicate value is formatted as light blue. Test this change by typing 1002 in cell A101.

27.In the Employee Summary worksheet, enter the COUNTIF function in cells B3 and B4 to count the number of female and male employees, respectively.

28.In cells C3 and C4, enter the AVERAGEIF function to calculate the average salary of female employees and the average salary of male employees, respectively. Format the average salary column using the Accounting format with two decimal places.

29.In the Loan Analysis sheet, use PMT function to calculate the monthly payment on a business loan of $300,000 at 3 percent annual interest to be repaid in 15 years.