ASSESSMENT ITEM 2: GROUP ASSIGNMENT
Weighting: 20%.
Spring 2015
1. OVERVIEW
The Board of Directors of Wesfarmers (‘WES’ on the Australian Stock Exchange) has tasked the
Finance Director of the Coles Division of WES with the analysis of an Energy Efficiency related
initiative. The initiative stems from WES’s corporate sustainability principles and the objective to
reduce carbon emissions by direct abatement. Your group is a team of Coles management
accountants, assigned to assist the Coles Finance Director (i.e., your CMA Lecturer) by performing
the required financial analysis. The initiative is fully outlined in Appendix One of this document.
Your group must submit and present your financial analysis in three formats:
A. Excel – financial modelling
B. Word – written report
C. PowerPoint – for presentation in-class (5 minutes per group).
Extracts from the Excel financial modelling are to be included in your Word report and PowerPoint
presentation.
2. EXCEL
The Finance Director (i.e., your Lecturer) will review your financial modelling. They will need to be
very comfortable with your technical execution. Like many senior managers, (s)he is extremely time
poor and needs to be communicated to very concisely. (S)he needs to quickly understand the design
of your models, as well as assumptions and limitations.
You will email your Excel file to your lecturer for review. Here is what the Finance Director will
expect to see:
• The model is clearly structured, with supporting tables in separate tabs that build-up from a
detailed breakdown.
• Input and output tables are clear and separate.
• Tables logically present key components (e.g., costs, benefits, EBIT, assumptions and
parameters).
• The model is materially complete and comprehensive.
• The model is technically correct, e.g., cash flows are on an after-tax basis and treatments are
conventional.
• Excel extracts used in the written report or PowerPoint presentation should be appropriately
formatted, e.g., present figures in $M (e.g., $0.18M for $180,000).
- 1 -
CMA – GROUP ASSIGNMENT
3. WORD
Remember, the Finance Director is your audience, so focus on the numbers. It should be very clear
what the document is about, what the analysis implies, and what recommendations you are making
(if any) for future progression of the initiative.
You will lose marks if you include superfluous information. The Finance Director is highly
knowledgeable about the business and will be irritated by time wasted reading an unfocussed
report. Your report must present only pertinent information and key aspects of the financial
modelling.
This is a business style report for a non-academic ‘insider’. It must provide concise and easily
readable information to aid decision-making. It must include explanation of major conceptual points
with simple definitions and explanation of relevant terminology (especially operational matters) and
complexities). The Finance Director is often unfamiliar with operational issues.
You MUST INCLUDE an Executive Summary. Assume that your report will be sent to the CEO so it
must be comprehensive and describe the ‘big picture’ and the main ‘numbers’ (e.g., EBIT, Capex,
NPV, IRR). The structure should also include Introduction, Discussion of Key Findings, and
Conclusion. The Cover Page should not include the Executive Summary.
The Finance Director will expect your report to:
• analyse and discuss the information, and evaluate it critically.
• identify problems and suggest solutions.
• speculate about future trends and impacts.
The writing style must be a non-academic report - see the Guide to writing assignments issued by the
UTS Business School: www.uts.edu.au/node/50946/
An appropriate report format will include headings, sub-headings, bullet-points, tables and figures. It
must be easy for the Finance Director to understand the structure, and be able to selectively read
sections of the whole report rather than the whole thing from start to finish. Although the report is
to be in a non-academic style, you must reference appropriately and you absolutely must not
plagiarise.
APPENDIX ONE: Solar Panels Initiative
The Coles Energy Efficiency work group has identified the potential opportunity to install solar
panels on the roof of selected Coles distribution centres (DC). As per Table 1, the work group put
together a list of suitable DCs with solar system capacity size.
Table 1: maximum size of solar capacity per DC
Table 2 shows the outcome of negotiations with suppliers for solar panels. There are two alternative
types of solar panels that are being investigated: (1) polycrystalline and (2) monocrystalline. The
tabled costs include all equipment and accessories (e.g., inverters and bi-directional meters) for
synchronisation with the public electricity grid (i.e., feeds unused solar generated power back into
the public grid and WES would receive a purchase cost credit).1 Additional to Table 2, installation
costs of $50 per square meter of solar panel would be incurred, regardless of panel type.
Table 2: Solar panels purchase costs
Ongoing annual operating costs that would be incurred should solar panels systems be installed
would be limited to maintenance costs of $0.50 per square (for servicing).
Polycrystalline panels have an average efficiency of 130 Watts per square meters. Monocrystalline
solar panels have an average efficiency of 170 Watts per square meter.
1 For example, a 950kW polycrystalline system would cost $3,500 x 950 = $3,325,000.
DC State
Solar system
sqm
Goulburn NSW 6,000
Altona North RDC VIC 5,000
Heathwood QLD 8,000
Edinburgh Parks SA 7,000
Kewdale RDC WA 4,000
Solar System
Capacity kW
Solar Panels Cost
per kW
Polycrystalline
Solar Panels Cost
per kW
Monocrystalline
1,300 3,300 3,960
1,300 3,450 4,140
1,100 3,500 4,200
900 3,800 4,560
700 4,100 4,920
500 4,500 5,400
300 5,000 6,000
- 7 -
CMA – GROUP ASSIGNMENT
The output of a solar panel system depends on system capacity and the amount of sun. A system
capacity of 100kW can produce up to 100kW of power per hour under optimal conditions, i.e., the
system produces 100kWh. The amount of sun exposure depends on technical factors such as
orientation and tilt of the panels and on number of daylight hours, full sun versus cloudy days and
intensity of the sunlight. The last three factors are governed by the geographical location. For your
energy calculation, sunshine and related average solar outputs per region are available in Table 3.
Table 3: Average daily electricity output of solar panel systems2
Electricity providers (i.e., over the public grid) are allowed to pass on costs for carbon emissions to
customers and a carbon surcharge is included in the prices in Table 4.3
Table 4: Purchase costs of electricity
2 Source: Consumer guide to buying solar panels, Vol 14, 26 August 2011, available at www.cleanenergycouncil.org.au
3 One kWh means one unit of electricity. When we say we pay 20 cents per unit of electricity we mean we pay 20 cents for
each kWh of electricity that our appliances draw from the network. As an example, if you have a 50 watt light bulb running
for 20 hours, the bulb uses 50x20= 1000 watt-hours or 1 kilowatt-hour (kWh) of electricity. One kilowatt (KW) is 1000 times
a watt, and 1 megawatt (MW) is 1000 times a kilowatt.
State
Average daily kWh
produced per system kW
NSW 3.9
QLD 4.2
SA 4.2
TAS 3.5
VIC 3.6
WA 4.4
State Purchase cost per kWh
NSW $0.34
QLD $0.20
SA $0.40
TAS $0.25
VIC $0.22
WA $0.26
- 8 -
CMA – GROUP ASSIGNMENT
All costing information provided above is for FY17 incurrence, and excludes GST.
Other inputs and assumptions are as follows:
- Useful life of solar panels for financial reporting is 25 years, and for company tax 10 years.
- The hurdle rate (i.e., discount rate for cash flows) is 12.5%.
- Assume inflation rate of 3% per annum.
- The solar systems would be installed in FY17 and be 100% operational at the beginning of
FY18.
- Energy consumption in the DCs is expected to increase by 1.7% per annum for the life of the
solar panels.
- (Baseline) electricity price inflation rate of 7% p.a.
TASKS:
There are three tasks that are to form the basis of the three files that you submit (i.e., Excel, Word
and PowerPoint), as follows:
TASK A: complete the Analysis Table (in the Excel_Assignment.xlsx file on UTS Online) and include it
in all three files that you submit (i.e., Excel, Word and PowerPoint).
Table A: Analysis Table at baseline electricity inflation rate of 7%
? For the Analysis Table populate all the cells that have question marks (‘?’).
TASK B: complete the Executive Summary Table (also in the Excel_Assignment.xlsx file on UTS
Online) and include it in all three files that you submit (i.e., Excel, Word and PowerPoint)
Table B: Projects that meet the 12.5% hurdle rate at 7% electricity inflation rate
? For the Executive Summary Table you are to delete all the question marks (‘?’) and
populate only those cells that your analysis recommends as being: (1) financially justified
(i.e., a positive NPV) AND (2) financially superior (a single DC cannot have both
Polycrystalline and Monocrystalline panels). (Also you must populate the Totals row).
$m Polycrystalline Monocrystalline
DC Capex NPV FY18 EBIT Capex NPV FY18 EBIT
Goulburn ? ? ? ? ? ?
Altona North RDC ? ? ? ? ? ?
Heathwood ? ? ? ? ? ?
Edinburgh Parks ? ? ? ? ? ?
Kewdale RDC ? ? ? ? ? ?
$m Polycrystalline Monocrystalline
DC Capex NPV FY18 EBIT Capex NPV FY18 EBIT
Goulburn ? ? ? ? ? ?
Altona North RDC ? ? ? ? ? ?
Heathwood ? ? ? ? ? ?
Edinburgh Parks ? ? ? ? ? ?
Kewdale RDC ? ? ? ? ? ?
Total ? ? ? ? ? ?
- 9 -
CMA – GROUP ASSIGNMENT
TASK C: complete the Sensitivity Analysis Table (also in the Excel_Assignment.xlsx file on UTS Online)
and include it in all three files that you submit (i.e., Excel, Word and PowerPoint). Undertake
research to discuss the appropriateness of the baseline annual electricity inflation rate of 7%.
Discuss the appropriateness of the 3% rate and the 10% rate based on your research.
Table C: Sensitivity analyses – annual electricity inflation rate
? For the Sensitivity Analysis Table populate all the cells that have question marks (‘?’).
$m Polycrystalline
7% annual increase 3% annual increase 10% annual increase
DC NPV FY18 EBIT NPV FY18 EBIT NPV FY18 EBIT
Goulburn ? ? ? ? ? ?
Altona North RDC ? ? ? ? ? ?
Heathwood ? ? ? ? ? ?
Edinburgh Parks ? ? ? ? ? ?
Kewdale RDC ? ? ? ? ? ?
$m Monocrystalline
7% annual increase 3% annual increase 10% annual increase
DC NPV FY18 EBIT NPV FY18 EBIT NPV FY18 EBIT
Goulburn ? ? ? ? ? ?
Altona North RDC ? ? ? ? ? ?
Heathwood ? ? ? ? ? ?
Edinburgh Parks ? ? ? ? ? ?
Kewdale RDC ? ? ? ? ? ?