[SOLVED] The invoices of food sales for your company

Project 1 Sales 2019 Sheet
Overview
You track the invoices of food sales for your company. You need to format and calculate sale totals.
Tasks
1 Open the Practice Exam 1 workbook.
2 Rename the Sales sheet to 2019 Sales.
3 Without deleting the text, remove the hyperlink from A3 on the 2019 Sales sheet.
4 Apply the Title Style to cell A3.
5 Wrap the text in row 6, then center it.
6 Merge without centering A3:G3. Then make it 24pt. and bold.
7 Create formulas to complete each of the invoice totals in the Total column.
8 Create Named Ranges: Name: SaleAmount Cell range: D7:D24
Name: TaxAmount Cell range: E7:E24
Name: ShipAmount Cell range: F7:F24
9 In cell K7, use the correct named range (SaleAmount) in a function to calculate the total sales of the items.
In cell K8, use the correct named range in a function to calculate the total tax.
In cell K9, use the correct named range in a function to calculate the total shipping.
10 Format K7:K9 as Currency with 2 decimals.
11 Set the print orientation to Landscape.
12 Type your full name in C25.
13 Without deleting the column or any of the data, modify the Name column so it doesn’t show.
Project 2 Addresses Sheet
Overview
You need to import customer addresses and make them reader friendly.
Tasks
1 Create a new sheet and name It Addresses.
2 On the Addresses sheet, import the text file 2019 Employee addresses. It is tab delimited and has headers. Place the import in cell A1.
3 In the cell F2, use a function to insert only the State abbreviation from E2.
In the cell G2, use a function to insert only the Zip from E2.
In the cell H2, use functions to combine the customers first name and last name in all capitals. The first name and last name should be separated by a space.
Use Auto Fill to bring the formulas in F2:H2 down through row 19 if necessary.
4 Autofit column H.
5 Change the column width of G to 8.
6 Create a page break before row 13.
Repeat row 1 to print on the top of each page.
Create a center header: Employee Addresses that is bold and 14pt.
Create a center footer: the page number (without the word Page).
Scale the data on the sheet to fit the width of 1 page (NOTE: Do not fit the height or it will cancel the page break.)
Project 3 Annual Sheet
Overview
You want to highlight the highest Annual Sales and display certain products in a chart format.
Tasks
1 On the Annual sheet, delete rows 3:4.
2 In the cells, A1:F1, merge and center the title.
In the cells, A2:F2, merge and center the date.
3 Format the text in cells A1:F2: 14pt, Fill: Green, Accent 6, Lighter 60%
4 Format the numbers in cells B5:F14: Accounting format no decimals.
5 In cell G5 insert a Column Sparkline that displays the sales for the 4 quarters. Fill through all the products.
In B15:E15 create Line Sparklines comparing the 9 products in each quarter.
Add a red high point marker to the Line Sparklines.
Change the height of row 15 to 30.
6 On the Annual worksheet, use the data in cells A4:E13 to insert a 3-D Clustered Column Chart beginning in cell A17. The products should be on the horizontal axis.
7 Display only the categories (by filtering the chart): Baseball Bats, Olympic Frisbees, Kayaks, Tennis Racquets.
8 Switch the Row/Column data so the Quarters are on the category axis.
9 Create the Chart Title: Yearly Sales and name the chart: 4Qtrs
10 Apply the Chart Style 3.
11 Using a type of format that will update even if the values change, highlight the top three quarterly product sales with a custom format: orange fill and black text.
12 Copy the cells A4:F14 and Paste with Transpose in cell A38.
In row 38, Wrap the titles and make sure all the numbers fully display.
13 Add Alt Text to the chart of your last name.
Project 4 Ledger Sheet
Overview
You need to analyze customer’s bank transactions for certain expenses.
Tasks
1 On the Ledger sheet, create a table from the data in A3:E66.
Apply the style Orange, Table Style Medium 3.
Sort the data by Name (A to Z) and Date (oldest to newest). It is only one sort.
2 In cell H4 use a formula to find the number of expenses for rent.
In cell H5 use a formula to find the number of expenses on or before 2/15/2019.
Hint: COUNTIF.
3 In cell H7 use a formula to find out the total amount of transactions for rent.
In cell H8 use a formula to find out the total amount of transactions on or after 2/16/19. Hint: SUMIF.
4 Display the Total of all expenses by using the Total Row.
5 Give the table a name with your firstIntitialLastName, e.g. JSmith.
Hint: On the Table Tools, Design Tab, Properties group.
Project 5 Clothing Expenses Sheet
Overview
You need to analyze customer’s bank transactions for clothing expenses.
Tasks
1 On the Clothing Expenses sheet, remove the formatting from the table (using Table Style/Clear).
Convert the table (A3:E66) to a range.
2 Change all the occurrences of Mishaw, Renee to Menshaw, Rene
3 Filter the data in cells A3:E66 for Clothing expenses that occurred after 2/3/2019.
4 Add a print header with the sheet name on the right (use the element, don’t type the name).
5 Add a left print footer of your full name.
6 Add the properties for Subject: Practice Exam 1 and Comment: Upload this file on completion
7 Export the data on the sheet as a PDF. Name it with your last name, first initial, and Clothing Expenses (e.g. SmithJ Clothing Expenses).
8 Put the worksheets in the following order: 2019 Sales, Ledger, Clothing Expenses, Annual, Addresses.
9 Create a new sheet and change the sheet name to your name. Then hide the sheet.
10 Save the workbook with your last name, first initial, and workbook name Practice Exam 1 (e.g. SmithJ Practice Exam 1). Submit the workbook and the PDF via the link on the Week 10 – Review and Practice Exam 1 folder.