Excel Introductory Capstone2 Annual Report | Excel Introductory Capstone2 Annual Report

We can handle all online courses, Business and Management, Business and Finance, Business and Accounting, Human Resource Management, History, English, Literature, Nursing, psychology, Statistics, Information Technology, Applied Sciences, Computer Science and Many more
We will help you in weekly quizzes, assignments, exams, thesis writing, dissertation writing, Myitlab all courses.
We can do complete MyITLab course , Microsoft Word, Microsoft PowerPoint, Microsoft Excel, Microsoft Access
We have worked on all universities dashboards. Following are few Universities that are mentioned
www.umgc.edu/l...
umd.instructur...
savannahtech.b....
blackboard.sc.....
For Help Contact us:
Gmail: [e-mail]
WhatsApp 1: [phone]
WhatsApp Link: wa.me/message/...
Telegram: [phone]
Follow me On my Instagram Page:
/ solutionitlabs
1 Open the Excel file named Student_Excel_Intro_Cap2_Annual_Report.xlsx downloaded with this project.
2 On the Net Sales worksheet, calculate totals in the ranges F4:F8 and B9:F9. Apply the Total cell style to the range B9:F9.
3 Using absolute cell references as necessary, in cell G4, construct a formula to calculate the percent that the Texas Total is of Total Sales, and then apply Percent Style. Fill the formula down through the range G5:G8.
4 In the range H4:H8, insert Line sparklines to represent the trend of each state across the four quarters. Do not include the totals. Add Markers and then in the first row, apply the first Sparkline style.
5 Select the range A3:E8, and then use the Recommended Charts command to suggest an appropriate chart. Click the first Clustered Column chart that uses the state names as the category axis. Align the upper left corner of the chart inside the upper left corner of cell A11, and then size the chart so that its lower right corner is slightly inside cell H24. Apply chart Style 6. As the chart title, type Quarterly Net Sales by State
6 To show the percent that each state contributes to the total sales, select the nonadjacent ranges that represent the state names and state totals. Insert a 3-D Pie chart, and then move the chart to a New sheet. Name the sheet Net Sales Chart
7 Change the Chart Title to Annual Net Sales by State and then change the chart title Font Size to 32. Remove the Legend from the chart, and then add Data Labels that display only the Category Name and Percentage positioned in the Center. Change the data labels Font Size to 14 and apply Bold and Italic. Change the Font color to White, Background 1.
8 Select the entire pie and display the Format Data Series pane. From the 3-D Format gallery, modify the 3-D options by changing the Top bevel and Bottom bevel to the first bevel option in the first row. Set all of the Width and Height boxes to 256 and then change the Material to the fourth Standard type-Metal.
9 Insert a Custom Footer with the File name in the left section and then save your workbook.
10 On the Portland Inventory worksheet, in cell B5, enter a function that will display the average retail price. In cell B6, enter a function that will display the median retail price. In cell B7, enter a function that will calculate the lowest retail price. In cell B8, enter a function that will calculate the highest retail price. Format the range B5:B8 with Accounting Number Format.
11 On the Portland 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.
12 Format the range A13:G19 as a table with headers and then apply Teal, Table Style Light 13. If the style is not available, choose another style. Filter the table on the Sport column to display only the Hiking types. Display a Total Row in the table, and then sum the Quantity in Stock for Hiking items. Type the result in cell B11. Remove the total row from the table and then clear the Sport filter.
13 In cell B10, insert a COUNTIF function to count the number of Hiking items in the Sport column.
14 Add Gradient Fill Blue Data Bars to the range A14:A19. Add conditional formatting to the range G14:G19 so that the cells with text that contains the word Order are formatted with Bold and Italic. Sort the table by Item # from Smallest to Largest.
15 Display the Inventory Summary sheet. In cell B4, enter a formula that references cell B4 in the Portland Inventory sheet so that the Portland Total Items in Stock displays in cell B4. In cell B5, enter a formula that references cell B5 in the Portland Inventory sheet so that the Portland 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 Portland Inventory sheet.