Excel Project II Spring 2024

.pdf

School

Central Connecticut State University *

*We aren’t endorsed by this school

Course

MISC

Subject

Finance

Date

Feb 20, 2024

Type

pdf

Pages

2

Uploaded by jhammer18

Excel Project II Learning Objectives 1. Create a pivot table in Excel 2. Add new calculated field to pivot table 3. Create a pivot chart with appropriate labels 4. Apply appropriate numeric format on pivot table and chart 5. Analyze and interpret outcome of pivot tables and calculated fields Data Set Background In the previous Excel project, you analyzed the merits of investing of two companies operating in the discount retail industry. This time, you will This time, you will investigate two aspects of the relative desirability of investing in stock of those two companies, Circuit Discount TV and General Brands, Inc. To do so you will calculate the return on stockholders’ equity and performance ratios to allow you to compare and contrast the two companies. The data set contains the elements of the financial statements of both companies for the five years 2019 through 2023. Requirements: 1. Create one pivot table to summarize the income from continuing operations, earnings per share (EPS), and dividends paid for the five-year period 2019 through 2023 on a separate worksheet. The data should be summarized by each company for all relevant years. Add a calculated field to calculate the dividend payout ratio for each company (carried to two decimal places) over the five-year period 2019-2023. Label the worksheet ‘DividendPayout’. 2. On the ‘DividendPayout’ worksheet create a pivot cluster column chart to with columns for EPS and dividends with a trend line for the dividend payout ratio for each company over the five-year period. Each company’s data for each year should be displayed side by side. Change the title of the chart to “Dividends Payout Ratio Trend”. 3. Create one pivot table to summarize the basic earnings per share and market per share for the five-year period 2019 through 2023 on a separate worksheet. The data should be summarized by each company for all relevant years. Add a calculated field to calculate the price earnings ratio for each company (carried to two decimals places) over the five- year period 2019-2023. Label the worksheet ‘PriceEarningsRatio’. 4. On the ‘PriceEarningsRatio’ worksheet display the trend of the price earnings ratio over the five-year period 2019-2023 using a bar chart with line graph. Each company’s data for each year should be displayed in a separate bar chart. Add data labels to the top of each chart display each price-earnings ratio. The X axis title should be ‘Years’ and the Y axis title should be ‘Price Earnings Ratio’. The Change the title of the chart to “[company name] P/E Ratio Trend”.
5. Based on your analysis, answer the following questions on a separate worksheet labeled ‘Responses’: a. Which of the two companies, Circuit Discount TV or General Brands, Inc. pays investors the greater percentage of its earnings during the period 2019-2023? b. Indicate whether the dividend payout ratio for each company is (1) increasing significantly, (2) decreasing significantly, or (3) remaining relatively the same during the period 2019-2023? What seems to be the reason for the change in the dividend payout ratio for each company? c. Is the price-earnings ratio for each company (1) increasing significantly, (2) decreasing significantly, or (3) remaining relatively the same during the period 2019-2023? d. Upon review of your calculations and analysis, which company, Circuit Discount TV or General Brands, Inc. would you be more interested to invest in. The expectation is that your response is supported by your analysis above. 6. The worksheets should be formatted with appropriate numeric values using accounting format with zero decimals unless indicated otherwise. Excel Project II Due: 4/7/2024 via Blackboard
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help