Assignment 1: Personal Budget Tracker
Objective: Build a simple monthly budget spreadsheet.
Steps:
1. Open Google Sheets and create a new spreadsheet. Name it “Monthly Budget.”
2. In Row 1, create headers: Category, Budgeted Amount, Actual Amount,
Difference.
3. In Column A, list 6 expense categories (e.g., Rent, Food, Transportation, Utilities,
Entertainment, Savings).
4. Enter sample dollar amounts in the Budgeted Amount and Actual Amount
columns.
5. In the Difference column, write a formula to subtract Actual from Budgeted (e.g.,
=B2-C2 ). Drag it down for all rows.
6. Add a total row at the bottom using =SUM() for each column.
7. Apply currency formatting to all number cells (Format > Number > Currency).
8. Use conditional formatting to highlight any row where you went over budget in
red.
Assignment 2: Class Grade Book
Objective: Track student scores and calculate averages automatically.
Steps:
1. Create a new spreadsheet and name it “Grade Book.”
2. In Row 1, add headers: Student Name, Quiz 1, Quiz 2, Quiz 3, Test 1, Average,
Letter Grade.
3. Enter at least 8 fictional student names in Column A.
4. Enter scores (0–100) for each quiz and test.
5. In the Average column, use =AVERAGE(B2:E2) and drag it down for all
students.
6. In the Letter Grade column, use an =IF() formula to assign grades:
=IF(F2>=90,"A",IF(F2>=80,"B",IF(F2>=70,"C",IF(F2>=60,"D","F"))))
7. Freeze Row 1 (View > Freeze > 1 row) so headers stay visible while scrolling.
8. Sort the sheet by Average from highest to lowest.
Assignment 3: Product Inventory Tracker
Objective: Manage a simple product inventory with stock alerts.
Steps:
1. Create a new spreadsheet named “Inventory Tracker.”
2. Add headers in Row 1: Product Name, Category, Unit Price, Quantity in Stock,
Total Value, Stock Status.
3. Enter 10 products with their category, price, and quantity.
4. In the Total Value column, multiply Unit Price by Quantity (e.g., =C2*D2 ).
5. In the Stock Status column, use an IF formula to flag low stock:
=IF(D2<5,"Low Stock","OK")
6. Apply conditional formatting to highlight “Low Stock” cells in red and “OK” in
green.
7. Add a summary section below the data using =SUM() for total inventory value
and =COUNTIF() to count how many products are low on stock.
8. Insert a bar chart showing Product Name vs. Quantity in Stock.
Assignment 4: Survery Data Analyzer
Objective: Import and analyze responses from a simple survey.
Steps:
1. Create a new spreadsheet named “Survey Results.”
2. In Row 1, add headers: Respondent ID, Age, Gender, Satisfaction Score (1–5),
Would Recommend (Yes/No), Comments.
3. Enter at least 15 rows of fictional survey responses.
4. In a separate section to the right, create a summary table with labels: Total
Responses, Average Satisfaction, % Who Would Recommend.
5. Use =COUNTA() to count total responses, =AVERAGE() for satisfaction, and
=COUNTIF()/COUNTA() to calculate the recommendation percentage. Format as a
percentage.
6. Use =COUNTIF() to count how many respondents gave each score (1 through
5) and list the results in a small table.
7. Create a pie chart from the score counts showing score distribution.
8. Apply alternating row colors using Format > Alternating Colors for readability.
Assignment 5: Sales Dashboard
Assignment 5: Sales Dashboard
Objective: Build a multi-tab sales report with charts and formulas.
Steps:
1. Create a new spreadsheet named “Sales Dashboard.” Rename the first tab “Raw
Data.”
2. Add headers: Date, Salesperson, Region, Product, Units Sold, Unit Price, Total
Sale. Enter at least 20 rows of data across three fictional regions and three salespeople.
3. In the Total Sale column, calculate =E2*F2 for each row.
4. Create a second tab named “Summary.” Use =SUMIF() to calculate total sales
by each salesperson and by each region.
5. Use =MAXIFS() and =MINIFS() to find the best and worst single sale per
region.
6. Back on the Raw Data tab, create a filter (Data > Create a filter) to allow sorting
by salesperson or region.
7. On the Summary tab, insert a column chart comparing total sales by salesperson
and a separate line chart showing sales by date.
8. Add a text box at the top of the Summary tab noting the overall total using
=SUM() and the top-performing salesperson using =INDEX(MATCH()) .