Part $1$: The SolarTech Manufacturing Acquisition Proposal

Spreadsheet Assignment Instructions: Create a spreadsheet that calculates pro forma financials, free cash flows, and the net present value of the acquisition of BrightFuture Solar by Solar'Tech. The spreadsheet will be presented by Emma's supervisor to the board of directors, and Emma will not be present at the meeting. The spreadsheet must include all the necessary inputs, be formula$-$based, and appropriately use cell referencing. The spreadsheet should be dynamic in the sense that a user can easily see the effects of changing inputs. For example, if any inputs change, all the cash flow and NPV calculations will automatically update. $($Note: As part of grading your spreadsheet, I will change some of the assumptions and verify that the NPV changes appropriately, which will be the case if your spreadsheet is formula$-$based.$)$

Upload your finished spreadsheet to Canvas before $1$:$00$ PM$,$ April $4,2025.$ You must upload an Excel or Google Sheets file $($not a link, not a pdf$).$ You can work alone or in groups of up to four people $($no groups over four people$).$ If you work in a group, you should all plan to work on the second part of the case together also, which will be done on the date posted in Canvas.

Grading Rubric for Part $1$: Comprehensive Spreadsheet Analysis

$\backslash$table$[[$Criteria$,$Points,Description$],[\backslash$table$[[$Visual$],[$Presentation$]],20,\backslash$table$[[$Your spreadsheet is organized, easy to follow, and visually clear.$],[$Sections are well$-$labeled, and the formatting is consistent $($e$.$g$.,],[$fonts$,$ colors, headers$).$ The overall appearance is professional.$]]],[,10,\backslash$table$[[$The layout is somewhat cluttered, hard to navigate, or$],[$inconsistent$.$ The spreadsheet could be improved for readability.$]]],[\backslash$table$[[$Dynamic$],[$Structure$]],30,\backslash$table$[[$Project is submitted as an editable spreadsheet. Cell references$],[$and formulas are used for all calculations.$]]],[,0,\backslash$table$[[$Project is submitted as a pdf$,$ word document, or link. If submitted$],[$as a spreadsheet, the project is static, meaning cell references$],[$are missing or incomplete, calculations are done manually, and$],[$formulas are missing.$]]],[\backslash$table$[[$Numerical$],[$Accuracy$]],50,\backslash$table$[[$The NPV calculation is correct, and all financial components are$],[$accurately calculated based on the assumptions provided.$]]],[,45,$Annual cash flows $($CFFA$)$ are correct, but NPV is incorrect.$],[,40,$OCF and EBIT are correct, but errors in CFFA and NPV$.],[,0-30,\backslash$table$[[$Incorrect EBIT, OCF, CFFA, and NPV$.$ Partial credit awarded for$],[$correct numbers in the Pro Forma.$]]]]$Email $1$

To: Emma Brooks, Financial Analyst

From: Robert Lawson, CFO, SolarTech

Date: March $26,2025$

Subject: BrightFuture Solar Proposal

Hi Emma,

I hope you're doing well. I wanted to touch base regarding the analysis of the proposed acquisition of BrightFuture Solar for $$2\mathrm{.}5$ million. As discussed, we need to make an informed decision about acquiring their solar panel manufacturing assets, and I$\text{'}$m counting on you to put together all the numbers concisely. Create a spreadsheet with the projected revenue, costs, and cash flows on this acquisition for the next five years, and calculate the Net Present Value $($NPV$)$ of the acquisition, using a $15\%$ discount rate.

Once you've compiled all the data, please submit your spreadsheet with the NPV calculation clearly presented. I will be sharing the spreadsheet with the Board, so it should be well$-$organized, easily traceable, and include all the necessary assumptions $($sales projections, operating expenses, depreciation, etc.$).$ The deadline for this deliverable is $1$ PM$,$ Friday, April $4,2025.$

I've copied key department leads below, as they will provide you with the necessary assumptions for your model. Be sure to reach out to them for any further details or clarifications:

Sales Projections $($Kelly Mitchell, VP of Sales$)$

Operating Expenses and Working Capital Assumptions $($Mark Davis, Director of Operations$)$

Tax and Depreciation Information $($Angela Carr, Senior Tax Manager$)$

I look forward to seeing your work on this$-$please let me know if you have any questions or need any help as you progress.

Best regards,

Robert Lawson

Chief Financial Officer, SolarTechEmail $2$

To: Emma Brooks, Financial Analyst

From: Kelly Mitchell, VP of Sales, SolarTech

Date: March $31,2025$

Subject: Sales Projections for BrightFuture Solar Acquisition

Hi Emma,

As part of the financial model for the BrightFuture Solar acquisition, you'll need to incorporate the sales projections for the first few years after the acquisition. BrightFuture Solar's expected sales in Year $1($post$-$acquisition$)$ are estimated at $$1\mathrm{.}25$ million. We anticipate a $10\%$ growth in sales each year for the first five years, based on their current product offerings and market demand.

This sales forecast is critical to the model, so please use it as the starting point for your revenue projections. If you ne