Skip to content

Fix Cash Flow Send to Excel with Equations#1044

Merged
cpaulgilman merged 21 commits into
patchfrom
cf_excel_all_models
May 23, 2022
Merged

Fix Cash Flow Send to Excel with Equations#1044
cpaulgilman merged 21 commits into
patchfrom
cf_excel_all_models

Conversation

@cpaulgilman

@cpaulgilman cpaulgilman commented May 18, 2022 •
edited
Loading

Copy link
Copy Markdown
Collaborator

Goes with NatLabRockies/ssc#818

Fixes #952

Applies fixes completed for Single Owner in PR #936 to other FOM financial models.

Abbreviations:

  • SO = Single Owner
  • CS = Community Solar
  • MP = Merchant Plant
  • LPF = Leveraged Partnership Flip
  • AEPF = All Equity Partnership Flip
  • TPO = Third Party Ownership - Host/Developer

UI changes

Hide DSCR debt with size limit for all financial models except SO.

Show PBI available for debt service option on Financial Cash Incentives form for CS, MP, TPO. (Was only available for SO and LPF.)

New FOM Fixes for This PR

SLB: Add salvage value to investor federal tax basis calculation.

LPF: Remove receivables reserve from debt calculation to match SSC.

TPO: Receivables reserve funding fix for final year

MP: Rename named rages for revenue source to SSC variable names

Item SO CS MP LPF/AEPF TPO SLB
Change "levelized cost" in metrics table to LCOE. x x x x x x
Reference to adjusted DSCR in metrics table. x x x x no n/a
Add debt fraction to metrics table x x x x no n/a
Fix font size of energy section in cash flow. x x x x x x
Fix property tax net assessed value. x x x x x x
Debt sizing fix receivables reserve in Year zero in cash flow. x x x x x n/a
Make iterative size of debt for debt fraction option less confusing. x x x x x n/a
Sculpted debt with debt fraction limit. x x x x x n/a
Fix cash flow from investing activities total for out years to exclude total purchase of property row. x x x x x x
Fix shading and borders. x x x x x x
Change number formatting for PPA price, PTC and other $/kWh values from one to three decimal places so values are visible. x x x x x x
Revenue annual values. n/a x n/a x x x
Remove PPA revenue lost to self consumption. x n/a x x n/a x
Rename Excel ranges to SSC variable names. x x x x x x
Use LCOS energy discharge in place of battery annual discharge. x x x x x x
Clean up debt repayment Excel formulas. x x x x x n/a
Add custom depreciation to Excel. n/a n/a n/a x n/a n/a
Fix receivables reserve ending balance in Year 1. x x x x x x
Receivables reserve basis is all revenue excluding PBI and salvage value, not only PPA revenue. x x x x x x
Add fuel cell cf_thermal_value to revenue. x x x x x x
Use gen without storage for O&M production cost if greater than zero. x x x x x

Notes:

  • Sculpted debt with size limit is only available for single owner now, but all spreadsheets support it in case we enable it for other financial models in the future.

  • Capacity and curtailment payment only available for SO, CS now, but all spreadsheets support it for future. Revenue for MP is unique.

  • Spreadsheet calculation for sculpted debt with size limit is different from SAM: Spreadsheet hits debt fraction limit exactly, SAM approximates it.

  • TPO does not include debt metrics in metrics table, so skipping those.

  • CS only available with PVWatts, but doing fuel cell and battery energy outputs here in preparation for enabling CS for other techs in the future.

Replace Excel range names with SSC variable names

These range names were useful when we did more calculations of annual values in Excel. Now that we are exporting the annual results from SAM, there is no need for the Excel named range to be different than the SSC variable name for these variables:

Named Range SSC Variable
AnnualPPAPrice cf_ppa_price
GrossRevenue cf_energy_value
AnnualCapacityRevenue cf_capacity_payment
AnnualCurtailmentRevenue cf_curtailment_value
AnnualEnergyMarketRevenue cf_energy_market_revenue
AnnualAncillaryService1Revenue cf_ancillary_services_1_revenue
AnnualAncillaryService2Revenue cf_ancillary_services_2_revenue
AnnualAncillaryService3Revenue cf_ancillary_services_3_revenue
AnnualAncillaryService4Revenue cf_ancillary_services_4_revenue
AnnualCurtailmentRevenue cf_curtailment_value
AnnualCapacityRevenue cf_capacity_payment
EnergyMarket mp_enable_energy_market_revenue
AncillaryService1 mp_enable_ancserv1
AncillaryService2 mp_enable_ancserv2
AncillaryService3 mp_enable_ancserv3
AncillaryService4 mp_enable_ancserv4
IRRTargetYear flip_target_year

