Nook’s Notes: Spreadsheets Capstone

I like reading, but reading non-fiction can be a struggle for me to retain. So, I will be treating my reading as if it were classwork by taking and sharing notes. Want to join me?

Image of a computer that says “Capstone Project” and on the desk is a trophy and a whistle.

All the instruction is over and done with, so now is the time to apply everything from the past four weeks into this fifth and final Capstone week. The motivation to take this micro-credential course stemmed from numerous previous attempts to learn how to use spreadsheets beyond the basics, which had never stuck in my brain. Here’s to this attempt, which with its assignments, quizzes, and capstone, I expect to build some real muscle memory!

Chocolate Data Analysis and Visualization

Competencies

• Formulas
• Functions
• Pivot tables

Elements

• Download the Dataset
• Data Preparation
• Data Analysis
• Advanced Formulas and Functions
• Data Visualization
• Formatting and Presentation

Instructions

1. Import the data and save your file as “CapstoneProject-[Your last name]”
2. Ensure all data is correctly formatted using the “Format Cells” icon to set the appropriate data types for each column (e.g., the “Date of Production” and “Expiry Date” columns have the date format & “Price (USD)” and “Weight (g)” columns have the numeric format)
3. Create a new column called “Total Revenue” that calculates for each product the “Price (USD) by “Weight (g)”
4. Create a new column called “Days to Expiry” that calculates for each product the “Expiry Date” minus “Date of Production”
5. Create a new column called “Is Expired” that uses the IF function to indicate “Yes” if the “Expiry Date” is before today’s date, else “No”
6. Create a new sheet called “Pivot Table Analysis”
7. Insert a pivot table here that analyzes the average price and weight by chocolate type
8. Insert a pivot table here that shows the total number of chocolate products by each location
9. Insert a pivot table here that summarizes the total revenue by each chocolate type
10. Create a new sheet called “Visualizations”
11. Insert a bar chart here that shows the total number of products by chocolate type
12. Insert a pie chart here that shows the distribution of chocolate types by cocoa percentage ranges
13. Insert a line chart here that visualizes the trend of “Total Revenue” by “Date of Production”
14. Insert a scatter plot that shows the relationship between “Price (USD)” and “Cocoa Percentage”
15. Format all sheets so that data is clear, readable, and presented using cell borders, shading, and font styles
16. Differentiate headings and data using bolding and consistent font styles
17. Highlight key insights with conditional formatting (e.g., expired products)