FIN80005: Corporate Financial Management
FIN80005: Corporate Financial Management
Assignment 2: Excel report Capital budgeting
Word limit:1500 (+/- 10%)
Weighting:40%
Due date:5pm AEST 15 September 2022 (Week 10)
After you have read this information, head over to theAssignment 2 Q&Adiscussion board to ask any questions and see what your peersare saying about this assignment.
Assignment overviewThe objective of this assignment is to use Excel spreadsheets to aid in solving a capital budgeting problem and to analyse how the market impounds new information into stock prices.. The concepts in Weeks 48 are particularly relevant to this assignment. You will solve a capital budgeting exercise using Excel, with additional discussion on findings, assumptions and recommendations.
This assignment supportsunit learning outcomes 1, 2, 3 and 4.
Assignment detailsHypothetical company background
You are an analyst working in the finance department of Candy Crash Limited. Candy Crash is a public listed company that specializes in the production of confectionery. The production relies heavily on the use of machinery. The company has 2,115,000 number of shares outstanding trading on the stock exchange.
Part 1
Candy Crash is currently in negotiation with a large supermarket chain, Costca Limited, to supply its confectionery in a private label for Costca. Under the terms, Candy Crash is expected to supply confectionery to Costca every year for the next ten years.
If Candy Crash proceeds with the supply of confectionery, the company needs to purchase machinery to cope with the increase in production. New machinery is expected to cost $2,800,000, with an additional $500,000 installation and shipping costs. The machinery is expected to have a working life of 10 years. The companys accounting policy is to depreciate using the straight line approach of 10% per year. It is expected that the new machinery can be sold for $200,000 at the end of its useful life.
If Candy Crash is to proceed with the supply of confectionery to Costca, it is expected that the yearly operating revenues would increase by $2,400,000 in year one. From year two onwards, it is expected that the increase in yearly operating revenues would grow at a rate of 5% per annum. Total variable costs associated with the increased production would be 60% of the increase in yearly operating revenues. The fixed costs associated with the increased production are expected to be $300,000 per year. However, as the private label confectionerys selling price is cheaper than Candy Crashs brand, it is expected that Candy Crashs existing operating revenues would fall by $200,000 per annum and existing operating costs would decrease by $80,000 per annum if Candy Crash proceeds with the supply of confectionery. Moreover, there would be an initial increase in net working capital of $50,000. From year one to year nine, net working capital is expected to increase by $10,000 per year. All the net working capitals can be recovered at the end of the projects life.
Given that this projects risk level is not significantly different, you believe that it is appropriate to use the existing WACC of 12.6%. The companys capital structure has remained fairly stable, with a debt-to-equity ratio of 0.8. The company has no plan to adjust its capital structure in the future. The company tax rate is 30%.
Furthermore, the CEO suggests conducting sensitivity analysis as follows because of uncertainty in relation to some of the expected cash flows:
Allow for a 30% probability that incremental revenues associated with the supply of private label confectionery would be 40% lower than expected starting from year six;Allow for a 20% probability that incremental revenues associated with the supply of private label confectionery would be 30% higher than expected starting from year six.
Part 2
Semi-strong form efficiency tests are concerned with whether security prices reflect all publicly available information. The event study methodology can be used to investigate the effects of many events such as a corporate announcement. By studying the stock price reaction before, during and after an announcement, an examination of whether the market is semi-strong form efficient can be conducted.
After performing the full analysis in Part 1, Candy Crash decides to proceed with the supply of private label confectionery to Costca (regardless of the NPV and other investment analyses). As such, the company announces details related to the expected increase in profits and net cash flows that it would achieve from the supply of private label confectionery. The table below shows the daily returns of Candy Crash (stock), the market and the risk-free asset 5 days before and after the announcement. Day 0 is the day of the announcement and there is no other price-sensitive announcement within the event window. The company has an equity beta of 1.2.
1Table 1. Daily returns for Candy Crash, market and risk-free asset during the even window.
Table 1
Day Stock return Market return Risk-free
-5 0.20% 0.28% 0.0075%
-4 0.35% 0.22% 0.0075%
-3 -0.28% 0.09% 0.0075%
-2 -0.40% -0.20% 0.0075%
-1 1.20% 0.30% 0.0075%
0 2.50% 0.30% 0.0075%
1 -1.30% -0.20% 0.0075%
2 -1.66% -0.10% 0.0075%
3 -0.50% 0.10% 0.0075%
4 0.40% 0.20% 0.0075%
5 0.26% 0.35% 0.0075%
Table 1 (2020) courtesy of Dr Mardy ChiahRequired
You are to prepare a report to present to the CEO, based on the Excel analysis you conduct for Part 1 and Part 2.
Part 1
Show the various cash flows based on the different scenarios; assuming that the Candy Crash decides to proceed with the supply of private label confectionery to Costca; taking into consideration of the various scenarios. You should also clearly state any assumptions (if any) made in your analysis. Based on your analysis, is this project a good investment project for Candy Crash?
Part 2
Using Capital Asset Pricing Model (CAPM), calculate the daily abnormal return of Candy Crash during the event window and plot the cumulative daily abnormal returns on a diagram. Daily abnormal return is computed as:
Abnormal return = Actual return Expected return
Discuss the abnormal return pattern of Candy Crash before, during and after the announcement and justify whether the stock price reaction is consistent with semi-strong form market efficiency.
Your response should also include:
whether the abnormal return pattern is consistent with the analysis conducted from Part 1; and
expectations of what would happen to the share price subsequent to the analysed event window.
Supporting resources
The following resources will assist you with completing this assignment:
Show and hide formulas in Excel(Links to an external site.)(IQ Accounting Solutions 2014).
Chapter 3 Report writing(Links to an external site.)(Summers & Smith 2014).
Sample of a business-style report (PDF 375 KB)(Links to an external site.).
Swinburne Harvard style guide(Links to an external site.).
Submission details overviewAssignment criteriaRequest assignment extensionAssignment criteriaConsideration of cash flows in various scenarios.
Conclusions and recommendations for Part 1.
Calculation and plot of cumulative abnormal return on a graph
Discussion on abnormal return pattern before, during and after announcement
Knowledge and understanding of spreadsheet application.
Structure, referencing and presentation.
Writing style and grammar.
Your work will be assessed using the following marking guide:
Assignment 2 marking guide
Criteria No Pass Pass50-59% Credit60-69% Distinction70-79% High Distinction80-100%
Consideration of cash flows in various scenarios
(25%) No evidence of scenario testing or poor scenario testing. Limited scenario testing. Satisfactory scenario testing. Appropriate scenario testing. Superior scenario testing.
Conclusions and recommendations for Part 1
(15%) None provided or poor connection between analysis and conclusions. Limited connection between analysis and conclusions. Satisfactory connection between analysis and conclusions. Appropriate connection between analysis and conclusions. Superior connection between analysis and conclusions.
Calculation and plot of cumulative abnormal return on a graph
(10%) None provided or poor plot - e.g., no labels and descriptions Adequate plot - e.g., Unclear labels and descriptions Satisfactory plot - e.g., reasonable labels and descriptions Appropriate plot - e.g., appropriate labels and descriptions Superior plot - e.g., clear labels and descriptions
Discussion on abnormal return pattern before, during and after announcement
(20%) No evidence of discussion or poor discussion on stock price behaviour Limited discussion on stock price behaviour Satisfactory discussion on stock price behaviour Appropriate discussion on stock price behaviour Superior discussion on stock price behaviour
Knowledge and understanding of spreadsheet application
(20%) No evidence of use of spreadsheet or poor use of spreadsheet - e.g., no formulas Limited use of spreadsheet - e.g., limited use of formulas Satisfactory use of spreadsheet - e.g., reasonable use of formulas Appropriate use of spreadsheet - e.g., formulas used, but minor errors Competent use of spreadsheet - e.g., correct formulas
Structure, referencing and presentation
(5%) No evidence of structure or thought of presentation or referencing; or poor attention to structure, referencing and presentation. Limited attention to structure, referencing and presentation. Satisfactory attention to structure, referencing and presentation. Appropriate attention to structure, referencing and presentation. Superior attention to structure, referencing and presentation.
Writing style and grammar
(5%) No evidence of checking for style and grammar, or poor attention to style and grammar. Limited use of style and grammar. Satisfactory use of expression, but contains spelling errors. Appropriate use of expression with minimal spelling errors. No easily discernible errors in expression.
References
IQ Accounting Solutions 2014,Show and Hide Formulas in Excel, 1 April, online video, viewed 17 July 2019, <https://www.youtube.com/watch?v=IIgX1H3Yod8>.
Summers, J & Smith, B 2014,Communication Skills Handbook, 4th edn, Wiley, ProQuest Ebook Central, <https://ebookcentral.proquest.com/lib/swin/detail.action?docID=4813164>.
FIN80005: Corporate Financial Management
Assignment 2: Excel report Capital budgeting
Word limit:1500 (+/- 10%)
Weighting:40%
Due date:5pm AEST 15 September 2022 (Week 10)
After you have read this information, head over to theAssignment 2 Q&Adiscussion board to ask any questions and see what your peersare saying about this assignment.
Assignment Overview
The objective of this assignment is to use Excel spreadsheets to aid in solving a capital budgeting problem and to analyse how the market impounds new information into stock prices.. The concepts in Weeks 48 are particularly relevant to this assignment. You will solve a capital budgeting exercise using Excel, with additional discussion on findings, assumptions and recommendations.
This assignment supportsunit learning outcomes 1, 2, 3 and 4.
Assignment Detail
Hypothetical company background
You are an analyst working in the finance department of Candy Crash Limited. Candy Crash is a public listed company that specializes in the production of confectionery. The production relies heavily on the use of machinery. The company has 2,115,000 number of shares outstanding trading on the stock exchange.
Part 1
Candy Crash is currently in negotiation with a large supermarket chain, Costca Limited, to supply its confectionery in a private label for Costca. Under the terms, Candy Crash is expected to supply confectionery to Costca every year for the next ten years.
If Candy Crash proceeds with the supply of confectionery, the company needs to purchase machinery to cope with the increase in production. New machinery is expected to cost $2,800,000, with an additional $500,000 installation and shipping costs. The machinery is expected to have a working life of 10 years. The companys accounting policy is to depreciate using the straight line approach of 10% per year. It is expected that the new machinery can be sold for $200,000 at the end of its useful life.
If Candy Crash is to proceed with the supply of confectionery to Costca, it is expected that the yearly operating revenues would increase by $2,400,000 in year one. From year two onwards, it is expected that the increase in yearly operating revenues would grow at a rate of 5% per annum. Total variable costs associated with the increased production would be 60% of the increase in yearly operating revenues. The fixed costs associated with the increased production are expected to be $300,000 per year. However, as the private label confectionerys selling price is cheaper than Candy Crashs brand, it is expected that Candy Crashs existing operating revenues would fall by $200,000 per annum and existing operating costs would decrease by $80,000 per annum if Candy Crash proceeds with the supply of confectionery. Moreover, there would be an initial increase in net working capital of $50,000. From year one to year nine, net working capital is expected to increase by $10,000 per year. All the net working capitals can be recovered at the end of the projects life.
Given that this projects risk level is not significantly different, you believe that it is appropriate to use the existing WACC of 12.6%. The companys capital structure has remained fairly stable, with a debt-to-equity ratio of 0.8. The company has no plan to adjust its capital structure in the future. The company tax rate is 30%.
Furthermore, the CEO suggests conducting sensitivity analysis as follows because of uncertainty in relation to some of the expected cash flows:
Allow for a 30% probability that incremental revenues associated with the supply of private label confectionery would be 40% lower than expected starting from year six;
Allow for a 20% probability that incremental revenues associated with the supply of private label confectionery would be 30% higher than expected starting from year six.
Part 2
Semi-strong form efficiency tests are concerned with whether security prices reflect all publicly available information. The event study methodology can be used to investigate the effects of many events such as a corporate announcement. By studying the stock price reaction before, during and after an announcement, an examination of whether the market is semi-strong form efficient can be conducted.
After performing the full analysis in Part 1, Candy Crash decides to proceed with the supply of private label confectionery to Costca (regardless of the NPV and other investment analyses). As such, the company announces details related to the expected increase in profits and net cash flows that it would achieve from the supply of private label confectionery. The table below shows the daily returns of Candy Crash (stock), the market and the risk-free asset 5 days before and after the announcement. Day 0 is the day of the announcement and there is no other price-sensitive announcement within the event window. The company has an equity beta of 1.2.
Table 1. Daily returns for Candy Crash, market and risk-free asset during the even window.
Table 1
Day Stock return Market return Risk-free
-5 0.20% 0.28% 0.0075%
-4 0.35% 0.22% 0.0075%
-3 -0.28% 0.09% 0.0075%
-2 -0.40% -0.20% 0.0075%
-1 1.20% 0.30% 0.0075%
0 2.50% 0.30% 0.0075%
1 -1.30% -0.20% 0.0075%
2 -1.66% -0.10% 0.0075%
3 -0.50% 0.10% 0.0075%
4 0.40% 0.20% 0.0075%
5 0.26% 0.35% 0.0075%
Table 1 (2020) courtesy of Dr Mardy ChiahRequired
You are to prepare a report to present to the CEO, based on the Excel analysis you conduct for Part 1 and Part 2.
Part 1
Show the various cash flows based on the different scenarios; assuming that the Candy Crash decides to proceed with the supply of private label confectionery to Costca; taking into consideration of the various scenarios. You should also clearly state any assumptions (if any) made in your analysis. Based on your analysis, is this project a good investment project for Candy Crash?
Part 2
Using Capital Asset Pricing Model (CAPM), calculate the daily abnormal return of Candy Crash during the event window and plot the cumulative daily abnormal returns on a diagram. Daily abnormal return is computed as:
Abnormal return = Actual return Expected return
Discuss the abnormal return pattern of Candy Crash before, during and after the announcement and justify whether the stock price reaction is consistent with semi-strong form market efficiency.
Your response should also include:
whether the abnormal return pattern is consistent with the analysis conducted from Part 1; and
expectations of what would happen to the share price subsequent to the analysed event window.
Supporting resources
The following resources will assist you with completing this assignment:
Show and hide formulas in Excel(Links to an external site.)(IQ Accounting Solutions 2014).
Chapter 3 Report writing(Links to an external site.)(Summers & Smith 2014).
Sample of a business-style report (PDF 375 KB)(Links to an external site.).
Swinburne Harvard style guide(Links to an external site.).
Submission details overview
For this assignment, you will be required to submit your excel workings as well as your 1500 word report. You can do this by uploading each file to the submission link.
This assignment will be submitted through Canvas. When you are ready to submit your assignment, select the 'Start Assignment' button at the top of this page. You will be taken to the 'File Upload' tab where you can choose your file or submit your URL.
Assignment Criteria
Consideration of cash flows in various scenarios.
Conclusions and recommendations for Part 1.
Calculation and plot of cumulative abnormal return on a graph
Discussion on abnormal return pattern before, during and after announcement
Knowledge and understanding of spreadsheet application.
Structure, referencing and presentation.
Writing style and grammar.
Your work will be assessed using the following marking guide:
Assignment 2 marking guide
Criteria No Pass Pass50-59% Credit60-69% Distinction70-79% High Distinction80-100%
Consideration of cash flows in various scenarios
(25%) No evidence of scenario testing or poor scenario testing. Limited scenario testing. Satisfactory scenario testing. Appropriate scenario testing. Superior scenario testing.
Conclusions and recommendations for Part 1
(15%) None provided or poor connection between analysis and conclusions. Limited connection between analysis and conclusions. Satisfactory connection between analysis and conclusions. Appropriate connection between analysis and conclusions. Superior connection between analysis and conclusions.
Calculation and plot of cumulative abnormal return on a graph
(10%) None provided or poor plot - e.g., no labels and descriptions Adequate plot - e.g., Unclear labels and descriptions Satisfactory plot - e.g., reasonable labels and descriptions Appropriate plot - e.g., appropriate labels and descriptions Superior plot - e.g., clear labels and descriptions
Discussion on abnormal return pattern before, during and after announcement
(20%) No evidence of discussion or poor discussion on stock price behaviour Limited discussion on stock price behaviour Satisfactory discussion on stock price behaviour Appropriate discussion on stock price behaviour Superior discussion on stock price behaviour
Knowledge and understanding of spreadsheet application
(20%) No evidence of use of spreadsheet or poor use of spreadsheet - e.g., no formulas Limited use of spreadsheet - e.g., limited use of formulas Satisfactory use of spreadsheet - e.g., reasonable use of formulas Appropriate use of spreadsheet - e.g., formulas used, but minor errors Competent use of spreadsheet - e.g., correct formulas
Structure, referencing and presentation
(5%) No evidence of structure or thought of presentation or referencing; or poor attention to structure, referencing and presentation. Limited attention to structure, referencing and presentation. Satisfactory attention to structure, referencing and presentation. Appropriate attention to structure, referencing and presentation. Superior attention to structure, referencing and presentation.
Writing style and grammar
(5%) No evidence of checking for style and grammar, or poor attention to style and grammar. Limited use of style and grammar. Satisfactory use of expression, but contains spelling errors. Appropriate use of expression with minimal spelling errors. No easily discernible errors in expression.
References
IQ Accounting Solutions 2014,Show and Hide Formulas in Excel, 1 April, online video, viewed 17 July 2019, <https://www.youtube.com/watch?v=IIgX1H3Yod8>.
Summers, J & Smith, B 2014,Communication Skills Handbook, 4th edn, Wiley, ProQuest Ebook Central, <https://ebookcentral.proquest.com/lib/swin/detail.action?docID=4813164>.
Referencing
Referencing conventions required for this unit are: Swinburne Harvard.
To avoid plagiarism, you are required to provide a reference whenever you include information from other sources in your work. You can check your assignments ahead of submission by using a program called Turnitin. TheAcademic practicepage in the Student Hub has information on plagiarism, referencing and Turnitin.
Helpful information on referencing can also be found in thisReferencingguide.
Recording of more details about the assignment
https://au-lti.bbcollab.com/recording/36e9286ec54f4e618d744c48999d4b89