Also, for Residential/Commercial, change annual energy and savings range names to SSC variable names.

FOM Fixes From PR #936

Transfer initial fixes in SO to other financial models.

Item CS MP LPF/AEPF SLB TPO
Energy terms as annual values, and for battery fuel cell. Update references for revenue and annual energy (electricity to grid) in NPV section. x x x x x
Change EnergyToGrid named range to cf_energy_sales and EnergyFromGrid to cf_energy_purchases. x x x x x
Operating costs for fuel cell, battery. x x x x x
Remove fuel costs calculations from Inputs sheet and replace with annual values. x x x x x
Add/fix LCOS cash flow equations in Excel. x x x x x
Land lease. x x x x x

@cpaulgilman cpaulgilman added this to the 2021.12.02 Patch 2 milestone May 18, 2022
@cpaulgilman cpaulgilman self-assigned this May 18, 2022
@cpaulgilman cpaulgilman mentioned this pull request May 18, 2022
Merged
brtietz
brtietz requested changes May 18, 2022
View reviewed changes

@brtietz brtietz left a comment

Copy link
Copy Markdown
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Great work completing a massive undertaking. I left a few comments on the code. I had issues with Excel export for CashLoan:

image

@sjanzou and @mjprilliman I focused on testing detailed pv, detailed pv-battery, and PVWatts. If you can test other technologies that would be really helpful.

Comment thread deploy/runtime/cashflow.lk Outdated
Comment thread deploy/runtime/cashflow.lk Outdated
Comment thread deploy/runtime/cashflow.lk Outdated
@cpaulgilman
Address Brian's comments
ecd5f12 
* Remove redundant check for Host Developer to display cf_utility_bill

* Fix missing parentheses from is_fom_fin().

* Fix logic in set_annual_values to not export cf_ppa_price for BTM models.
@cpaulgilman

cpaulgilman commented May 18, 2022

Copy link
Copy Markdown
Collaborator Author

@brtietz Thanks Brian! I fixed the issues you found.

@brtietz brtietz self-requested a review May 18, 2022 19:39
brtietz
brtietz approved these changes May 18, 2022
View reviewed changes

@brtietz brtietz left a comment

Copy link
Copy Markdown
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Looks great!

sjanzou
sjanzou requested changes May 19, 2022
View reviewed changes

@sjanzou sjanzou left a comment

Copy link
Copy Markdown
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Great work!

A few details

  1. Should we put the DSCR limit in the other debt models? e.g. LPF

image

  1. should the results be DSCR (Excel) or minimum DSCR (SAM)?

image

  1. Should the results table in SAM for the Merchant Plant include the (minimum) DSCR?

image

  1. (Change requested) Some of the host developer values are not very close:

image

@cpaulgilman
Fixes for Steve PR review
057bde9 
Fix Host Developer ppa_revenue, ppa_price

Change label in Excel to "Minimum debt service coverage ratio" (add "Minimum").

Fix Excel working capital reserve funding and check all other workbooks.
@cpaulgilman

cpaulgilman commented May 19, 2022

Copy link
Copy Markdown
Collaborator Author

Thanks @sjanzou.

Should we put the DSCR limit in the other debt models? e.g. LPF

Not yet. We added this to Single Owner in SAM 2021.12.02 for a special request. I would like to see if we get any feedback on it before making it available to the other models because for projects with high revenue to cost ratio, users may think the debt size is based on CAFDS when it really isn't. I did add the calculation to the spreadsheets for all models, so if we do decide to add it, it should only require a change to SSC and the SAM UI.

Should the results be DSCR (Excel) or minimum DSCR (SAM)?

I made label change to the spreadsheets -- they should all now be "Minimum debt service coverage ratio" to be consistent with "Minimum DSCR" in SAM metrics table.

Should the results table in SAM for the Merchant Plant include the (minimum) DSCR?

The metrics table shows either debt fraction or minimum DSCR conditionally based on the input mode for all PPA models with debt and Merchant Plant. I think that behavior is fine. If we want to change it, I would do it as a separate issue.

(Change requested) Some of the host developer values are not very close:

