1. OVERVIEW
The Board of Directors of Woolworths Limited (WOW) has tasked the Finance Director with the analysis of an Energy Efficiency related initiative. The initiative stems from WOW’s corporate sustainability principles and the objective to reduce carbon emissions by direct abatement. Your group is a team of WOW management accountants, assigned to assist the WOW 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 of WOW (i.e., your CMA Lecturer) will review your financial modelling. He will need to be very comfortable with your technical execution. Like many senior managers, he is extremely time poor and needs to be communicated to very concisely. 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).
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 in the report. The Finance Director is highly knowledgeable about the business and he will be irritated by time wasted reading an unfocussed report. He wants the report to 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. It 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.
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 must not plagiarise.
4. MAXIMUM LENGTH
The Excel have no size restrictions.
The written report must be formatted as follows (including insertion of Excel extract tables):
• Margins of 2.5cm (all four margins)
• Adequate spacing between lines and paragraphs
• Font size of 11
• Maximum page length = 7 + Peer Review Sheet + Cover Page1 + Reference List
Pages beyond the maximum length will NOT be read or marked – remember that the Finance Director is extremely time-poor! The Cover Page should not include the Executive Summary.
You may not need a reference list if you use footnotes instead. Remember this is not an academic style report (it is a business style report) and so you should limit referencing to the bare minimum.
APPENDIX ONE: NDC Freezer Closure Initiative
This fictitious case study has been put together using only publicly available information
A. Background and Context
In Woolworths Supermarkets Australia, Stock Keeping Units (SKUs) have three distinct temperature zones: (1) ambient; (2) chilled and (3) frozen (i.e., freezer). The entire ambient and chilled SKU ranges are warehoused in five Regional Distribution Centres (RDCs), each of which also warehouses the entire freezer RDC SKU range. There is also a freezer National Distribution Centre (NDC). The frozen range warehoused in the five RDCs are fast-moving SKUs (i.e., having high customer demand) whilst the NDC only warehouses slow-moving SKUs (i.e., having low customer demand). The five RDCs hold identical ranges of ambient, chilled and frozen SKUs, whilst the NDC holds a unique range of frozen SKUs.
Deliveries to supermarket stores (i.e., ‘into-store transport’) only occur from the five RDCs. NDC ranged frozen SKUs are first transported by trucks using pallets from the NDC to a RDC (i.e., ‘NDC-to- RDC transport’), where they are temporarily staged (stacked on the floor) before being included on trucks for the into-store transport leg along with pallets of RDC ranged freezer SKUs (in special refrigerated trucks).
The five RDCs are: Sydney, Melbourne, Brisbane, Adelaide and Perth. Tasmania is not in any way implicated in the decision to close the NDC. Whilst the financial analysis you are to perform is on a national level, it must be built-up from the regional level.
The initiative for analysis by your group is the potential permanent closure of the NDC. The NDC frozen SKU range would cease to be warehoused of sold in the supermarkets. The released freezer space capacity in supermarket stores would be used to stock more of the RDC frozen SKU range.
The NDC closure initiative has come about for two reasons. First, the NDC freezer range consumes substantial energy (electricity and diesel fuel). WOW is serious about its corporate sustainability principles and the objective of reducing carbon emissions by direct abatement. Second, if it is not closed, the NDC will instead require significant capital expenditure to refurbish it to keep it operational. Hence, there are two scenarios for you to analyse: (1) NDC closure, versus; (2) NDC refurbishment.
Your group is tasked with providing financial analysis for the Finance Director to choose between the two scenarios. There are two sources of information: (1) this document and; (2) the WOW_excel_file on UTS Online. Be careful, some of the information we provide may not be relevant!
For simplicity, you must assume: (1) the NDC would close at the cross-over point between FY15 and FY16, and (2) the refurbished NDC assets would be operational (and depreciable) on the first day of FY16, but paid for in FY15. More information about these assumptions is given in following.
The WOW Fiscal Year is from July to June. FY15 ends on 30th June 2015.
B. Volumes
There are three bases for volumes: pallets, cartons and items (sale units). On average, regardless of temperature zone, there are 5 items (sales units) per carton (e.g., there are 5 packets of breakfast cereal in a carton).
The WOW_excel_file on UTS Online provides Regional Stores Profile (i.e., the number of supermarket stores per RDC).
If the NDC was closed, there would be a period of complex transition, with a gradual deletion of the NDC SKU range from stores as customers purchased the last of the stock. However, for your financial modelling, for simplicity you must assume that the NDC would despatch normal volumes up to and including the final day of FY15. Also, you must assume that stores would sell normal NDC SKU volumes up to and including the final day of FY15, after which they would sell no more.
Deleting the NDC SKUs would have consequences for customer behaviour, which you must assume would wholly take affect from the beginning of FY16. It is expected that ambient and chilled volumes would each decrease by 1.00% whilst RDC freezer SKU volumes would increase by 10.00%. Whilst some customers will substitute RDC freezer SKUs for NDC SKUs, other customers will permanently switch their shopping to the main competitor, Coles, who have a larger range of frozen SKUs.
C. NDC and RDC warehousing costs
Each temperature zone of each DC is a distinct cost centre. All costs (whether fixed or variable) are allocated to the cost object of ‘carton’. The WOW_excel_file shows the FY15 budget Cost Per Carton (CPC), with a breakdown into Fixed (FCPC) and Variable (VCPC).
Fixed costs include warehouse management, depreciation and other costs. Each FY15 budgeted fixed cost pool has a relevant range that is +/- 50% of current carton volumes (e.g., if FY15 volumes are 10M cartons per year, the relevant range would be between 5M and 15M cartons per year).
Variable costs are incurred for receiving (unloading from vendor trucks), storing and sorting (in the warehouse) and loading cartons onto store-bound trucks. Cartons are received and loaded using pallets. Variable costs comprise labour, electricity, equipment rental (e.g., forklifts) and other costs.
Figure 1: Pallet
The NDC SKUs are only delivered into stores via trucks despatched from the RDCs, using the NDC cross-docking process. Each RDC has a receiving dock and a despatching dock. The receiving dock is for unloading pallets from trucks into the warehouse. The despatching dock is for loading pallets onto trucks for delivery into-stores. In each freezer RDC, the NDC-cross-docking process is the activities for unloading NDC pallets at the receiving dock, storing them on the floor, and then loading them onto trucks at the despatching dock (for the into-store transport leg). The NDC cross-docking volumes are not included in the RDC carton volume counts. In the FY15 budget, 3% of the freezer NDC VCPC is for recharges from the RDCs for costs incurred for NDC cross-docking.
Approximately 40% of the NDC total costs are for electricity (mainly for refrigeration). There would be no net change to electricity consumption in the RDCs if the NDC was closed.
D. Transport costs
There are two sets of transport (i.e., truck) operations: NDC and RDC. As stated above, the NDC trucks do not deliver into-stores – they only deliver to RDCs. At a RDC the truck is fully unloaded before the truck returns directly to the NDC.
The NDC transport operations are outsourced to ABC Transport Pty Ltd. The contract is cancellable by WOW at any time without financial penalty. The contract has two types of costs:
a) Fixed: FY15 cost of $2.0m.
b) Variable: as per the WOW_excel_file. Approximately 75% of the variable cost paid by WOW reimburses ABC for their diesel fuel costs. The variable rate includes a buffer for ABC to absorb the risk from diesel fuel price volatility.
In addition to the ABC contract costs, the NDC FCPC and VCPC include other transport related costs incurred by WOW for NDC transport operations.
The RDC transport operations are owned and operated by WOW. Each RDC has its own truck fleet for transporting pallets to supermarket stores. Cost centres are based on temperature zone, i.e., ambient, chilled and frozen. Pallets loaded with RDC freezer SKUs are combined with pallets loaded with NDC freezer SKUs for transportation into-stores.
Each RDC transport fleet has two types of costs, as per the FY15 budget figures in the
WOW_excel_file:
a) Fixed – cost per carton.
b) Variable – cost per carton.
Each truck despatched from a RDC is bound for a single supermarket only – after delivering the pallets to the single store the truck returns directly to the RDC. Each fixed cost pool has a relevant range that is +/- 50% of current carton volumes (e.g., if current volumes are 10M cartons per year, the relevant range would be between 5M and 15M cartons per year).
Approximately 85% of the variable costs incurred by WOW for RDC transport are for diesel fuel.
E. In-store costs
If the NDC was closed, there would be no changes to the freezer infrastructures in stores. The capacity made available in each store from jettisoning the NDC SKUs would be used to increase the RDC SKU range inventory. There would be no change to any fixed costs in stores (including electricity). Store variable cost per carton would not change (i.e., for labour, waste removal, etc.). The FY15 budgeted stores CPC are provided in the WOW_excel_file.
F. Sales and gross profit – impacts
If the NDC was closed, the volume changes discussed previously would impact sales and gross profit. The WOW_excel_file shows the FY15 budget sales mix and gross profit profiles.
G. Inventory Holdings
The inventory holding cost value in the NDC is equivalent to the purchase costs of 90 days of NDC SKU sales (on the basis of 365 days of sales per year). The NDC inventory holdings figure includes stock in the RDC cross-docking process. Assume that inventory holdings in the RDCs would not change if the NDC was closed (either in FY15 or in the future). Also assume that inventory holdings in store would be unchanged if the NDC was closed (either in FY15 or in the future). For inventory holdings, the timing of the cash flow is within the year, e.g., a change in inventory from FY15 to FY16 (in line with year-on-year sales growth) is a cash flow in FY16.
For simplicity, you are to assume that if the NDC was closed at the end of FY15, the NDC inventory holding at the end of FY15 would be zero (even though the operational reality would be far more complicated). Similarly, you are to assume that the NDC inventory holding at the end of FY25 would be zero.
H. Project Capex and Transition Expenses
The NDC Fixed Asset Register is in the WOW_excel_file.
If the NDC was closed, the site would be sold as-is in FY16, inclusive of all items in the Fixed Asset Register (which would cease to be depreciated after FY15). The cash proceeds from sale, net of transaction costs, would be received in FY16, estimated as $20.0m. Project related costs (to be immediately expensed as transition expenses) for closing the NDC would be $0.2M in FY15 and
$0.1M in FY16. There would be no personnel redundancy costs as all NDC-based employees would be re-deployed elsewhere within the business.
If the NDC was refurbished, the Capex expected is in the WOW_excel_file. The following asset classes from the NDC Fixed Asset Register would be scrapped and fully written down to zero at the end of FY15: Fit-Out; IT; Racking and Storage and; Refrigeration Equipment (the other asset class balances would remain). The acquisition date (and depreciation start date) for the replacement (refurbished) assets would be 01-July-2015. The refurbished NDC would have a useful life of 10 years, i.e., until 30 June 2025, with the assumption that it would be closed and sold in FY16 (see the assumption listed below for more information). The WOW policy for IT assets is to replace them as soon as they have been fully depreciated, recognising the cash out flow in the period of the acquisition date. The refurbishment would require transition expenses of $0.45M in FY15 (to temporarily outsource the NDC SKU supply during the refurbishment). None of these refurbishment related costs have been included in the FY15 budget.
MANDATORY ASSUMPTIONS AND PARAMETERS:
a) The WOW_excel_file contains crucial information you will need.
b) The WOW_excel_file contains a tab called ‘Summary Table – MUST SUBMIT’ which you must populate and include in the excel file that you email to your lecturer.
c) Assume that every store has the same sales volume, sales mix and gross profit profile.
d) Assume that the sales mix and gross profit profile in the FY15 Budget will also be in effect over future years.
e) Assume that the FY15 Regional Stores Profile (i.e., number and locations of stores per RDC as per the WOW_excel_file) will remain unchanged over future years.
f) Assume volume growth of 2.0% per annum across all SKUs (e.g., with NDC refurbishment, the FY16 volumes will be 2% higher than the FY15 budget).
g) Assume no other relevant cost impacts other that those discussed in this document.
h) Assume inflation of 3% per year; i.e., fixed (excluding depreciation), variable, capital, purchase costs etc. Use this inflation figure also to assume that the selling price of all items in stores grows by 3% per year (i.e., the FY16 prices will be 3% higher than FY15 budget).
i) Assume that, unless stated otherwise above, the cash flow for an item occurs in the period in which the cost is incurred.
j) For a terminal value, assume that, starting from FY16, the refurbishment scenario would have a useful life of 10 years, after which time the NDC site would be sold as-is in the following year (i.e., FY26) inclusive of all items in the Fixed Asset Register. The cash proceeds of $30.0M (net of transaction costs) would be received in FY26. Also, project related costs of
$0.5M would be incurred in FY26. Regardless of this 10 year assumption, for depreciation purposes, apply the useful lives provided for the Fixed Asset Register and Capex information in the WOW_excel_file and cease depreciation in FY25.
k) As stated earlier, assume that the NDC would cease to operate on the final day of FY15. Assume that all sales and operating impacts would take effect from the first day of FY16.
l) The WOW company tax rate is 30%.
m) The WOW discount rate for projects of this type is 12%.
n) Ignore GST – all figures provided exclude GST.
o) Do not use any rounding in any of your calculations.
p) Assume that diesel fuel costs $1.40 per litre in FY15. Use the formula provided in the Bunker Freight Case to calculate CO2−e (tonnes) avoidable in FY15 by closing the NDC
Assume that electricity costs $100.00 per megawatt hour in FY15. Assume that one megawatt hour of electricity equates to 0.86 tonnes of carbon dioxide pollution. Calculate the CO2−e (tonnes) avoidable in FY15 by closing the NDC.
Use the order calculator below and get started! Contact our live support team for any assistance or inquiry.
[order_calculator]