Website Home Page |

Excel Assignments List

| Froyo Technologies Public Resources

| Investment | Initial Value | Current Value | Return (%) |

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

| Stock A    | 1000          | 1200          |            |

| Stock B    | 1500          | 1400          |            |

| Stock C    | 800           | 900           |            |

 
Task :

1. Calculate the return percentage for each investment using a formula ((Current Value - Initial Value) / Initial Value * 100).
2. Calculate the average return across all investments using the AVERAGE function.
3. Use IF function to categorize each investment as "Profitable" if Return > 0, otherwise "Not Profitable".
4. Create a column chart to compare the return percentage of each investment.
5. Format the chart title, axes labels, and legend to enhance readability.

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

Steps to Perform:

1.      Create a Table for Sales Data:

• Enter the sample data into Excel starting from cell A1.
• Select the data range.
• Go to Insert tab > Table to convert the range into a table.

2.      Calculate Total Sales Revenue for Each Order:

• In cell F2 (next to "Order Date" column header), enter the formula =D2*E2 to calculate the total sales revenue for Order ID 1001.
• Copy the formula down to calculate for Order ID 1002 and Order ID 1003 (F3 and F4).

3.      Use INDEX and MATCH Functions to Find Customer Name for Order ID 1003:

• In a new cell (for example, G2), enter the formula =INDEX(B2:B4, MATCH(1003, A2:A4, 0)).
• This formula uses INDEX and MATCH functions together to find the Customer Name associated with Order ID 1003.

4.      Create a PivotChart for Total Sales by Product Category:

• Select the range of data including headers (A1).
• Go to Insert tab > Charts group > Choose PivotChart > Select a suitable style.
• In the PivotChart Fields pane, drag "Product" to Axis (Categories) and "Total Revenue" to Values.

5.      Use Slicers in the PivotChart to Filter Data:

• With the PivotChart selected, go to Insert tab > Filter group > Slicer.
• Choose fields like "Month" and "Product" to add slicers that allow filtering of data in the PivotChart dynamically.