Category: Excel

Please complete using the directions on the Excel sheet. Send back answers in XLSX format.

Capital Investment Review Memorandum
Solara Energy Arizona Solar Farm Expansion

Executive Directive

As part of our capital allocation and long-term growth strategy, we are evaluating a proposed investment in a 100-acre utility-scale solar farm in Arizona. This project will support a five-year Power Purchase Agreement (PPA) and may position the company for future expansion.

You are hereby directed to conduct a comprehensive financial evaluation of this investment consistent with the standards applied in a professional corporate finance environment. This analysis will be reviewed at the executive and board levels. The quality, transparency, and defensibility of your work must reflect that reality.

Your responsibilities include:
Estimating the firms cost of capital from first principles
Constructing a fully integrated pro forma free cash flow model
Performing a complete capital budgeting evaluation
Conducting sensitivity and risk analysis
Presenting a concise executive-level investment recommendation

Under no circumstances may you assume or rely on a provided discount rate. You must independently estimate Solara Energys Weighted Average Cost of Capital (WACC) using current, real-world market data. All assumptionsincluding peer group selection, beta adjustments, capital structure targets, risk-free rate, market risk premium, and cost of debtmust be analytically justified and clearly documented.

The credibility of your recommendation depends directly on the rigor of your cost of capital estimation. Your analysis must demonstrate disciplined financial judgment and professional standards appropriate for a multi-million-dollar capital commitment.

Company Overview

Solara Energy is a mid-sized renewable energy operator with solar and wind assets across the southwestern United States. Our primary revenue model consists of long-term Power Purchase Agreements, supplemented by spot market electricity sales.

We are evaluating the construction of a new 100-acre solar farm in Arizona to fulfill a five-year PPA.

Project Parameters

• Equipment Investment: $28,000,000 (5-year MACRS depreciation)
• Land Opportunity Cost: $5,800,000 (after-tax realizable value)
• Salvage Value: 50% of equipment cost at end of Year 5
• Production Forecast (MWh): 70,000; 72,000; 75,000; 68,000; 65,000 PPA Commitment: 60,000 MWh annually at $85 per MWh
• Spot Market Price: $75 per MWh
• Variable Operating Cost: $20 per MWh
• Fixed Operating Cost: $2,500,000 per year
• Net Working Capital: 5% of annual revenue (invested one year prior and fully recovered at termination)
• Environmental Reclamation: $1,200,000 in Year 6
• Charitable Deduction from Land Donation: $4,000,000 in Year 7
• Corporate Tax Rate: 25%

Part I Cost of Capital Determination

You are required to estimate Solaras project-specific Weighted Average Cost of Capital.

Cost of Equity (CAPM Framework):
Re = Rf + L (Rm Rf)

Required steps:
Identify and justify a relevant peer group (For example: NextEra Energy, First Solar, Enphase Energy, Brookfield Renewable)
Obtain levered betas and capital structure data
Unlever each beta: U = L / [1 + (1 T)(D/E)]
Average unlevered betas
Relever to Solaras target capital structure (assume 40% debt unless justified otherwise)
Clearly document assumptions for risk-free rate and market risk premium

Cost of Debt:
Estimate project borrowing cost based on Treasury yield plus appropriate credit spread.
Compute after-tax cost of debt: Rd(1 T)

WACC Calculation:
WACC = (E/V)Re + (D/V)Rd(1 T)

Assess whether an 11% required return is reasonable relative to your estimate.

Part II Free Cash Flow Model Construction

Develop a detailed pro forma model including:
PPA and spot market revenues
Variable and fixed operating expenses
Depreciation (5-year MACRS)
Taxes and tax shields
Net working capital investment and recovery
Initial capital expenditures and land opportunity cost
After-tax salvage value
Reclamation costs and donation-related tax effects

The model must clearly separate operating and investment cash flows.

Part III Capital Budgeting Evaluation

Using your estimated WACC, compute:
Net Present Value (NPV)
Internal Rate of Return (IRR)
Profitability Index (PI)
Payback Period
Discounted Payback Period

Interpret results and reconcile any conflicts among decision metrics.

Part IV Sensitivity and Risk Analysis

Conduct sensitivity analysis on:
Electricity price (10%)
Variable cost ($5 per MWh)
Production volume (10%)
Discount rate (9%13%)

Present findings in summary tables. And include a tornado chart (optional) .

Required Deliverables

1. Excel Financial Model (audit-ready and clearly structured)
2. Executive Decision Memorandum (maximum one page) covering:
Base case valuation results
Interpretation of metrics
Risk and sensitivity findings
Strategic considerations
Clear investment recommendation

Submission Instructions:

1. Excel Worksheet: Submit a complete Excel file containing all quantitative analyses.

2. Decision Memo: Include your one-page Decision Memo in a text box on a separate worksheet within the same Excel file.

Attached Files (PDF/DOCX): ClassicGardensInstructions.docx, ClassicGardensSolutions.pdf, WilsonHome-solutions.pdf, WilsonHome.docx

Note: Content extraction from these files is restricted, please review them manually.

I provided the data and the problem set.

Using instructions on excel file please comple work and send it back in the same xlsx file!

Overview

Financial statement analysis helps us to gain insights into a companys operations. In this assignment, we will take a companys income and balance sheet data and develop a spreadsheet model that helps us to interpret this raw financial data. We will first generate financial ratios and comparative statements. Then we will prepare graphs to give us a pictorial overview of the companys situation. In the process, we will employ many basic concepts of spreadsheet design.

Data

Each of you has been assigned to a new company Your task is to analyze the companys financial statements to get a good understanding of the companys operation. You can find the data in the Financial Statement Data for project 3 module on They include the financial statements (both balance sheets and income statements) and the industry comparison report from Mergent Online.

