Website Home Page |

Excel Assignments List

| Froyo Technologies Public Resources

| Category         | Budgeted Amount | Actual Amount | Variance |

|------------------|-----------------|---------------|----------|

| Marketing        | 5000            | 4800          |          |

| Operations       | 7000            | 7200          |          |

| IT               | 6000            | 5800          |          |

|------------------|-----------------|---------------|----------|

| Sales            | 8000            | 7800          |          |

| Human Resources  | 6000            | 6200          |          |

| Res & Develop    | 9000            | 8800          |          |

| Customer Support | 5500            | 5600          |          |

| Finance          | 7500            | 7300          |          |

| Production       | 8500            | 8200          |          |

| Logistics        | 4000            | 4200          |          |

| Purchasing       | 5000            | 4900          |          |

| Legal            | 3000            | 3100          |          |

| Administration   | 4000            | 3900          |          |

 
Task :

1. Calculate the variance (Actual Amount - Budgeted Amount) for each category using formulas.
2. Calculate the total budgeted amount and actual amount using the SUM function.
3. Use IF function to categorize each category as "Within Budget" if Variance >= 0, otherwise "Over Budget".
4. Create a clustered column chart to compare budgeted and actual amounts for each category.
5. Insert a trendline in the chart to visualize the overall budget performance.

To perform the tasks outlined in Assignment  using Excel, follow these step-by-step instructions:

Steps to Perform:

1.      Calculate Variance for Each Category:

• In cell D2 (next to "Variance" column header), enter the formula =C2-B2 to calculate the variance for Marketing.
• Copy the formula down to calculate variances for Operations (D3) and IT (D4).

2.      Calculate Total Budgeted and Actual Amounts:

• In cell B7 (below "Budgeted Amount"), enter the formula =SUM(B2:B4) to calculate the total budgeted amount.
• In cell C7 (below "Actual Amount"), enter the formula =SUM(C2:C4) to calculate the total actual amount.

3.      Use IF Function to Categorize Budget Status:

• In cell E2 (next to "Variance" column), enter the formula =IF(D2>=0, "Within Budget", "Over Budget") to categorize Marketing.
• Copy the formula down to categorize Operations (E3) and IT (E4).

4.      Create a Clustered Column Chart for Budget Comparison:

• Select the range including headers (A1).
• Go to Insert tab > Charts group > Choose Clustered Column > Select a suitable style.
• This chart will compare budgeted and actual amounts for each category visually.

5.      Insert a Trendline in the Chart:

• Click on the chart to select it.
• Go to Chart Tools > Design tab > Add Chart Element > Trendline > Choose a suitable trendline option (e.g., Linear, Exponential, etc.).
• This trendline helps visualize the overall performance trend based on the budget data.