This is the assignment question. i have the excel data file but could not post here but i can provide it if there is any other way

Note (Important): (1) This is an individual assignment. It accounts for 5% of your grade. (2) Your final solution should be ONE Microsoft Excel 2016 file (.xlsx file) that is submitted via Blackboard. (3) Pay attention to details: all questions are detail-oriented to test specific skills. Make sure you read the instruction word by word carefully. Otherwise, you might lose points for failing to fulfill any of the clearly specified requirements. (4) You may want to submit your solution at least 30 minutes before the deadline. Make sure you click Submit rather than Save Draft to actually submit. You are required to double-check your submission by downloading it and opening it to see if it is your final version. You will be responsible for any technical difficulty or mistake on your side that results in submitting the wrong work or your work not being submitted or being accepted as late. No exceptions will be made. (5) Make good use of the videos and tutorials. Assignment #1: Microsoft Excel (Total: 50 pts) This is an actual dataset based on the Monthly House Sales Report from U.S. Census Bureau (https://www.census.gov/topics/housing. html/), showing the monthly sales of single-family houses in four regions of United States from 2010 to 2016. You are hired by US Census Bureau as an analyst intern and you have been asked to use your analytical skills and expertise with Excel to complete the following tasks. Q0. Downloaded the data (HouseSales_2010_2016.xlsx) from Blackboard. Rename it exactly as your last name followed by your G number without G and then followed by suffix _Excel_1. There should not be extra spaces or underlines in between. For example, the files name should be like Hampe12345678_Excel_1.xlsx. You will work on Q1-Q11 on this Excel file and then submit it via Blackboard. (1 pts) Q1. (Freeze Panes) Create a copy of the SingleFamilyHouse worksheet and name it Q1. In the Q1 worksheet, freeze the top label row. (1 pts) Q2. (Conditional Formatting) Create a copy of the Q1 worksheet and name it Q2. In the Q2 worksheet, highlight the sales less than 4 (in Thousands of Units) with Light Red with Dark Red Text. Do not delete any record. Results created not using Conditional Formatting will get zero point. (1 pts) Q3. (Sorting) Create a copy of the Q1 worksheet and name it Q3. In the Q3 worksheet, sort the records by Region (in the ascending order) as the first level of sorting AND by Sales (in the descending order) as the second level of sorting so that one can see for each region which month yields the best sales. (2 pts) MBUS 302 Microsoft Excel Assignment #1 Page 2 of 3 Q4. (AutoFilter) Create a copy of the Q1 worksheet and name it Q4. In the Q4 worksheet, use AutoFilter to only display the sales data for the South region in the first quarter (i.e., January, February, and March) AND sort the data by sales in the descending order. Do not delete any record. Results created not using AutoFilter will get zero point. (2 pts) Q5. (Chart) Create a copy of the Q1 worksheet and name it Q5. In the Q5 worksheet, create a column chart to answer this business question: how does each region perform compared to each other in October 2015? The region should appear on the horizontal axis. Add a proper title for the chart and proper descriptions of the horizontal and vertical axes. Results created not using AutoFilter to only show the relevant sales records first will lose points. (3 pts) Q6. (Chart) Create a copy of the Q1 worksheet and name it Q6. In the Q6 worksheet, use line chart to answer this business question: how does the monthly sales trend for Midwest region in year 2015 differ from that in year 2016? You need to show the monthly sales trend for year 2015 in one line and the monthly sales trend for year 2016 in a second line within the same graph. The month name (NOT numerical month) should appear on the horizontal axis. Add a proper title for the chart and proper descriptions of both axes. Results created not using AutoFilter to only show the relevant sales records first will lose points. (7 pts) Q7. (Subtotal) Create a copy of the Q1 worksheet and name it Q7. In the Q7 worksheet, use Subtotal command to answer this business question: what is the total sales across all regions in each quarter of each year? Namely, you need to show the total sales for 2010 Q1, 2010 Q2, 2010 Q3, 2010 Q4, 2011 Q1, 2011 Q2, etc. Hint: you need to sort the data properly first; and then choose the right At each change in column for Subtotal. Results created not using the Subtotal command will get zero point. (7 pts) Q8. (PivotTable) Create a copy of the Q1 worksheet and name it Q8. In the Q8 worksheet, first use PivotTable to answer this business question: what is the total sales across all regions in each quarter of each year? Your numbers should match the numbers in Q7. Then further design the PivotTable to answer this business question: within each year, what is the total sales for each region in each quarter? Make sure that the PivotTable is near the top of the worksheet. (7 pts) Q9. (PivotChart) Create a copy of the Q1 worksheet and name it Q9. In the Q9 worksheet, add a PivotChart to answer this business question: how is the overall sales in year 2014 distributed among the different regions? Make sure that the PivotTable and the Pie Chart are near the top of the worksheet, and the Pie Chart has the percentage data labels and a meaningful title. Hint: You should always use the entire data to create the PivotTable or PivotChart first and then make any necessary filtering inside the PivotTable. (7 pts) MBUS 302 Microsoft Excel Assignment #1 Page 3 of 3 Q10. (Formula/Function) Create a copy of the Q1 worksheet and name it Q10. In the Q10 worksheet, do the following: (9 pts) (1) Use formulas to display the average sales, the maximum sales, and the minimum sales number across all the individual sales numbers. There is only one number for average, one number for maximum, and one number for minimum. (3 pts) (2) Create a column labeled Less than Average to the right of the Sales (in Thousands of Units) column and use formula to display Yes if sales figure is less than the average sales figure and No otherwise. To specify the condition formula in the IF function, you have to use absolute reference which refers to the cell of the average sales figure. Results created not using formulas will get zero point. Results created not using absolute reference will lose points. (3 pts) (3) Then use formulas to count how many sales figures is less than the average AND how many sales figures reaches at least the average. (3 pts) Q11. (Creativity) Create a copy of the Q1 worksheet and name it Q11. In the Q11 worksheet, write down a business question that is not covered in previous questions and can be answered by using PivotTable or PivotChart. Your business question should be in the form of a question a manager or supervisor might be interested in as seen in Q8, Q9, etc. Then add the PivotTable or PivotChart analysis to answer your business question (3 pts)