please show all work, and formulas.
Question 2 Congratulationsl After completing your Advanced Spreadsheet Analysis course, you have been hired as a Portfolio Manager at Goldman Sachs Your first task is to analyze the profitabitity of the cormpanies within your portfolio Using the data below - Calculate the net profit margin for each year - Calculate the compound avorage annual growth rates (CABR) for revenue and net income using the Geomean function. What is the Geomean function? What is Excol's definition of it? is net income growing more stowly or faster than total revenue? is this a positive for your investment in the cornpany? - Using the Average function, calcutate the annual growth rate of total revenue - Create a column chart of total revenue and net income. Change the chart so that the x-axis label contains the year numbers and format the axis so that 2020 is on the far-fight side of the axis. For a little razzle dazzle, also choose a color for both net income and total revenue Guess what ... It's time for a real-world scenariolli In our content folder for this exam, you will see a link attached that will direct you to Apple's Consolidated Statements (Unaudited). Using their financials, create a table in Excel like this: - Calculate the ratio for each year. What does your calculation tell you about each ratio? What does the change mean? Did it improve or get worse? What constitutes improvement vs. what signifies worsening? - Recreate the Consolidated Balance Sheet in a SEPARATE worksheet It can be labeled Question 3 Part2 Here, you will use Excel's outlining feature to collapse the categones by their totals. For example, Current Assets should be collapsed to where you can expand to see cash and cash equivalents, marketable securities, and so on You will do this for all three sections of the balance sheet Question 2 Congratulationsl After completing your Advanced Spreadsheet Analysis course, you have been hired as a Portfolio Manager at Goldman Sachs Your first task is to analyze the profitabitity of the cormpanies within your portfolio Using the data below - Calculate the net profit margin for each year - Calculate the compound avorage annual growth rates (CABR) for revenue and net income using the Geomean function. What is the Geomean function? What is Excol's definition of it? is net income growing more stowly or faster than total revenue? is this a positive for your investment in the cornpany? - Using the Average function, calcutate the annual growth rate of total revenue - Create a column chart of total revenue and net income. Change the chart so that the x-axis label contains the year numbers and format the axis so that 2020 is on the far-fight side of the axis. For a little razzle dazzle, also choose a color for both net income and total revenue Guess what ... It's time for a real-world scenariolli In our content folder for this exam, you will see a link attached that will direct you to Apple's Consolidated Statements (Unaudited). Using their financials, create a table in Excel like this: - Calculate the ratio for each year. What does your calculation tell you about each ratio? What does the change mean? Did it improve or get worse? What constitutes improvement vs. what signifies worsening? - Recreate the Consolidated Balance Sheet in a SEPARATE worksheet It can be labeled Question 3 Part2 Here, you will use Excel's outlining feature to collapse the categones by their totals. For example, Current Assets should be collapsed to where you can expand to see cash and cash equivalents, marketable securities, and so on You will do this for all three sections of the balance sheet
