Assignment -13 | Budget Analysis and Performance

Excepted timing : 10 minutes

| Category      | Budgeted Amount | Actual Amount | Variance |
|---------------|-----------------|---------------|----------|
| Marketing     | 5000            | 4800          |          |
| Operations    | 7000            | 7200          |          |
| IT            | 6000            | 5800          |          |
| Management    | 5000            | 4800          |          |
| Others        | 7000            | 7200          |          |

 
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:

Step 1: Calculate the Variance

  1. Enter Data: Enter your data into Excel with the following columns:
    • A1: Category
    • B1: Budgeted Amount
    • C1: Actual Amount
    • D1: Variance

Enter your data starting from A2 to C6 (based on your provided example).

  1. Formulas:
    • In cell D2, enter the formula =C2 - B2 to calculate the variance for Marketing.
    • Copy the formula from D2 down to cells D3

to calculate variances for Operations, IT, Management, and Others.

Step 2: Calculate Total Budgeted and Actual Amounts

  1. Formulas:
    • In cell B7 (Total Budgeted), enter =SUM(B2:B6) to sum up all budgeted amounts.
    • In cell C7 (Total Actual), enter =SUM(C2:C6) to sum up all actual amounts.

Step 3: Use IF Function to Categorize Budget Performance

  1. Formulas:
    • In cell E1, enter Budget Performance.
    • In cell E2, enter the formula =IF(D2 >= 0, "Within Budget", "Over Budget").
    • Copy the formula from E2 down to cells E3

to categorize all categories based on their variance.

Step 4: Create a Clustered Column Chart

  1. Select Data:
    • Select cells A1 (including headers).
  2. Insert Chart:
    • Go to the Insert tab > Charts > Column > Clustered Column.

Step 5: Insert a Trendline in the Chart

  1. Add Trendline:
    • Right-click on any data series in the chart and select Add Trendline.
    • Choose the type of trendline that best fits your data (linear, exponential, etc.).
    • Optionally, display the equation and R-squared value on the chart if needed.