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