The Solar Panels Business CaseI. Introduction Central Arizona Storage has 850 stores. As part of the CEO’s low-cost supply chain strategy, $0.5bn of capital has recently been invested in a warehouse t

The Solar Panels Business CaseI. Introduction

Central Arizona Storage has 850 stores. As part of the CEO’s low-cost supply chain strategy, $0.5bn of capital has recently been invested in a warehouse transformation program. The objective of the strategy is to reduce costs to increase profits. However, news and financial analyst reports have viewed the recent performance of this transformation program as unsatisfactory because cost reductions are less than expected.

Central Arizona Storage has also received negative news reports and customer backlash about its lack of sustainability practices. While Central Arizona Storage has been preoccupied with the warehouse transformation program, its two main competitors have developed strong reputations for sustainability practices. The competitors’ annual reports are filled with discussions and performance measures about the successes of their sustainability strategies—especially concerning their switching to sustainable energy sources. Central Arizona Storage is lagging behind and customers are switching to the competitors.

Realizing that this is an issue that needs to be addressed, Central Arizona Storage is evaluating projects that incorporate sustainability practices. One project under consideration is the installation of solar panels on the roofs of the North and South warehouses. Here is an excerpt from a conversation between the Senior Management Accountant (Beth) and his talented Junior Management Accountant (you):

Beth: ‘‘I need you and your management accounting team to prepare the solar panels business case. The more warehouse roofs we put solar panels on, the more carbon dioxide greenhouse gas emissions and electricity costs we can save.’’

You: ‘‘What information do we have?’’

Beth: ‘‘There are the North and South warehouses and two solar panels sourcing scenarios: outsourced and insourced. If outsourced, external contractors would supply and install the solar panels. If insourced, we would purchase solar panels from the manufacturer and install them with internally managed projects. The business case needs to quantify carbon dioxide greenhouse gas emissions and public grid electricity cost savings, with net present values and internal rates of returns for the six alternatives.’’

You: ‘‘There are six alternatives?’’

Beth: ‘‘Yes, six alternatives. For the insource scenario, there are three potential solar panels quantities: North only, South only, and North and South combined. The larger the quantity, the lower the price per panel from the manufacturer. For the outsourced scenario we have three quotes from external contractors: North only, South only, and North and South combined.’’

You: ‘‘Do we have the solar panel prices for the insource scenario?’’

Beth: ‘‘Not yet. The CEO and CFO will negotiate the prices for the three quantities with the manufacturer. Your Excel model needs to use Goal Seek to calculate the three maximum prices that give net present values and internal rates of returns that equal those of the corresponding outsource alternatives. If the CEO and CFO end up negotiating prices lower than those maximums, then we can insource those projects and achieve better financial outcomes.’’

You: ‘‘But what if the projects do not meet our 8.0 percent hurdle rate?’’

Beth: ‘‘The Board of Directors will only approve capital expenditures that meet the 8.0 percent hurdle rate. Ideally, we end up with solar panels on both warehouses to save the most carbon dioxide emissions. Only after the price negotiations will we be able to calculate all the final net present values and internal rates of return to know which insource alternatives are approvable and whether any are better than the corresponding outsource alternatives. When we get the negotiated prices, I will update the business case myself and pass it on to the CEO, CFO, and Board of Directors.’’

You: ‘‘Thank you for giving me this exciting opportunity to broaden my strategic management accounting skills.’’  

Beth: ‘‘For you to do this business case successfully, your calculations must be correct and concisely communicated. The CEO and CFO need clear and easily understandable information for their negotiations with the solar panels manufacturer and to communicate with the Board of Directors. Eventually, Central Arizona Storage would want to inform the investor and stakeholder communities about the electricity costs and greenhouse gas emissions saved.’’

Requirement 1a: Outsourced

For each of the three alternatives, calculate:

• EBIT
• EBITDA
• Relevant cash flows for the discounted cash flow model
• NPV of the relevant cash flows
• IRR of the relevant cash flows

Summarize the results in the Excel table provided for Requirement 1a (see Exhibit 6).

EXHIBIT 6: Panel A: Requirement 1aOutsourcedPanels (each 1 sqm)Cost per panel installed ($000)Capex ($000)NPV ($000)IRRYear 1 EBIT ($000)NorthSouthNorth + South

Requirement 1b: Insourced

For each of the three alternatives, use Goal Seek (explained below) to calculate:

