Beginner Level Excel Worksheet Creation -- 2
Budget: $10 – $40 USD
I'm in need of an Excel worksheet It is already in a wook book split into 3 parts with a total of 32 questions. The questions are designed for a beginner level course, so they're not overly complex.
Key Requirements:
you are a financial analyst at a large corporation responsible for managing and analyzing the company's product reports and financial data. Your goal is to set up a dashboard to display the needed information.
Ideal Skills:
- Proficiency in Excel
- Experience in creating educational materials
- Competence in trend analysis
- Ability to work with financial data
Heres the Project: You are a financial analyst at a large corporation responsible for managing and analyzing the company's product
reports and financial data. The company deals with various products in different business segments and countries. Your
task is to create a comprehensive dashboard that provides insights into product performance, profit analysis, and financial
projections.
The final dashboard should not only be well-organized and visually appealing but should also provide valuable insights
into product performance, profit distribution, and future financial projections. Once completed, save the Excel workbook as
"Report Backlog Last Name First Name" and submit the workbook to the file submission folder.
Complete the following steps:
Part I
1. Enter your name in cell range C3:D3 on the Dashboard tab.
2. Utilizing the Get Data Function, import the Report Backlog Text File into a new worksheet.
3. Ensure that the worksheet is named Report Backlog and the imported data table is renamed Report_Backlog.
Move the Report Backlog sheet to the right of the Dashboard sheet.
4. Insert a blank column in front of column A and insert a blank row above row 1.
5. Apply a table style of your choice.
6. Remove duplicate records in the Report No. column/field. (Hint: Five duplicate records should be removed and
you should now have records down through row 527)
7. In the Report Name column, combine the Report No. and Product fields to create the individual report name.
Report names should include “Report Number – Product”. If necessary, expand the column to ensure the
combined data is easily readable.
8. Using the VLOOKUP function, find the Cost of Good and Original Price values using the table on the
Reference Tables worksheet.
9. In columns J - L, utilize simple mathematical equations to calculate the Gross Sales, Cost of Goods Sold
(COGS), and Profit. (Hint: $58,409 is the profit for report 1096-Amarilla)
10. In the Aged column, calculate the number of days the report has aged based on today's date using a function.
Ensure this cell is formatted in General number formatting.
11. Using an IF function, display the appropriate manager that will certify the report. Reports that have aged 500 days
or more or generated $75,000 or more in profit will be certified by Inventory Accounting, while all other
remaining reports will be reviewed by the Transportation Lead. (Hint: Nest an OR function inside the IF function)
12. Sort the Excel table by Report Name, smallest to largest.
Page 1 of 3
BA-216A Mid-Term Assessment
13. Add the Totals Row to the Excel table. Display the following:
a. Total Number of Reports
b. Total Number of Units Sold
c. Total Amount of Gross Sales
d. Total Cost of Goods Sold (COGS)
e. Total Profit
f.
Average Number of Days the Reports Aged
14. Format all data in a professional business manner.
Part II
15. Navigate to the Dashboard worksheet. Remove the gridlines.
16. Format columns A, D and H to minimize the column width to 2.29 (21 pixels).
17. In cell C11, utilize Data Validation to create a drop-down menu of the Report Names in of the Report_Backlog
Table. Use the cell range F3:F527.
18. Add an Input Message titled “Report Name” with a message that reads: "Utilize the drop-down option here to
conduct a quick analysis." Once completed, select report 1346 – Paseo for Report Name.
19. In cell C12, utilize the Index and XMatch functions to index the appropriate data. Fill the function down through
cell C20. Format all numbers appropriately. (Hint: The Units Sold should be 4,244 and the profit should be
$76,392)
20. In cell C23, utilizing Data Validation and the Reference Tables, create a drop-down menu list for the Manager
Analysis. Once completed, select Transportation Lead for the Manager Analysis
21. In cell C24, utilize an SUMIF Function to display the Total Units Sold based on the focus area drop down list
created.
22. In cell C25, utilize an SUMIF Function to display the Total Gross Sales based on the focus area drop down list
created.
23. In cell C26, utilize an AVERAGEIF Function to display the Average Profit based on the focus area drop down
list created.
24. In cell C27, utilize a COUNTIF Function to display the Number of Reports based on the focus area drop down
list created.
Part III
25. In cell E10 key in the title, "Country Analysis Dashboard.” Change the text to Arial font, size 14, and bold.
Merge and Center the cell range E10:G10.
26. Create a PivotTable from the Report_Backlog table. Place the PivotTable in Cell E11 of the Dashboard sheet.
Name the PivotTable, Gross Sales & Revenue Per Units Sold. Change the PivotTable option to not “Autofit
column widths on update.” (PivotTable Analyze tab → PivotTable group → Options → Layout & Format tab →
deselect the “Autofit column widths on update” option)
a. Add the Country field to the Rows area.
b. Insert a Calculated Field named “Gross Sales ($)” that calculates Total Gross Sales divided by the
Total Units Sold. (Hint: Canada should display $27.79)
c. Insert a second Calculated Field named “Revenue ($)” that calculates Profit divided by the Total Units
Sold. (Hint: Mexico should display $17.01)
Page 2 of 3
BA-216A Mid-Term Assessment
d. Remove Totals.
e. Apply a PivotTable style of your choice.
f.
Rename the columns appropriately.
27. Add the Red-Yellow-Green 3 Flags Conditional Formatting icon set to the Gross Sales column, and the Red
Yellow-Green 3 Signs icon set to the Revenue column.
28. Insert Slicer for Segments. Change the columns to 3. Position it in Cell Range E18:G22.
29. Utilizing the PivotTable, insert a Timeline Slicer that covers cells E23:G27. Remove the Selection Label, Scroll
Bar, and Time Level from the Slicer.
30. Add styles to the slicers that coordinate with the other items on the dashboard.
31. From the PivotTable, create a Combo Pivot Chart (2-D Column and Line) placing it in cells B28:G51. Format
the chart appropriately and professionally.
32. Hide all columns after Column H & all rows after Row 52.
Key Requirements:
you are a financial analyst at a large corporation responsible for managing and analyzing the company's product reports and financial data. Your goal is to set up a dashboard to display the needed information.
Ideal Skills:
- Proficiency in Excel
- Experience in creating educational materials
- Competence in trend analysis
- Ability to work with financial data
Heres the Project: You are a financial analyst at a large corporation responsible for managing and analyzing the company's product
reports and financial data. The company deals with various products in different business segments and countries. Your
task is to create a comprehensive dashboard that provides insights into product performance, profit analysis, and financial
projections.
The final dashboard should not only be well-organized and visually appealing but should also provide valuable insights
into product performance, profit distribution, and future financial projections. Once completed, save the Excel workbook as
"Report Backlog Last Name First Name" and submit the workbook to the file submission folder.
Complete the following steps:
Part I
1. Enter your name in cell range C3:D3 on the Dashboard tab.
2. Utilizing the Get Data Function, import the Report Backlog Text File into a new worksheet.
3. Ensure that the worksheet is named Report Backlog and the imported data table is renamed Report_Backlog.
Move the Report Backlog sheet to the right of the Dashboard sheet.
4. Insert a blank column in front of column A and insert a blank row above row 1.
5. Apply a table style of your choice.
6. Remove duplicate records in the Report No. column/field. (Hint: Five duplicate records should be removed and
you should now have records down through row 527)
7. In the Report Name column, combine the Report No. and Product fields to create the individual report name.
Report names should include “Report Number – Product”. If necessary, expand the column to ensure the
combined data is easily readable.
8. Using the VLOOKUP function, find the Cost of Good and Original Price values using the table on the
Reference Tables worksheet.
9. In columns J - L, utilize simple mathematical equations to calculate the Gross Sales, Cost of Goods Sold
(COGS), and Profit. (Hint: $58,409 is the profit for report 1096-Amarilla)
10. In the Aged column, calculate the number of days the report has aged based on today's date using a function.
Ensure this cell is formatted in General number formatting.
11. Using an IF function, display the appropriate manager that will certify the report. Reports that have aged 500 days
or more or generated $75,000 or more in profit will be certified by Inventory Accounting, while all other
remaining reports will be reviewed by the Transportation Lead. (Hint: Nest an OR function inside the IF function)
12. Sort the Excel table by Report Name, smallest to largest.
Page 1 of 3
BA-216A Mid-Term Assessment
13. Add the Totals Row to the Excel table. Display the following:
a. Total Number of Reports
b. Total Number of Units Sold
c. Total Amount of Gross Sales
d. Total Cost of Goods Sold (COGS)
e. Total Profit
f.
Average Number of Days the Reports Aged
14. Format all data in a professional business manner.
Part II
15. Navigate to the Dashboard worksheet. Remove the gridlines.
16. Format columns A, D and H to minimize the column width to 2.29 (21 pixels).
17. In cell C11, utilize Data Validation to create a drop-down menu of the Report Names in of the Report_Backlog
Table. Use the cell range F3:F527.
18. Add an Input Message titled “Report Name” with a message that reads: "Utilize the drop-down option here to
conduct a quick analysis." Once completed, select report 1346 – Paseo for Report Name.
19. In cell C12, utilize the Index and XMatch functions to index the appropriate data. Fill the function down through
cell C20. Format all numbers appropriately. (Hint: The Units Sold should be 4,244 and the profit should be
$76,392)
20. In cell C23, utilizing Data Validation and the Reference Tables, create a drop-down menu list for the Manager
Analysis. Once completed, select Transportation Lead for the Manager Analysis
21. In cell C24, utilize an SUMIF Function to display the Total Units Sold based on the focus area drop down list
created.
22. In cell C25, utilize an SUMIF Function to display the Total Gross Sales based on the focus area drop down list
created.
23. In cell C26, utilize an AVERAGEIF Function to display the Average Profit based on the focus area drop down
list created.
24. In cell C27, utilize a COUNTIF Function to display the Number of Reports based on the focus area drop down
list created.
Part III
25. In cell E10 key in the title, "Country Analysis Dashboard.” Change the text to Arial font, size 14, and bold.
Merge and Center the cell range E10:G10.
26. Create a PivotTable from the Report_Backlog table. Place the PivotTable in Cell E11 of the Dashboard sheet.
Name the PivotTable, Gross Sales & Revenue Per Units Sold. Change the PivotTable option to not “Autofit
column widths on update.” (PivotTable Analyze tab → PivotTable group → Options → Layout & Format tab →
deselect the “Autofit column widths on update” option)
a. Add the Country field to the Rows area.
b. Insert a Calculated Field named “Gross Sales ($)” that calculates Total Gross Sales divided by the
Total Units Sold. (Hint: Canada should display $27.79)
c. Insert a second Calculated Field named “Revenue ($)” that calculates Profit divided by the Total Units
Sold. (Hint: Mexico should display $17.01)
Page 2 of 3
BA-216A Mid-Term Assessment
d. Remove Totals.
e. Apply a PivotTable style of your choice.
f.
Rename the columns appropriately.
27. Add the Red-Yellow-Green 3 Flags Conditional Formatting icon set to the Gross Sales column, and the Red
Yellow-Green 3 Signs icon set to the Revenue column.
28. Insert Slicer for Segments. Change the columns to 3. Position it in Cell Range E18:G22.
29. Utilizing the PivotTable, insert a Timeline Slicer that covers cells E23:G27. Remove the Selection Label, Scroll
Bar, and Time Level from the Slicer.
30. Add styles to the slicers that coordinate with the other items on the dashboard.
31. From the PivotTable, create a Combo Pivot Chart (2-D Column and Line) placing it in cells B28:G51. Format
the chart appropriately and professionally.
32. Hide all columns after Column H & all rows after Row 52.