Assessment overview
The purpose of this assessment task is to encourage you to use Excel spreadsheets to aid in financial problem solving. You will solve a capital budgeting problem using an Excel spreadsheet, with additional discussions on findings, forecast errors and risk.
________________________________________
Assessment details
Using an Excel Spreadsheet prepare a full analysis to be presented to the General Manager (GM) of RUNWELL Corporation (see the scenario on the following page). Evaluate whether the existing product line should be replaced by the new product line. Your analysis should include the following:
• A written report (1200 words, +/- 10%) outlining your recommendation as to whether RUNWELL Corporation should proceed. Justify your recommendation and your analysis of risk.
• Table of cash flows.
• Use of Excel formulae where appropriate.
Your final submission must include the following:
• The budgeting report.
• The Excel spreadsheet with values.
• The Excel spreadsheet with formulas.
________________________________________
Additional resources
Scenario for the budgeting report
The GM of RUNWELL Corporation needs a detail analysis on an exciting proposal to introduce a new line of vehicle parts for environmental protection against carbon emission. Starting the production line requires renovating one existing section of the factory. It will be a business to business (B2B) contract based project that will continue for eight years. It’s projected that technological up-gradation of car manufacturing process will make this production line obsolete in ten years’ time. In winning this contract through a bidding process, the company has spent $41,000. Required renovation can be conducted in a short span of time at a cost of $130,000 that includes installation cost of new plant and equipment (P&E). The contract requires an annual quality assurance inspection that will cost $36,000 per annum. The procurement of human resources (HR) will be one-off cost at the beginning and estimated to be $48,000.
A local distributor of a Japanese company can immediately supply all required parts and accessories of the new P&E for a total charge of $1,400,000 including import duty of $210,000. In addition, transportation cost of $40,000 and installation costs of $70,000 are to be incurred for new P&E. These P&E would be depreciated over its useful life of ten years using a tax allowable straight line rate of 10%. However, the company can sell the machine at the end of the contract for $250,000 after incurring an additional cost of $24,000. The company has decided to capitalise total renovation costs to new P&E. RUNWELL will be in contract to supply 48,000 boxes of the parts per year and that will require RUNWELL to operate at 80% of its capacity when variable operating cost will be 50% of sales. Selling price per box will be $30. Annual fixed operating cost, excluding depreciation, will be $160,000. It is estimated that the production line will operate at full capacity during the last four years due to increasing demand. Variable operating cost at full capacity would be 45% of sales. Existing section of the factory, where the new P&E will be installed, is in use by a subcontractor who pays monthly rent of $2,500. Therefore, RUNWELL has to forgo the rent income once the new production line commences its operation. In addition to initial employee training cost of $26,000, there will be additional training expense of $18,000 in the first year. It is also estimated that the new production line will require an initial increased investment of $51,000 in stock and $23,000 in debtors that are offset by an increase in creditors of $25,000. The firm has a 14% weighted average cost of capital (WACC) and is subject to a 30% tax rate. The required discounted payback period is 5 years. The GM hesitates to take the final decision because of unexpected growth in car manufacturing technology. RUNWELL has an offer to sell the contract to another compliant company for $200,000. The GM also asks whether or not the discount rate should be increased to allow for the risk of the above contract or is the WACC appropriate?
MUST INCLUDE:
1.BUDGETING REPORT-
Executive Summary
1. Report only the highlights of the findings.
2. Entice an Executive to read on.
3. Essentially a lively summary of the main conclusions.
4. No longer than one page; this is not counted in the word count and must be on a separate page from the rest of the report.
Introduction
1. State the purpose of the report i.e. what you will discuss in the report
2. Outline the contents of the Report
3. Provide a brief description of the methodology
4. Describe the source of the data and state its location in the report.
5. This should contain information about what we expect to read in the project. This should take about half a page.
Analysis
1. Contains a thorough yet non-technical description of all the findings (graphs and tables will be included only where they help this discussion).
2. Details the results that were highlighted in the Executive Summary
3. Do not include any calculations here but include appropriate graphs and tables which are needed to support your discussions.
Conclusion
1. Report the findings and results of your work.
2. Essentially an expansion of the executive summary written from the point of view that the Executive Summary has not been read.
3. End with a discussion of the limitations of your analysis (e.g. reference to sample size if small, or comment on the data if it is old).
Appendices
1. Must be referred to in the main body of the report.
2. Must contain your selection of random numbers and related random data.
3. Include the raw data, charts and tables that are not essential, but support the ANALYSIS section.
4. Include your EXCEL output for descriptive statistics, confidence intervals, hypothesis testing and regression.
5. Include any other relevant calculations.
2.EXCEL spreadsheet with values
3.EXCEL spreadsheet with formulas