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 Cost of sales will be reduced by 5 Other costs will reduce by 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 Reduce the collection period by 15 days Increase inventory turnover by 2 times 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

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 29, 2024

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:

Cost of sales will be reduced by 5%
Other costs will reduce by 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.
Reduce the collection period by 15 days.
Increase inventory turnover by 2 times.
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.