clear
capture log close
set more off
set type double
* calculate credits according to MacNeil rate, using extrapolated estimates
*
log using "$pathlog\11_MacNeil_Credit_Analysis.log", replace
* 38,876 at end of 2019 - Lee Extrapolation uses percent of capacity so using 2019 customers estimates exports annually if capacity was unchanged from the end of 2019
local totcustall = 38876
* Uses Lee extrap of total exports and assumes totcustall is number of customers
import excel using "$pathback\25_Compiled_Exports_Production_maxed.xlsx", clear firstrow
* Generate Peak Hours based on MacNeil definitions
gen peakmacneil=hour>=16 & hour<20 & month>=6 & month<=9
replace peakmacneil=1 if ((hour>=7 & hour<9) | (hour>=18 & hour<20)) & (month>=10 | month<=5)
* Export credits from MacNeil
gen peak=peakmacneil
do "$pathdomac\11a_MacNeil Credit Rates.do"
gen macneilcredit=credit
* Exports are in Kw but credit is in $ per megawatt so need to divide by 1,000
gen totalcredit=macneilcredit*export/1000
* Calculate per customer credit average
gen macneilcrpercust=totalcredit/(`totcustall')
gen credit_kw = credit/1000
gen peak_credit = credit_kw if peakmacneil==1
gen offpeak_credit = credit_kw if peakmacneil==0
* compare to $160 application fee and $150 generation meter fee per customer proposed by Davis, p. 27, line 389.
****************************************
**********Sensitivity Analysis**********
****************************************
* All peak times
replace peak=1
drop credit
quietly do "$pathdomac\11a_MacNeil Credit Rates.do"
gen crallpeakpercust=credit*export/((`totcustall')*1000)
* All non-peak times
replace peak=0
drop credit
quietly do "$pathdomac\11a_MacNeil Credit Rates.do"
gen crallnonpeak=credit*export/((`totcustall')*1000)
save "$pathoutmac\leeextrapresults.dta", replace
* Using Lee Extrapolation Results, show credits under Schedule 137 proposal
use "$pathoutmac\leeextrapresults.dta", clear
* show figures using Lee extrap results
* macneil, 136 current rate, macneil assuming all is at peak, macneil assuming all is at nonpeak
* Lee Extrapolation
table month, c(sum macneilcrpercust sum totalcredit sum crallpeakpercust sum crallnonpeak sum expo) format(%12.0fc) row
****************************************
*************Report Tables**************
****************************************
* Table 2
putexcel set "$pathexcel/Exhibit 5-AJL.xlsx", sheet("Table 2") modify
*local row = 4
gen avg_exp_cust = export_amount/`totcustall'
preserve
collapse (sum) export_amount avg_exp_cust macneilcrpercust,by(month)
export excel month export_amount avg_exp_cust macneilcrpercust using "$pathexcel\Exhibit 5-AJL.xlsx",sheetmodify sheet("Table 2")cell(A2),
putexcel set "$pathexcel\Exhibit 5-AJL.xlsx",sheet("Table 2") modify
putexcel A1="Month", bold
putexcel B1="Estimated Total Exports", bold
putexcel C1="Average Exports per Customer", bold
putexcel D1="Credits per Customer Under Proposed Schedule 137",bold
restore
* Table 3
putexcel set "$pathexcel/Exhibit 5-AJL.xlsx", sheet("Table 3") modify
local row = 4
egen sum_macneilcrpercust = sum(macneilcrpercust)
quietly sum sum_macneilcrpercust
local sum_macneil = r(mean)
quietly putexcel C`row'=("Average Export Credits per year")
quietly putexcel D`row'=matrix(r(mean))
local row = `row' + 1
* Cost in Initial Fees (MacNeil Direct, line 389)
gen cost_initialfees = 310
quietly sum cost_initialfees
local cost = r(mean)
quietly putexcel C`row'=("Cost in Initial Fees")
quietly putexcel D`row'=matrix(r(mean))
local row = `row' + 1
* Number of Years Before Total Export Credits Exceed Proposed RMP First Year Fees
gen credit_years = `cost'/`sum_macneil'
quietly sum credit_years
local years = r(mean)
quietly putexcel C`row'=("Number of Years Before Total Export Credits Exceed Proposed RMP First Year Fees")
quietly putexcel D`row'=matrix(r(mean))
local row = `row' + 1
* Average Kilowatt Hours Provided to RMP Prior to a Customer Covering Proposed First Year Fees using Export Credits
quietly sum avg_exp_cust
local sum_avg_exp = r(sum)
gen kw_hours = `sum_avg_exp' * `years'
quietly sum kw_hours
local hours = r(mean)
quietly putexcel C`row'=("Average Kilowatt Hours Provided to RMP Prior to a Customer Covering Proposed First Year Fees using Export Credits")
quietly putexcel D`row'=matrix(r(mean))
local row = `row' + 1
* Amount of revenue RMP earns from selling these exports, per Customer (at current Schedule 136 residential retail rate of $.092 per kilowatt
gen rev = `hours' * 0.092
quietly sum rev
quietly putexcel C`row'=("Amount of revenue RMP earns from selling these exports, per Customer")
quietly putexcel D`row'=matrix(r(mean))
* Table 4
putexcel set "$pathexcel/Exhibit 5-AJL.xlsx", sheet("Table 4") modify
*local row = 4
preserve
collapse (sum) avg_exp_cust crallnonpeak crallpeakpercust (mean) offpeak_credit peak_credit ,by(month)
export excel month avg_exp_cust offpeak_credit crallnonpeak peak_credit crallpeakpercust using "$pathexcel\Exhibit 5-AJL.xlsx",sheetmodify sheet("Table 4")cell(A2),
putexcel set "$pathexcel\Exhibit 5-AJL.xlsx",sheet("Table 4") modify
putexcel A1="Month", bold
putexcel B1="Average Exports per Customer", bold
putexcel C1="Proposed Schedule 137 Off-Peak", bold
putexcel D1="Sensitivity 1:Average Credits per Customer if All Time were Off-Peak Time",bold
putexcel E1="MacNeil Proposed Schedule 137 Peak",bold
putexcel F1="Sensitivity 2:Average Credits per Customer if All Time were Peak Time",bold
restore
/*xtable month, c(sum mean sum mean sum ) row noput
quietly putexcel D`row'=matrix(r(xtable))*/
log close