Oops! Thanks. I fixed cf_ppa_revenue and cf_ppa_value in cashflow.lk, and also fixed working capital reserve funding in Excel, so results should be identical now for the default PVWatts / Host Developer case.

@mjprilliman

mjprilliman commented May 19, 2022

Copy link
Copy Markdown
Collaborator

image

Are these results in the acceptable range? This is for standalone battery.

@cpaulgilman

cpaulgilman commented May 20, 2022

Copy link
Copy Markdown
Collaborator Author

@mjprilliman It looks like "Developer operating cash post recovery" is missing from the Excel workbook, and might be causing that discrepancy. I also see LCOS-related outputs aren't being exported correctly from SAM. I'll address those issues.

@cpaulgilman
Partnership flip and LCOS fixes
8fb47e4 
Fix partnership flip tax investor operating cash distributions double-counting pre/post flip.

Fix LCOS send annual values for all FOM configs with battery.
@cpaulgilman

cpaulgilman commented May 20, 2022

Copy link
Copy Markdown
Collaborator Author

@mjprilliman I fixed the partnership flip issue: There was a long standing mistake in the partner returns equations in Excel. Spreadsheet results for both partnership flip with and without debt should be much closer to SAM now.

I also fixed LCOS export to Excel so you should see LCOS calculations on cash flow tab in Excel for all FOM configurations with battery.

@cpaulgilman cpaulgilman requested a review from sjanzou May 20, 2022 16:38
sjanzou
sjanzou approved these changes May 21, 2022
View reviewed changes

@sjanzou sjanzou left a comment

Copy link
Copy Markdown
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Looks good with a few notes:

  1. Fuel cell SO - IRR in Excel does not match - that is the IRR function in Excel

image

  1. ETES Single Owner - the NPV between SAM and Excel differs by a factor of 3

image

  1. Confirming that there is no LCOS for the ETES/SO configuration:

image

  1. Standalone battery merchant plant - do we want to do anything about the debt percentage? (Note, they match between Excel and SAM so maybe another issue?)

image

@cpaulgilman

cpaulgilman commented May 23, 2022

Copy link
Copy Markdown
Collaborator Author

Thanks @sjanzou for the additional review.

  1. Fuel cell SO - IRR in Excel does not match - that is the IRR function in Excel

Oops, this is an issue with the SO spreadsheet for all technologies. I fixed that error in the IRR formula: It needs to be updated by hand anytime a row is added or removed above the IRR calculation rows on the cash flow sheet.

  1. ETES Single Owner - the NPV between SAM and Excel differs by a factor of 3.

This is because of the DSCR input option with the maximum debt fraction. The input in SAM is set to 60%, but SAM calculates the debt size for an equivalent debt fraction of 59.08%. The spreadsheet DSCR calculation uses 60%. If you set the maximum debt fraction input in Excel to SAM's output value of 59.08, then the NPV in SAM and Excel is much closer. NPV is very sensitive to debt size in this case.

  1. Confirming that there is no LCOS for the ETES/SO configuration:

The LCOS is not implemented in SSC for ETES, so it doesn't get exported to Excel. The spreadsheets are ready for that implementation: When we update the ETES model in SSC to generate the LCOS cash flow outputs cf_annual_cost_lcos and cf_annual_discharge_lcos, the cash flow script should export those to Excel.

  1. Standalone battery merchant plant - do we want to do anything about the debt percentage? (Note, they match between Excel and SAM so maybe another issue?)

This is because of the Standalone Battery / Merchant Plant default configuration. It has no revenue, so the debt sizing calculations break down and none of the metrics make sense. This should be addressed as part of a review of defaults before we release Patch 2.

@cpaulgilman cpaulgilman merged commit bb616ae into patch May 23, 2022
@cpaulgilman cpaulgilman deleted the cf_excel_all_models branch May 23, 2022 18:47
@cpaulgilman cpaulgilman added the added to release notes PR and/or issue has been added to release notes for a public release label Jun 2, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@brtietz brtietz brtietz approved these changes

@sjanzou sjanzou sjanzou approved these changes

@mjprilliman mjprilliman Awaiting requested review from mjprilliman

Assignees

@cpaulgilman cpaulgilman

Labels

added to release notes PR and/or issue has been added to release notes for a public release

Projects

None yet

Milestone

2021.12.02 Patch 2

Development

Successfully merging this pull request may close these issues.

Update Cash Flow Send-to-Excel with Equations for non-Single Owner

4 participants

@cpaulgilman @mjprilliman @brtietz @sjanzou