Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Option 1 Pewter Discs 1. a) Open Part A Financial Statement Analysis spreadsheet templates. Input the information contained in the Pewter Discs 2017 to 2020

Option 1

Pewter Discs

1. a) Open Part A Financial Statement Analysis spreadsheet templates. Input the information contained in the Pewter Discs 2017 to 2020 financial statements that are attached to this case into Templates 1, 2, and 3.

Note: Because the spreadsheet templates have a maximum of three years, you will have to input the financial statements for years 2018, 2019 and 2020 using one set of templates and years 2021, 2022 and 2023 using another set.

b) Using the financial planning assumptions in this case, produce the statement of income, statement of changes in equity, and statement of financial position for Pewter Discs in the year 2021.

c) Prepare a written analysis of the companys financial statements for the years 2018, 2019 and 2020 (actual), 2021(year-end estimates), and 2022 (forecast). This information can be drawn from the following templates:

Template 4: Statement of Sources and Uses of Funds

Template 5: Statement of Cash Flows

Template 6: Vertical Analysis of the Statement of Income

Template 7: Horizontal Analysis of the Statement of Income

Template 8: Vertical Analysis of the Statement of Financial Position

Template 9: Horizontal Analysis of the Statement of Financial Position

Template 10: Financial Ratios

Template 12: Cash Flow and EBITDA

2. Open Part B - Decision-Making Tools spreadsheet templates. Input the revenue and cost information for 2019-2021 from Pewter Discs financial statements into Template 2 (Break-Even Analysis using the PV ratio). Based on this information and break-even planning assumptions, what are the companys break-even points for the years 2019, 2020 and 2021? How much sales revenue should Pewter Discs have to sell in 2021 in order to reach a $300,000, $350,000, and $400,000 before-tax profit objective? Include a written response to this in your part c analysis.

3. Open Part B - Decision-Making Tools spreadsheet Templates. Based on the monthly cost breakdown and by using Template 7 (Monthly Cash Budget), produce a cash budget for the year 2021.

Option 2

Other Company

Assumptions:

Financial projections include the following:

  1. Cost of sales will be reduced by 5%
  2. Other costs will reduce by 3%
  3. You may use the MD&A points if there is something there you wish to include in your project. Do not spend a significant amount of time on the MD&A. This is not part of the assignment. The purpose is to prepare the projection.
  4. Reduce the collection period by 15 days.
  5. Increase inventory turnover by 2 times.
  6. In terms of Debt consider how the company is set up to achieve growth through financing? Assume there is a planned investment in new assets of $2,000,000. Use the benchmark of 50%.

1. a) Open Part A Financial Statement Analysis spreadsheet templates. Input the information contained in the financial statements for 3 years not including latest year in Templates 1, 2, and 3.

Note: Because the spreadsheet templates have a maximum of three years, you will have to input the financial statements for the last three years using one set of templates and use another set for the current year and the projection year.

b) Using the financial planning assumptions in this case, produce the statement of income, statement of changes in equity, and statement of financial position for the company in the projected year.

c) Prepare a written analysis of the companys financial statements for the actual years and the projected or forecasted years. This information can be drawn from the following templates:

Template 4: Statement of Sources and Uses of Funds

Template 5: Statement of Cash Flows

Template 6: Vertical Analysis of the Statement of Income

Template 7: Horizontal Analysis of the Statement of Income

Template 8: Vertical Analysis of the Statement of Financial Position

Template 9: Horizontal Analysis of the Statement of Financial Position

Template 10: Financial Ratios

Template 12: Cash Flow and EBITDA

2. Open Part B - Decision-Making Tools spreadsheet templates. For this part you will use the information from Pewter Discs. Input the revenue and cost information for 2019-2021 from Pewter Discs financial statements into Template 2 (Break-Even Analysis using the PV ratio). Based on this information and break-even planning assumptions, what are the companys break-even points for the years 2019, 2020 and 2021? How much sales revenue should Pewter Discs have to sell in 2021 in order to reach a $300,000, $350,000, and $400,000 before-tax profit objective? Include a written response to this in your part c analysis.

3. Open Part B - Decision-Making Tools spreadsheet Templates. For this part you will use the information from Pewter Discs. Based on the monthly cost breakdown and by using Template 7 (Monthly Cash Budget), produce a cash budget for the year 2021.

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Gulf Capital And Islamic Finance The Rise Of The New Global Players

Authors: Aamir A. Rehman

1st Edition

0071621989

More Books

Students also viewed these Finance questions

Question

What method(s) will I use to communicate and celebrate?

Answered: 1 week ago