Certainly! Here’s how you can complete each step using **Excel** (or Google Sheets, works similarly). Let's assume you have the following **sample data** for Table 1 and Table 2: --- ### **Sample Data** #### **Table 1: Expenses** | Expense Code | Item | Projected Expense | Actual Expense | |--------------|--------------|------------------|----------------| | 101 | Office Rent | 1200 | 1300 | | 102 | Utilities | 400 | 390 | | 103 | IT Support | 300 | 350 | | 104 | Stationery | 100 | 80 | | 105 | Internet | 200 | 220 | #### **Table 2: Expense Category** | Expense Code | Expense Category | |--------------|-----------------| | 101 | Fixed | | 102 | Variable | | 103 | Variable | | 104 | Variable | | 105 | Fixed | --- ### **Step-by-Step Instructions** --- #### **1. Insert the given data in a Table (10%)** - Select your data in Table 1 (A1:D6). - Go to **Insert > Table** (or use Ctrl+T). - Make sure "My table has headers" is checked. - Click OK. --- #### **2. Insert a formula to tell which expenses have been over budget (20%)** - Insert a new column **E** called **Over Budget?** - In **E2**, enter the formula: ```excel =IF([@[Actual Expense]] > [@[Projected Expense]], TRUE, FALSE) ``` This returns TRUE if Actual > Projected, FALSE otherwise. - Drag the formula down for all rows. --- #### **3. Insert a column to type "Overbudget" automatically if the expense is overbudget and "No" if it is not overbudget (10%)** - Insert a new column **F** called **Status** - In **F2**, enter: ```excel =IF([@[Actual Expense]] > [@[Projected Expense]], "Overbudget", "No") ``` - Drag down for all rows. --- #### **4. Insert a row and Sum the accumulative Projected Expense and Actual expense (10%)** - At the bottom of your table, add a new row titled **Total**. - In the **Projected Expense** and **Actual Expense** columns, insert SUM formulas: - For Projected: `=SUM([Projected Expense])` - For Actual: `=SUM([Actual Expense])` --- #### **5. Freeze the first column (10%)** - Select cell **B2**. - Go to **View > Freeze Panes > Freeze First Column**. --- #### **6. Use conditional formatting to show overbudget cells with Yellow color (10%)** - Select the **Actual Expense** column. - Go to **Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format**. - Use the formula: ```excel =D2>C2 ``` (Assuming Actual is in D and Projected in C) - Set the fill color to **Yellow**. - Click OK. --- #### **7. Find relevant Expense Category based on the Expense Code using Table 2 (10%)** - Add a new column **G** called **Expense Category**. - In **G2**, use **VLOOKUP**: ```excel =VLOOKUP([@[Expense Code]], $I$2:$J$6, 2, FALSE) ``` (Assuming Table 2 is in columns I & J) - Drag down. --- #### **8. Insert a Bar Chart to compare Projected Expense and Actual expense in each item (10%)** - Select your data for **Item**, **Projected Expense**, and **Actual Expense**. - Go to **Insert > Bar Chart > Clustered Bar**. - The chart will appear showing both values per item. --- #### **9. Add Data Label and Legend to the Chart (10%)** - Click on the chart. - Click the **+** sign next to the chart, check **Data Labels** and **Legend**. --- --- ## **Summary of Steps with Excel Formulas** | Expense Code | Item | Projected | Actual | Over Budget? | Status | Expense Category | |--------------|-------------|-----------|--------|--------------|-------------|------------------| | 101 | Office Rent | 1200 | 1300 | TRUE | Overbudget | Fixed | | 102 | Utilities | 400 | 390 | FALSE | No | Variable | | 103 | IT Support | 300 | 350 | TRUE | Overbudget | Variable | | 104 | Stationery | 100 | 80 | FALSE | No | Variable | | 105 | Internet | 200 | 220 | TRUE | Overbudget | Fixed | | **Total** | | =SUM(C2:C6) | =SUM(D2:D6) | | | | --- **Let me know if you want a ready-to-use Excel template!**