Need this by 11:59PM (8 hours) will pay 12-20 bucks. Thanks!
1Start Excel. Open the downloaded Excel file named 2013Excel_exam_starter.xlsx. Save it as:
Excel2013_exam_final. Remember WHERE you save it and to save frequently!
2On the Net Sales worksheet, in the range F4:F9, use a function to calculate the annual total sales for each state.
3In the range B9:E9, calculate totals using a function.
4Apply the Total cell style to the range B9:F9. (first row, last col under Titles and Headings)
5In cell H4, insert a Line sparkline using the data range B4:E4. Format the sparkline by showing markers. Copy the sparklines down through cell H8.
6On the Seattle Inventory worksheet, in cell G14, enter an IF function to determine the items to be ordered. If the Quantity in Stock is less than 50, then the cell should display Order. If not, then the cell should display OK. Copy the function down through cell G19.
7Add conditional formatting to the range G14:G19 so that the cells that are equal to Order are formatted as Yellow Fill with Dark Yellow Text.
8In cell B6, enter a function that will display the median retail price. Format the range B5:B8 with Accounting Number Format.
9Format the range A13:G19 as a table with headers.
10Filler the table on the Sport column to display only the Skiing types. Apply a Total Row in the table, and then use a function in cell A20 to add the Quantity in Stock for Skiing items.
11Type the result in cell B11.
12On the Denver Inventory worksheet, remove all filters from the table and then convert the table to a normal range.
13On the Denver Inventory sheet, in cell F19, insert a function to count the cells that contain Fitness
14Display the Inventory Summary sheet. In cell B4, enter a formula that references cell B4 in the Seattle Inventory sheet so that the Seattle Total Items in Stock displays in cell B4.
15In cell B5, enter a formula that references cell B5 in the Seattle Inventory sheet so that the Seattle Average Price displays in cell B5. In cells B6, B7, and B8, enter similar formulas to reference the Median Price, Lowest Price, and Highest price in the Seattle Inventory sheet.
16 Ont he Anul Expenses sheet, using absolute cell references as necessary: in cell G5, construct a formula to calculate the % of Total by dividing the Sales Expense Annual Total by the Annual Totals by Quarter. Fill the formula down through the range G6:G10
17Use Format Painter to copy the formatting from cell A2 to A20.
18In cell B23, enter a formula which will reference the value in F10.
19Using absolute cell references as necessary, in cell C23, construct a formula to calculate the projected expenses for 2018 after the Forecasted increase in cell B21 is applied. Fill the formula through cell F23.
Apply Accounting number format with 2 decimals.
Autofit columns so that all values display.
20Using the data in the nonadjacent ranges A4:E4 and A10:E10, insert a 3D Pie Chart. Position the upper left corner of the chart slightly inside cell J2. Change the height of the chart to 3 inches and the width of the chart to 6 inches.
On the 3D pie chart, hide the legend. Show percentage, category, and leader lines only. Fill the label text with gradient fill. Leave all other settings at their defaults.
22Display the Sales Reps sheet. By using Flash Fill and inserting/deleting columns as necessary, place the last names in column A, the first names in column B, and then merge and center the title Sales Reps across the two columns.
23Ensure that the worksheets are named as follows, correctly spelled, and placed in the following order in the workbook: Net Sales; Seattle Inventory; Denver Inventory; Inventory Summary; Annual Expenses; Sales Reps.
Insert sheet tab name into center field of the footer on all worksheets.
24Color the sheet tab for the Inventory Summary sheet Dark Red. (First color under Standard Colors.)
25Save the workbook. Close the workbook and then close Excel. Upload your workbook as directed.
2 freelancers are bidding on average $25 for this job
Hi, I am a qualified excel expert. I shall be glad to share my previous experience however, need you to allow me on chat. Send me the links to download the files. Regards Kumail