W5 1: Scenario You have just been hired as an office manager for a local busines

W5 1:
Scenario
You have just been hired as an office manager for a local business. You notice the company collects order information in Excel and you realize this information can be helpful in informing business decisions if it was sorted and had conditional formatting applied so valuable information stands out more easily.
Requirements
Orders Sheet
Order Date – This column shows the date when the order was created; please apply number formatting to show a short date (e.g. M/D/YY).
Ship Date – This column shows the date when the order was shipped to the customer; please apply number formatting to show a short date (e.g. M/D/YY).
Ship Mode – This column shows which carrier the order was shipped with (FedEx, UPS, or USPS).
Customer Name – Replace the customer named “Kaylene Kwak” with your name.
New City – Create a column at the end called “New City” and use a formula that references the data in the “City” column. Your formula should replace all instances of “San Antonio” with the city where you currently reside. As it is copied down (using the Auto Fill feature) it should transfer over all other cities as currently named.
City, State, and Region – Sort the spreadsheet by region (ascending), then by state (ascending), and then by new city (ascending).
Category – Use conditional formatting to highlight the entire row with a color of your choice if the category is “Technology”. Please be careful where the $ goes in the conditional formatting custom formula.
Retail price per unit – This column shows the retail price of each unit.
Quantity sold – Add a green checkmark (conditional formatting icon set) for every customer that ordered five or more items.
Discount percent – It is the percentage by which the retail price is discounted. For example, if the retail price is $10 and there is a 40% discount the customer would only pay $6.
Total Sales – Use conditional formatting to highlight in gold the top 10% of cells in the total sales column.
Please note that the video talks about highlighting the top 10% of customers but it should be the top 10% of total sales. Additionally, their comments about the protection are not quite accurate so read the instructions below carefully.
Order Number – Create an additional column at the end called “New Order Number” that uses a formula to add a region code at the front of each order number. The region codes are: East = 101. South = 201. Central = 301. For example, the order number, “496-1850” from Central would look like “301-496-1850”. The region codes might change so set them up as input cells that can be altered in the future. Consider using named ranges.
Dashboard
Create a dashboard sheet that allows you to look up a customer name (on a drop down list) and see their order information (Output cells are: Order Date, Ship Date, Ship Mode, New City, State, Region, Category, Quantity, Discount, Total Sales, and New Order Number).
Protect the dashboard sheet so that the customer name is the only cell that users may select and edit. Hint: Make sure to test that the protection prevents other edits and that a new name can be selected before turning it in.
Follow the Standards of Professionalism Links to an external site. to clean up the workbook and make it look sharp. For example, on all sheets, check that values are formatted correctly. Ensure all columns are wide enough to see their information clearly.
W5 2:
Scenario
Congratulations! You have now been promoted to office manager at the car dealership you worked for in an earlier project. You have a meeting with the owner and need to be able to show how well the sales team and the different stores are doing.
As you work, use the Standards of Professionalism Links to an external site. to clean up the workbook and make it look sharp. This includes labeling and formatting the worksheets appropriately. On all sheets, ensure numbers have the proper format and please check that all columns are wide enough to display the data.
The logic functions we have learned should be helpful. For the dropdown lists, you can get a list of unique salespersons, store names, and car makes using the UNIQUE formula or the Remove Duplicates function.
Requirements
Sales sheet
Using Excel formulas, create a new column called “New Salesperson” that replaces all salespeople named “Arthur” with your first name.
New sheet: Dashboard
Create a drop-down list that includes the names of all fourteen new salespeople. For the salesperson selected in the drop-down list, calculate the following on the dashboard:
The total of “Sales Price” for that salesperson
The average of that salesperson’s “Sales Price”
The total number of cars sold by that salesperson
Create another drop-down list that includes the names of all three stores. For the selected store, calculate the following on the dashboard:
The total of “Sales Price” for that store
The average of that store’s “Sales Price”
The total number of cars sold at that store
New sheet: Pivot tables
Create the following three pivot tables. For each one, be intentional about which placement for row and column headings makes the data easiest to read.
The total “Sales Price” for each new salesperson and inventory type.
The number of cars sold by month and store.
The number of buyers (units sold), by gender, for each car make and model.
W6 1:
Scenario
You are teaching a class with 20 students. The class has 20 projects, 4 tests, and a final exam. The semester has just ended and you want to be able to create a dashboard that allows you to see individual student performance.
You plan to paste in a new class list next semester so you can see students’ progress along the way and know who may need some extra help. You also plan to share this helpful workbook with your fellow teachers so follow the Standards of Professionalism Links to an external site. to clean up the workbook and make it look sharp. On ALL sheets, check that values are formatted correctly. Ensure all columns are wide enough to see their information clearly.
Requirements
Grades sheet
Use text functions to break apart the first and last name of each student into the provided columns.
Calculate each student’s final score and the class average in column AD, all the way to the right.
Add a new calculation in column AE for the Final score percent.
Final score percent = total points earned / total points possible
Dashboard sheet
The dashboard is set up for you and should allow you to spend most of your time filling in the correct formulas, number formatting, and—most importantly—creating charts!
Please ensure you solve for all of these items:
In the shaded section below the label student name, create a drop-down list that allows the user to pick a student from the list on the Grades sheet. Many of the other functions used on the dashboard will reference this cell so consider using a named range.
In the merged cells below the label Final Score use a lookup function to get the selected student’s final score from Grades sheet—formatted as a percent (for example: 89.7%).
In the merged cells to the right of the label Student ID use a lookup function to get the selected student’s Student ID from Grades sheet.
For each of the major sections (Final Exam, Tests, First half projects, and Second half projects) you will need to:
Use functions to get the selected student’s score and calculate the percent based on the points possible. This should be done in a single cell.
Use functions to get the class average percent for the given assignment.
Create a chart to clearly display the student’s performance in relation to the class average. For the final exam, test, and project charts, the student’s scores must be shown using columns with the class average shown as a dot.
Lastly, please make some alterations to the look and feel of the dashboard so that it’s unique to you. You are welcome to add to it but it must retain the basic requirements listed above.
W6 2:
Scenario
You have been asked to provide recommendations to help a local restaurant increase sales and customer satisfaction. You are also responsible to create some dashboards that make it easy to understand what our survey and sales data may help us to learn. As always, you are expected to follow the Standards of Professionalism Links to an external site., including the use of spellcheck.
The restaurant administered a survey to current and potential customers to discover key information, including preferences, shopping patterns, and demographics (such as age and gender).
The first sheet of the project workbook contains a placeholder for the questions asked in the survey and the second sheet shows the question text (on row 1) on and the results from all respondents. The results in the spreadsheet are in a partially organized format because that is how the data came from the online survey program when exported into Excel. You will need to explore the data to determine how it can be useful. You are welcome to rearrange and reformat the sheet.
The company believes that the data may be useful, but they do not know how to manipulate or summarize the data. This is where they need your help. They have hired you to help them organize and summarize the data so that it is meaningful, easy to interpret, and useful for their marketing decisions.
Requirements
Survey questions
Before creating pivot tables, take some time to get familiar with the data by copying the questions from the Survey Results sheet to the Survey Questions sheet. This will allow you to get a feel for the survey structure before trying to work with the data.
Survey dashboards (one sheet per survey question)
Use the data from the Survey Results sheet. Create a pivot table and pivot chart for your choice of five survey questions (excluding the age and gender questions). Every table and chart needs to be connected to a slicer for gender or age (meaning there will be one slicer on each of the five new sheets). Label each sheet so the user can quickly determine which question it corresponds to.
Depending on the type of data provided in the responses (text or numeric) you will need to set up the pivot table differently.
Recommendation sheet
On the Recommendations sheet please provide what you feel are the three most important recommendations to the restaurant management team to help them increase sales and customer satisfaction. The pivot tables and charts you made are a great place to look for insights that could lead to recommendations. For each recommendation, please cite the analysis you did that supports it (see example row).