What to do:

• Follow the Excel worksheet guideline provided below to prepare the accounting statements. You should use separate worksheets for balance sheet and income statement, and name each worksheet appropriately (i.e., HD_Income). Format the statements to enhance readability by using these formatting tools: number formats, border lines, shade (fill color), and indents. You must use at least three.
• Generate a common size balance sheet and a common size income statement on the same worksheet where each statement is.
• Make comments in the common-size income statement about the level and trend of the following items: COGS, Depreciation expenses, EBIT (operating income before interest expenses and taxes), and NI. Do the same in the common-size balance sheet about Cash, Inventory, Current Assets, Net Fixed Assets, Account Payable, Current Liabilities, Long- term Debt, and Common Stock.
• On a separate worksheet named Ratios, calculate the following ratios for each year. Refer to the note for the ratio definitions, i.e., Ch 7_8 note in the lecture note folder. Ratios in () refers to the corresponding ratios in Mergent Online, if they differ.1. Liquidity: Current ratio and Quick ratio.
  2. Asset management: Inventory Turnover, net fixed asset turnover (net PPEturnover), and total asset turnover ratio.
  3. Debt: Total debt to equity ratio (use this definition to compute, = (Current debt +long-term debt & leases ) / total equity), times interest earned (interest coverageratio), long-term Debt-to-Equity (LT debt to equity)
  4. Profitability: profit margin, BEP (operating margin), ROA, ROE.
• Find the industry averages for the ratios that you computed above, using Mergent Onlines comparison report. Be sure to control outliers when computing the averages.

When there is no industry average available, put NA in the industry column on the ratio

table.

• Comment on the calculated ratios 1) compared to the industry average (; 2) trend for theperiod. See an example attached.
• Provide a summary of your analysis of the companys performance by identifying itsweaknesses/strengths in no more than 2-3 sentences.
• Create separate comparative bar graphs for each of the four categories. Make sure to putall the ratios in a category together in a chart. Be sure to use the secondary axis for ratios that are very high/low compared to others in the category. You will have four charts. See a sample chart attached.
• Name the file with your last name, i.e., Moon_Excel 3.xlsx. Excel worksheet design:
  • Label clearly so that a user can see what are inputs and outputs.
  • Use at least four formatting tools (number formats, underlines, indent, and fillcolor) to enhance readability of the financial statements and your outputs.
  • In the output area (i.e., ratios computed), use only formulas with references to inputcells. Do not type in numbers in your formula!
  • Use different fonts and font sizes, as well as features such as bold and italicize, toenhance your worksheets appearance and readability.
  • Your graphs should have titles, and the series should have legends.
  • Appearance matters!Grading Rubrics Points Original and Common-sized statements and comments 8 Formats and appearance 4 Ratio analysis and interpretations 8 Industry Benchmark, outlier control 5 Ratio Graphs 3 Summary of analysis 2

Attached Files (PDF/DOCX): IP-Excel-ACP-3-solution.pdf

Note: Content extraction from these files is restricted, please review them manually.

Question 1: Lab Insight: To evaluate the collectability of accounts receivable, companies analyze how long the receivables have been outstanding. Why? The longer the receivables go unpaid, the less likely they will ever be collected. Aging receivables helps the companys accountant determine the appropriate bad debts expense to record, and thus, improves the estimate for the allowance for doubtful accounts valuation.

In this lab, we group accounts receivable into 30-day buckets so accountants can quickly focus on the oldest accounts outstanding.

Required:

1. Age the receivables in six 30-day buckets including 130 days, 3160 days, 6190 days, 91120 days, 121150 days, and 151180 days.
2. Provide a detailed list of the receivables from the 6190 days bucket.

Ask the Question: How can we age receivables?

Master the Data: Open Excel File Lab 6-1 Data.xlsx.

Note that the data lists 200 receivables that are all past their due date.

Software needed

• Excel/Tableau/Power BI
• Screen capture tool (Windows: Snipping Tool; Mac: Cmd+Shift+4)

Data:

Perform the Analysis: Age the receivables and put them into their appropriate aged buckets.

Upload one Word or PDF document containing all of your screenshots for this Lab using the button below.

1. Take a screenshot of your aged receivables, paste it into a Word document named “Alt Lab 6-1 SS.doc”, and label the screenshot Submission 1.
2. Take a screenshot of your detailed receivables in the 6190 days aging bucket, paste it into your Word document named “Alt Lab 6-1 SS.doc”, and label the screenshot Submission 2.

Question 2

Lab Insight: To evaluate the collectability of accounts receivable, companies analyze how long the receivables have been outstanding. Why? The longer the receivables go unpaid, the less likely they will ever be collected. Aging receivables helps the companys accountant determine the appropriate bad debts expense to record, and thus, improves the estimate for the allowance for doubtful accounts valuation.

In this lab, we group accounts receivable into 30-day buckets so accountants can quickly focus on the oldest accounts outstanding.

Required:

1. Age the receivables in six 30-day buckets including 130 days, 3160 days, 6190 days, 91120 days, 121150 days, and 151180 days.
2. Provide a detailed list of the receivables from the 6190 days bucket.

Ask the Question: How can we age receivables?

Master the Data: Open Excel File Lab 6-1 Data.xlsx.

Note that the data lists 200 receivables that are all past their due date.

Software needed

• Excel/Tableau/Power BI
• Screen capture tool (Windows: Snipping Tool; Mac: Cmd+Shift+4)

Data:

Perform the Analysis: Age the receivables and put them into their appropriate aged buckets.

Answer the following questions based the details provided.