Website Home Page |

Excel Assignments List

| Froyo Technologies Public Resources

Sample Data

| Product | Unit Price | Qty(Base Case) | Qty(Scenario 1) | Qty(Scenario 2) |

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

| A       | 100        | 500            | 600             | 450                   |

| B       | 150        | 700            | 800             | 600                   |

| C       | 80         | 400            | 450             | 350                   |

 

Task :

1. Calculate the total revenue for each product in the base case using formulas.
2. Use Goal Seek to determine the required Unit Price for Product C to achieve a specific revenue target.
3. Create a data table to analyze the impact of Quantity changes on total revenue.
4. Use INDEX and MATCH functions to find the Unit Price for Scenario 1 and Scenario 2.
5. Build a scenario summary report to compare total revenue across different scenarios.

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

Step to solve:

1. Calculate Total Revenue for Each Product in the Base Case

To calculate the total revenue for each product in the base case, you can use the formula:

Total Revenue=Unit Price×Quantity\text{Total Revenue} = \text{Unit Price} \times \text{Quantity}Total Revenue=Unit Price×Quantity

Here's how you would apply this formula for each product:

·         Product A:

• Unit Price: $100
• Qty (Base Case): 500
• Total Revenue = 100 * 500 = $50,000

·         Product B:

• Unit Price: $150
• Qty (Base Case): 700
• Total Revenue = 150 * 700 = $105,000

·         Product C:

• Unit Price: $80
• Qty (Base Case): 400
• Total Revenue = 80 * 400 = $32,000

2. Use Goal Seek to Determine Required Unit Price for Product C

To determine the required Unit Price for Product C to achieve a specific revenue target using Goal Seek:

• Assume the revenue target is $40,000.
• Set up your worksheet where you have Unit Price, Quantity, and Total Revenue for Product C.
• Go to Data tab > What-If Analysis > Goal Seek.
• Set "Set Cell" to the cell containing the Total Revenue of Product C.
• Set "To value" to $40,000.
• By changing "By changing cell" to the Unit Price of Product C.
• Click OK.

Excel will perform the Goal Seek analysis and adjust the Unit Price of Product C to achieve the specified revenue target.

3. Create a Data Table to Analyze Impact of Quantity Changes

To analyze the impact of Quantity changes on total revenue using a Data Table:

• Set up a table with Quantity values (Base Case, Scenario 1, Scenario 2) as rows.
• Place the Unit Prices of each product as column headers.
• In a cell next to the Quantity values, use a formula to calculate Total Revenue for each scenario using the formula from step 1.
• Select the range of cells containing these values (excluding headers).
• Go to Data tab > What-If Analysis > Data Table.
• For "Row input cell", select the cell containing the Quantity values.
• Click OK.

Excel will create a data table showing Total Revenue for each product at different Quantity scenarios and Unit Prices.

4. Use INDEX and MATCH Functions to Find Unit Price for Scenario 1 and Scenario 2

To find the Unit Price for each product in Scenario 1 and Scenario 2 using INDEX and MATCH:

·         Assuming Scenario 1 and Scenario 2 quantities are listed in columns D and E, starting from row 2 (assuming row 1 is headers):

o    Product A:

=INDEX(B2:B4, MATCH("A", A2:A4, 0))

o    Product B:

=INDEX(B2:B4, MATCH("B", A2:A4, 0))

o    Product C:

=INDEX(B2:B4, MATCH("C", A2:A4, 0))

·         Replace A2

• with the actual range where Product names are listed.

5. Build a Scenario Summary Report

To build a scenario summary report to compare total revenue across different scenarios:

• Create a summary table with columns for Base Case, Scenario 1, and Scenario 2.
• In each column, calculate the total revenue for each product using the formulas from step 1 or referencing the Data Table results (step 3).
• Optionally, use charts or conditional formatting to visually represent the revenue differences