Website Home Page |

Excel Assignments List

| Froyo Technologies Public Resources

Sample Data

 

| Company   | Market Cap (Billion) | Revenue (Million) | Profit (Million) |

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

| Company A | 110                  | 1000              | 1000             |

| Company B | 150                  | 6000              | 1200             |

| Company C | 280                  | 4000              | 1800             |

| Company A | 100                  | 1000              | 1000             |

| Company B | 150                  | 6000              | 1200             |

| Company C | 180                  | 2000              | 1800             |

| Company A | 100                  | 5000              | 1000             |

| Company B | 250                  | 3000              | 2200             |

| Company C | 280                  | 2000              | 1800             |

Task :

1. Calculate the profit margin for each company using a formula (Profit / Revenue * 100).
2. Use INDEX and MATCH functions to find the Company with the highest Market Cap.
3. Calculate the Price-to-Earnings (P/E) ratio for each company using formulas.
4. Use LARGE function to find the second largest Market Cap among the companies.
5. Create a bubble chart to visualize the relationship between Market Cap and Revenue, with bubble size representing Profit.

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

Steps to Perform:

1. Calculate Profit Margin for Each Company:
   • Use a formula to calculate the profit margin for each company, expressed as a percentage.
   • Example: =(Profit / Revenue) * 100 assuming Profit is in millions and Revenue is in millions.
2. Use INDEX and MATCH Functions to Find Company with Highest Market Cap:
   • Use the INDEX and MATCH functions to identify the company with the highest Market Cap.
   • Example: =INDEX(A2:A4, MATCH(MAX(B2:B4), B2:B4, 0)) where Company names are in column A and Market Caps are in column B.
3. Calculate Price-to-Earnings (P/E) Ratio for Each Company:
   • Calculate the P/E ratio for each company using the formula Market Cap divided by Profit.
   • Example: =B2 / (C2 / 1000) assuming Market Cap is in billions and Profit is in millions.
4. Use LARGE Function to Find Second Largest Market Cap:
   • Use the LARGE function to find the second largest Market Cap among the companies.
   • Example: =LARGE(B2:B4, 2) to find the second largest Market Cap in the range B2

5. Create a Bubble Chart to Visualize Market Cap, Revenue, and Profit:
   • Select the data range including Market Cap, Revenue, and Profit columns.
   • Go to Insert > Bubble Chart > Bubble to create the chart.
   • Customize the chart by setting Market Cap as the x-axis, Revenue as the y-axis, and Profit as the bubble size.

Detailed Steps:

• Step 1: Calculate Profit Margin
  • Enter =(D2 / C2) * 100 in a new column (assuming Profit is in column D and Revenue in column C) to calculate the profit margin percentage for each company.
• Step 2: Use INDEX and MATCH to Find Highest Market Cap
  • Enter =INDEX(A2:A4, MATCH(MAX(B2:B4), B2:B4, 0)) assuming Company names are in column A and Market Caps are in column B.
• Step 3: Calculate P/E Ratio
  • Enter =B2 / (D2 / 1000) assuming Market Cap (B2) is in billions and Profit (D2) is in millions. Adjust the divisor (1000 or 1,000,000) based on units.
• Step 4: Find Second Largest Market Cap
  • Enter =LARGE(B2:B4, 2) to find the second largest Market Cap in the range B2.
• Step 5: Create Bubble Chart
  • Select the data range including Market Cap, Revenue, and Profit columns.
  • Go to Insert > Bubble Chart > Bubble.
  • Customize the chart to appropriately represent Market Cap on the x-axis, Revenue on the y-axis, and Profit as the bubble size. Adjust titles and labels for clarity.