Question
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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started