• EBIT
• EBITDA
• Relevant cash flows for the discounted cash flow model
• NPV of the relevant cash flows
• IRR of the relevant cash flows

Summarize the results in the Excel table provided for Requirement 1b (see Exhibit 7).

EXHIBIT 7: Requirement 1bOutsourcedPanels (each 1 sqm)Installation capex ($000)Purchase cost per panel ($000)Capex ($000)NPV ($000)IRRYear 1 EBIT ($000)NorthSouthNorth + South

For Requirement 1b, use Goal Seek (under What-If analysis in Excel) to calculate the three maximum prices that give net present values and internal rates of returns that equal those of the corresponding outsource alternatives. The CEO will use the ‘‘Purchase cost per panel’’ information from the Requirement 1b table to negotiate with the solar panels manufacturer. When the negotiations are finished, the senior management accountant will use that information to update your financial model and business case.

Exhibit 8 provides a view on how to set up your financial model with Goal Seek. The ‘‘Purchase cost per panel’’ cells are values that are calculated by Goal Seek and those values are inputs into other cells in other worksheets in the financial model. The cells with formulas (partly shown) feed from outputs from other cells in other worksheets in the financial model.

Requirement 1c: Greenhouse Gas Emissions

To populate the Excel table for Requirement 1c, calculate the greenhouse gas emissions that would be saved. The ‘‘hypothetical example’’ (see Exhibit 9) cells contain formulas that you can easily copy from (after you have finished, delete the hypothetical example from your model). For calculating solar-generated kWh as a percentage of total electricity usage by a warehouse, the case only contains enough information to do this for Year 1.

EXHIBIT 9Year 1NorthSouthHypothetical ExampleSolar-generated electricity kWh100,000CO2 emissions factor0.871.170.87CO2 emissions (kg CO2) saved87,000CO2 emissions (tonnes CO2) saved87

Requirement 1d: Executive Summary Table

In the separate worksheet included, design an Executive Summary table that feeds from other worksheets in your financial model. Design your Executive Summary table to clearly present the following information:

• The maximum solar panel prices negotiable for insource alternatives.
• The sizes of the warehouse installations and total size of all installations.
• A comparison of each of the three insource alternatives with the respective outsource alternatives in terms of:
  • capital expenditures
  • cost per solar panel
  • EBIT and cash flow impacts
  • NPVs and IRRs
  • carbon dioxide (tonnes) savings in Year 1
  • warehouse electricity percent solar generated.

Requirement 2: Communication of the Modeling

Business cases provide information for decision makers about potential capital expenditures projects. Approvable projects typically must be financially justified by a positive NPV and acceptable IRR (although a Board of Directors can sometimes override this requirement). Qualitative issues involving strategic alignment are also important information in business cases. Your draft business case should concisely communicate if, and how, different alternatives would align with the strategic need to adopt sustainability practices. If applicable, analyze and communicate trade-offs between alternatives.

The senior management accountant and the CFO will be the immediate audience for your draft business case. They will need to be confident that it adequately presents and discusses the qualitative factors, and that the calculations are correct and concisely communicated. When they are confident, they will pass it on to the CEO and the Board of Directors.

Requirement 3: Written Report

The written report must be based on tables you extract (possibly including your own designs) from the Financial Model Template File Download Financial Model Template Fileyou submit. Here are the main points:

• Clearly present the financial information and discuss how the projects would achieve sustainability benefits.
• Do not include detailed discount cash flow models.
• Do not design new tables that would supplant the Requirement 1 tables.
• Include an Executive Summary with your Executive Summary table. The Executive Summary should concisely and comprehensively summarize the report.
• Summarize the operational issues and electricity grid synchronization.
• Discuss issues or assumptions made in the financial modeling.
• Conclude with the next steps for finalizing the business case.

NEED THE EXCEL DOC AND WORD DOC PLEASE!!!!!!!!!!

You should submit both your completed Excel file for this project and your written report in a Word document for your instructor to grade your final project submission.

Any and all calculations required to be made in the Excel file should be completed using the appropriate Excel commands.

The written report should be submitted in a Word document with a Title Page, a Reference page containing at a minimum 5 outside authoritative references, double-spaced, Times New Roman 12-font, and should be a minimum of 5 pages in length excluding the Title and Reference pages.  Your report should be addressed to the CFO and the Senior Management Accountant.