junte/Model
1
0
Fork 0
mirror of https://github.com/Hestia-Homes/Model.git synced 2026-07-12 13:29:04 +00:00
Code Issues Projects Releases Packages Wiki Activity Actions 1
Model/etl/eligibility/ha_15_32/WFT Sales data analysis.py

665 lines
32 KiB
Python
Raw Permalink Blame History

import numpy as np
import pandas as pd
ECO4_NEW_RATES = 1710
GBIS_NEW_RATES = 600
def app():
# Load in the excel
nov_ha_data = pd.read_excel(
'etl/eligibility/ha_15_32/ALL HA FIGURES AND ASSIGNED INSTALLERS 21.11.2023 with sales data.xlsx',
)
# Drop rows where HA name is null
nov_ha_data = nov_ha_data.dropna(subset=["HA Name"])
nov_ha_data["ha_number"] = nov_ha_data["HA Name"].str.extract(r"(\d+)").astype(int)
nov_ha_data = nov_ha_data.sort_values("ha_number", ascending=True)
variance_explanations = pd.read_excel(
'etl/eligibility/ha_15_32/ALL HA FIGURES AND ASSIGNED INSTALLERS 21.11.2023 with sales data.xlsx',
sheet_name="Variance explanations"
)
september_figures = pd.read_excel(
"etl/eligibility/ha_15_32/ALL HA FIGURES AND ASSIGNED INSTALLERS SEP 23 UPDATE (2).xlsx",
sheet_name="HA Stats"
)
historical_invoices = pd.read_excel(
"etl/eligibility/ha_15_32/ALL HA FIGURES AND ASSIGNED INSTALLERS 21.11.2023 with sales data.xlsx",
sheet_name="Jul 22 to Oct 23"
)
# Drop rows where installer rates is null
historical_invoices = historical_invoices[~pd.isnull(historical_invoices["INSTALLER RATES"])]
historical_invoices = historical_invoices[historical_invoices["INSTALLER RATES"] != "NA "]
# By Scheme, take a weighted mean of the INSTALLER RATES, weighted on the number of rows
n_invoices = historical_invoices.groupby(["Scheme", "INSTALLER RATES"])["Invoice number"].count().reset_index()
n_invoices = n_invoices[n_invoices["Scheme"].isin(["Eco 4", "GBIS"])]
historical_scheme_rates = n_invoices.groupby("Scheme").apply(
lambda x: np.average(x["INSTALLER RATES"], weights=x["Invoice number"])
).reset_index().rename(columns={0: "Historical rates"})
# we take just entries sales data that have sales > 0
sales_data = nov_ha_data[nov_ha_data["Sales"] > 0]
# We now need to adjust sales data depending on the variance explanations
sales_data = sales_data.merge(
variance_explanations[["HA", 'Which figure is correct']],
how="left",
left_on="ha_number",
right_on="HA"
)
def adjust_sales(row):
if pd.isnull(row["Which figure is correct"]):
return row["Sales"]
if row["Which figure is correct"] == "HA facts & figures":
return row['No. of Tech surveys complete']
if row["Which figure is correct"] == "Billed amount":
return row["Sales"]
if row["Which figure is correct"] in ["Both correct, HA facts and figures includes November", "Both correct"]:
return row["Sales"]
raise ValueError(f"Unknown value for 'Which figure is correct': {row['Which figure is correct']}")
# We now need to adjust sales data depending on the variance explanations
sales_data["adjusted_sales"] = sales_data.apply(lambda row: adjust_sales(row), axis=1)
# We therefore adjust GBIS and ECO4 sales data based on adjusted sales
sales_data["adjusted_eco4_sales"] = sales_data["No. of Tech surveys complete - Eco 4"] / sales_data["Sales"] * \
sales_data["adjusted_sales"]
sales_data["adjusted_gbis_sales"] = sales_data["No. of Tech surveys complete - GBIS"] / sales_data["Sales"] * \
sales_data["adjusted_sales"]
sales_data["cancellation_rate"] = (sales_data["Sales"] - sales_data["adjusted_sales"]) / sales_data["Sales"]
# The difference between the adjusted sales and the actual sales is the cancellation
cancellations = (sales_data["adjusted_sales"].sum() - sales_data["Sales"].sum()) / sales_data["Sales"].sum()
# Given the cancellations, we can now adjust the expected remaining surveys
sales_data["No. of Tech surveys remaining"] = sales_data["No. of Tech surveys remaining"] * (
1 - sales_data["cancellation_rate"]
)
# We now merge on the expected values for September
sales_data = sales_data.merge(
september_figures[["Redacted HA", "ECO4", "GBIS"]].rename(
columns={"Redacted HA": "HA Name", "ECO4": "Sept Expected ECO4", "GBIS": "Sept Expected GBIS"}
),
how="left",
on="HA Name",
)
sales_data["Sept Expected ECO4"] = sales_data["Sept Expected ECO4"].fillna(0)
sales_data["Sept Expected GBIS"] = sales_data["Sept Expected GBIS"].fillna(0)
# We calculate the ECO4 and GBIS conversion rates with the adjusted numbers
sales_data["ECO4 Conversion"] = sales_data["adjusted_eco4_sales"] / sales_data["adjusted_sales"]
sales_data["GBIS Conversion"] = sales_data["adjusted_gbis_sales"] / sales_data["adjusted_sales"]
# We now calculate the expected remaining ECO4 and GBIS sales
# We take the number of remaining surveys and multiply by the conversion rate for each scheme, which tells us
# how many more we should expect to see
sales_data["Expected Remaining ECO4"] = sales_data["No. of Tech surveys remaining"] * sales_data["ECO4 Conversion"]
sales_data["Expected Remaining GBIS"] = sales_data["No. of Tech surveys remaining"] * sales_data["GBIS Conversion"]
# We now produce a forecasted ECO4 and GBIS sales figure
sales_data["Forecasted ECO4 Sales"] = sales_data["adjusted_eco4_sales"] + sales_data["Expected Remaining ECO4"]
sales_data["Forecasted GBIS Sales"] = sales_data["adjusted_gbis_sales"] + sales_data["Expected Remaining GBIS"]
# Take the columns we're interestd in
# HA # Properties Sept ECO4 Figures Sept GBIS Figures Nov Total Sales Nov ECO4 Sales Nov GBIS Sales
# Remaining Surveys ECO4 conversion GBIS conversion Forecasted ECO4 Sales Forecasted GBIS sales ECO4 Change
# GBIS Change
sales_data_formatted = sales_data[[
"HA Name",
"ASSET LIST no.",
"Sept Expected ECO4",
"Sept Expected GBIS",
"adjusted_sales",
"adjusted_eco4_sales",
"adjusted_gbis_sales",
"No. of Tech surveys remaining",
"ECO4 Conversion",
"GBIS Conversion",
"Forecasted ECO4 Sales",
"Forecasted GBIS Sales"
]].rename(
columns={
"adjusted_sales": "Oct Total Sales (adjusted for variance)",
"adjusted_eco4_sales": "Oct ECO4 Sales (adjusted for variance)",
"adjusted_gbis_sales": "Oct GBIS Sales (adjusted for variance)",
"No. of Tech surveys remaining": "Remaining Surveys",
}
)
# Convert columns which should be integers to integers
for col in ["ASSET LIST no.", "Remaining Surveys", "Sept Expected ECO4", "Sept Expected GBIS",
"Oct Total Sales (adjusted for variance)", "Oct ECO4 Sales (adjusted for variance)",
"Oct GBIS Sales (adjusted for variance)", "Forecasted ECO4 Sales", "Forecasted GBIS Sales"]:
sales_data_formatted[col] = sales_data_formatted[col].fillna(0)
sales_data_formatted[col] = sales_data_formatted[col].astype(int)
# Remove HA 17 because this was EPCs only. We also remove HA33 because they do not have access to the full portfolio
sales_data_formatted = sales_data_formatted[
~sales_data_formatted["HA Name"].isin(["HA 17", "HA 33"])
]
# September expected ECO4 and GBIS
sept_expected_eco4 = sales_data_formatted["Sept Expected ECO4"].sum()
sept_expected_gbis = sales_data_formatted["Sept Expected GBIS"].sum()
# Completed so far
oct_eco4_sales = sales_data_formatted["Oct ECO4 Sales (adjusted for variance)"].sum()
oct_gbis_sales = sales_data_formatted["Oct GBIS Sales (adjusted for variance)"].sum()
# Forecasted figures
forecasted_eco4_sales = sales_data_formatted["Forecasted ECO4 Sales"].sum()
forecasted_gbis_sales = sales_data_formatted["Forecasted GBIS Sales"].sum()
# Expected remaining sales
expected_remaining_eco4_sales = forecasted_eco4_sales - oct_eco4_sales
expected_remaining_gbis_sales = forecasted_gbis_sales - oct_gbis_sales
# Forecast change vs September
forecasted_eco4_change = 100 * (forecasted_eco4_sales - sept_expected_eco4) / sept_expected_eco4
forecasted_gbis_change = 100 * (forecasted_gbis_sales - sept_expected_gbis) / sept_expected_gbis
aggregates = pd.DataFrame(
columns=["Scheme", "Sept Expected", "Oct Completed", "Forecasted Remaining Sales", "Forecasted Total Sales",
"Forecasted Change vs Sept"],
data=[
["ECO4", sept_expected_eco4, oct_eco4_sales, expected_remaining_eco4_sales, forecasted_eco4_sales,
forecasted_eco4_change],
["GBIS", sept_expected_gbis, oct_gbis_sales, expected_remaining_gbis_sales, forecasted_gbis_sales,
forecasted_gbis_change],
]
)
# Multiply by histoical rates to get revenue
# For ECO4, this is ~£1456 and for GBIS it's ~£432
historical_gbis_price = historical_scheme_rates[
historical_scheme_rates["Scheme"] == "GBIS"
]["Historical rates"].iloc[0]
historical_eco4_price = historical_scheme_rates[
historical_scheme_rates["Scheme"] == "Eco 4"
]["Historical rates"].iloc[0]
aggregates["Sept Expected Revenue"] = np.where(
aggregates["Scheme"] == "ECO4",
aggregates["Sept Expected"] * historical_eco4_price,
aggregates["Sept Expected"] * historical_gbis_price
)
aggregates["Completed Revenue"] = np.where(
aggregates["Scheme"] == "ECO4",
aggregates["Oct Completed"] * historical_eco4_price,
aggregates["Oct Completed"] * historical_gbis_price
)
# We use the new rates for the forecasted revenue
aggregates["Forecasted Remaining Revenue"] = np.where(
aggregates["Scheme"] == "ECO4",
aggregates["Forecasted Remaining Sales"] * ECO4_NEW_RATES,
aggregates["Forecasted Remaining Sales"] * GBIS_NEW_RATES
)
# We also calculate the forecasted remaining revenue at the original price
aggregates["Forecasted Remaining Revenue (original price)"] = np.where(
aggregates["Scheme"] == "ECO4",
aggregates["Forecasted Remaining Sales"] * historical_eco4_price,
aggregates["Forecasted Remaining Sales"] * historical_gbis_price
)
aggregates["Forecasted Revenue"] = aggregates["Completed Revenue"] + aggregates["Forecasted Remaining Revenue"]
# Forecasted revenue with original price
aggregates["Forecasted Revenue (original price)"] = (
aggregates["Completed Revenue"] + aggregates["Forecasted Remaining Revenue (original price)"]
)
# Create a totals row which sums up the two rows
forecasted_change_vs_sept = 100 * (
aggregates["Forecasted Total Sales"].sum() - aggregates["Sept Expected"].sum()
) / aggregates["Sept Expected"].sum()
aggregates = pd.concat(
[
aggregates,
pd.DataFrame(
[
["Total", aggregates["Sept Expected"].sum(), aggregates["Oct Completed"].sum(),
aggregates["Forecasted Remaining Sales"].sum(), aggregates["Forecasted Total Sales"].sum(),
forecasted_change_vs_sept,
aggregates["Sept Expected Revenue"].sum(), aggregates["Completed Revenue"].sum(),
aggregates["Forecasted Remaining Revenue"].sum(),
aggregates["Forecasted Remaining Revenue (original price)"].sum(),
aggregates["Forecasted Revenue"].sum(),
aggregates["Forecasted Revenue (original price)"].sum(),
]
],
columns=aggregates.columns
)
]
)
# For each property in the asset list, we now calculate an average conversion rate to ECO4 and GBIS
# We do this by taking the forecasted sales values for each schemes and dividing by the number of properties
number_properties = sales_data_formatted["ASSET LIST no."].sum()
eco4_conversion_rate = forecasted_eco4_sales / number_properties
gbis_conversion_rate = forecasted_gbis_sales / number_properties
# We also attribute a future value per property
future_eco4_value = ECO4_NEW_RATES * eco4_conversion_rate
future_gbis_value = GBIS_NEW_RATES * gbis_conversion_rate
# We also calulate a revenue figure for the old rates
historical_eco4_value = historical_eco4_price * eco4_conversion_rate
historical_gbis_value = historical_gbis_price * gbis_conversion_rate
# For the HAs that have not begun selling, we estimate the value of the projects
# We start with some problem HAs
# HA 7, HA 24, HA 25
# These HAs have no sales data, so we use the expected figures
problem_has_data = nov_ha_data[
(nov_ha_data["HA Name"].isin(["HA 7", "HA 24", "HA 25"]))
].copy()
# Merge on the september expected figures
problem_has_data = problem_has_data.merge(
september_figures[["Redacted HA", "ECO4", "GBIS"]].rename(
columns={"Redacted HA": "HA Name", "ECO4": "Sept Expected ECO4", "GBIS": "Sept Expected GBIS"}
),
how="left",
on="HA Name",
)
# Fill NAs
problem_has_data["Sept Expected ECO4"] = problem_has_data["Sept Expected ECO4"].fillna(0)
problem_has_data["Sept Expected GBIS"] = problem_has_data["Sept Expected GBIS"].fillna(0)
# We now calculate the expected ECO4 and GBIS sales based on the average conversion rates
problem_has_data["Expected ECO4 Sales"] = problem_has_data["ASSET LIST no."] * eco4_conversion_rate
problem_has_data["Expected GBIS Sales"] = problem_has_data["ASSET LIST no."] * gbis_conversion_rate
# Filter just on columns we're interested in
problem_has_data = problem_has_data[[
"HA Name",
"ASSET LIST no.",
"Sept Expected ECO4",
"Sept Expected GBIS",
"ECO4",
"GBIS",
"Expected ECO4 Sales",
"Expected GBIS Sales"
]].rename(
columns={
"ECO4": "Nov Expected ECO4",
"GBIS": "Nov Expected GBIS",
}
)
# Fill NAs
problem_has_data["Nov Expected ECO4"] = problem_has_data["Nov Expected ECO4"].fillna(0)
problem_has_data["Nov Expected GBIS"] = problem_has_data["Nov Expected GBIS"].fillna(0)
# We calculate HA level Sept, Nov expected revenue, based on historical rates and then forecasted revenue
problem_has_data["Sept Expected ECO4 Value"] = problem_has_data["Sept Expected ECO4"] * historical_eco4_price
problem_has_data["Sept Expected GBIS Value"] = problem_has_data["Sept Expected GBIS"] * historical_gbis_price
problem_has_data["Nov Expected ECO4 Value"] = problem_has_data["Nov Expected ECO4"] * historical_eco4_price
problem_has_data["Nov Expected GBIS Value"] = problem_has_data["Nov Expected GBIS"] * historical_gbis_price
problem_has_data["Forecasted ECO4 Revenue"] = problem_has_data["ASSET LIST no."] * future_eco4_value
problem_has_data["Forecasted GBIS Revenue"] = problem_has_data["ASSET LIST no."] * future_gbis_value
# Totals
problem_has_data["Sept Expected Total Value"] = problem_has_data["Sept Expected ECO4 Value"] + \
problem_has_data["Sept Expected GBIS Value"]
problem_has_data["Nov Expected Total Value"] = problem_has_data["Nov Expected ECO4 Value"] + \
problem_has_data["Nov Expected GBIS Value"]
problem_has_data["Forecasted Total Revenue"] = problem_has_data["Forecasted ECO4 Revenue"] + \
problem_has_data["Forecasted GBIS Revenue"]
# We calculate a total expected value for September, November and then forecasted
problem_has_expected_eco4_value = problem_has_data["Sept Expected ECO4"].sum() * historical_eco4_price
problem_has_expected_gbis_value = problem_has_data["Sept Expected GBIS"].sum() * historical_gbis_price
problem_has_expected_total_value = problem_has_expected_eco4_value + problem_has_expected_gbis_value
problem_has_nov_eco4_value = problem_has_data["Nov Expected ECO4"].sum() * historical_eco4_price
problem_has_nov_gbis_value = problem_has_data["Nov Expected GBIS"].sum() * historical_gbis_price
problem_has_nov_total_value = problem_has_nov_eco4_value + problem_has_nov_gbis_value
forecasted_eco4_value = problem_has_data["ASSET LIST no."].sum() * future_eco4_value
forecasted_gbis_value = problem_has_data["ASSET LIST no."].sum() * future_gbis_value
problem_has_forecasted_total_value = forecasted_eco4_value + forecasted_gbis_value
problem_has_summary = pd.DataFrame(
columns=["Scheme", "Sept Expected", "Nov Expected", "Forecasted"],
data=[
["ECO4", problem_has_expected_eco4_value, problem_has_nov_eco4_value, forecasted_eco4_value],
["GBIS", problem_has_expected_gbis_value, problem_has_nov_gbis_value, forecasted_gbis_value],
["Total", problem_has_expected_total_value, problem_has_nov_total_value, problem_has_forecasted_total_value]
]
)
# We now also estimate the value of the remaining HAs based on historical sales performance and new rates
# We take the has that are not in the sales data
remaining_has = nov_ha_data[
~nov_ha_data["HA Name"].isin(sales_data_formatted["HA Name"])
].copy()
# Merge on the september expected figures
remaining_has = remaining_has.merge(
september_figures[["Redacted HA", "ECO4", "GBIS"]].rename(
columns={"Redacted HA": "HA Name", "ECO4": "Sept Expected ECO4", "GBIS": "Sept Expected GBIS"}
),
how="left",
on="HA Name",
)
# We update the asset list size for HA 33, because they do not have access to the full portfolio
remaining_has.loc[remaining_has["HA Name"] == "HA 33", "ASSET LIST no."] = 20699
# We also remove HA 17
remaining_has = remaining_has[~remaining_has["HA Name"].isin(["HA 17"])]
# We now calculate the expected ECO4 and GBIS sales based on the average conversion rates
remaining_has["Expected ECO4 Sales"] = remaining_has["ASSET LIST no."] * eco4_conversion_rate
remaining_has["Expected GBIS Sales"] = remaining_has["ASSET LIST no."] * gbis_conversion_rate
# Filter just on columns we're interested in
remaining_has = remaining_has[[
"HA Name",
"ASSET LIST no.",
"Sept Expected ECO4",
"Sept Expected GBIS",
"ECO4",
"GBIS",
]].rename(
columns={
"ECO4": "Nov Expected ECO4",
"GBIS": "Nov Expected GBIS",
}
)
remaining_has = remaining_has.fillna(0)
# We take just HAs that had an initial september expectation for ECO4 or GBIS, or that now have a Nov expectation
remaining_has = remaining_has[
(remaining_has["Sept Expected ECO4"] > 0) | (remaining_has["Sept Expected GBIS"] > 0) |
(remaining_has["Nov Expected ECO4"] > 0) | (remaining_has["Nov Expected GBIS"] > 0)
]
# Expected sales based on asset list size and conversion rate
remaining_has["Forecasted Sales ECO4"] = remaining_has["ASSET LIST no."] * eco4_conversion_rate
remaining_has["Forecasted Sales GBIS"] = remaining_has["ASSET LIST no."] * gbis_conversion_rate
# Calculat the total expected value for September and November
remaining_has["Sept Expected ECO4 Value"] = remaining_has["Sept Expected ECO4"] * historical_eco4_price
remaining_has["Sept Expected GBIS Value"] = remaining_has["Sept Expected GBIS"] * historical_gbis_price
remaining_has["Nov Expected ECO4 Value"] = remaining_has["Nov Expected ECO4"] * historical_eco4_price
remaining_has["Nov Expected GBIS Value"] = remaining_has["Nov Expected GBIS"] * historical_gbis_price
# Calculate forecasted revenue
remaining_has["Forecasted ECO4 Revenue"] = remaining_has["ASSET LIST no."] * future_eco4_value
remaining_has["Forecasted GBIS Revenue"] = remaining_has["ASSET LIST no."] * future_gbis_value
# We also calculate forecasted revenue with the original price
remaining_has["Forecasted ECO4 Revenue (original price)"] = remaining_has["ASSET LIST no."] * historical_eco4_value
remaining_has["Forecasted GBIS Revenue (original price)"] = remaining_has["ASSET LIST no."] * historical_gbis_value
# Calculate totals for each scheme
remaining_has_september_eco4_sales = remaining_has["Sept Expected ECO4"].sum()
remaining_has_september_gbis_sales = remaining_has["Sept Expected GBIS"].sum()
remaining_has_november_eco4_sales = remaining_has["Nov Expected ECO4"].sum()
remaining_has_november_gbis_sales = remaining_has["Nov Expected GBIS"].sum()
remaining_has_forecasted_eco4_sales = remaining_has["Forecasted Sales ECO4"].sum()
remaining_has_forecasted_gbis_sales = remaining_has["Forecasted Sales GBIS"].sum()
remaining_has_september_eco4_value = remaining_has["Sept Expected ECO4 Value"].sum()
remaining_has_september_gbis_value = remaining_has["Sept Expected GBIS Value"].sum()
remaining_has_november_eco4_value = remaining_has["Nov Expected ECO4 Value"].sum()
remaining_has_november_gbis_value = remaining_has["Nov Expected GBIS Value"].sum()
remaining_has_forecasted_eco4_value = remaining_has["Forecasted ECO4 Revenue"].sum()
remaining_has_forecasted_gbis_value = remaining_has["Forecasted GBIS Revenue"].sum()
remaining_has_forecasted_eco4_value_original_price = remaining_has["Forecasted ECO4 Revenue (original price)"].sum()
remaining_has_forecasted_gbis_value_original_price = remaining_has["Forecasted GBIS Revenue (original price)"].sum()
# Calculate the change in forecasted sales against the September expected sales
remaining_has_foecast_change_eco4 = 100 * (
remaining_has["Forecasted Sales ECO4"].sum() - remaining_has["Sept Expected ECO4"].sum()
) / remaining_has["Sept Expected ECO4"].sum()
remaining_has_foecast_change_gbis = 100 * (
remaining_has["Forecasted Sales GBIS"].sum() - remaining_has["Sept Expected GBIS"].sum()
) / remaining_has["Sept Expected GBIS"].sum()
# Total change
remaining_has_foecast_change_total = 100 * (
remaining_has["Forecasted Sales ECO4"].sum() + remaining_has["Forecasted Sales GBIS"].sum() -
remaining_has["Sept Expected ECO4"].sum() - remaining_has["Sept Expected GBIS"].sum()
) / (remaining_has["Sept Expected ECO4"].sum() + remaining_has["Sept Expected GBIS"].sum())
asset_list_size = remaining_has["ASSET LIST no."].sum()
# Create a summary table of the rest with the totals for ECO4, GBIS and then a total row
remaining_has_aggregate = pd.DataFrame(
columns=["Scheme", "Asset List Size", "Sept Expected Sales", "Nov Expected Sales", "Forecasted Sales",
"Forecasted Change vs Sept",
"Sept Expected Value", "Nov Expected Value", "Forecasted Value", "Forecasted Value (original price)"],
data=[
[
"ECO4", asset_list_size, remaining_has_september_eco4_sales, remaining_has_november_eco4_sales,
remaining_has_forecasted_eco4_sales, remaining_has_foecast_change_eco4,
remaining_has_september_eco4_value,
remaining_has_november_eco4_value, remaining_has_forecasted_eco4_value,
remaining_has_forecasted_eco4_value_original_price
],
[
"GBIS", asset_list_size, remaining_has_september_gbis_sales, remaining_has_november_gbis_sales,
remaining_has_forecasted_gbis_sales, remaining_has_foecast_change_gbis,
remaining_has_september_gbis_value,
remaining_has_november_gbis_value, remaining_has_forecasted_gbis_value,
remaining_has_forecasted_gbis_value_original_price
],
[
"Total", asset_list_size, remaining_has_september_eco4_sales + remaining_has_september_gbis_sales,
remaining_has_november_eco4_sales + remaining_has_november_gbis_sales,
remaining_has_forecasted_eco4_sales + remaining_has_forecasted_gbis_sales,
remaining_has_foecast_change_total,
remaining_has_september_eco4_value + remaining_has_september_gbis_value,
remaining_has_november_eco4_value + remaining_has_november_gbis_value,
remaining_has_forecasted_eco4_value + remaining_has_forecasted_gbis_value,
remaining_has_forecasted_eco4_value_original_price +
remaining_has_forecasted_gbis_value_original_price
]
]
)
# Calculate pipeline value
pipeline_value = aggregates[["Scheme", "Completed Revenue", "Forecasted Remaining Revenue"]].merge(
remaining_has_aggregate[["Scheme", "Forecasted Value"]].rename(
columns={"Forecasted Value": "Forecasted Revenue, Unconfirmed HAs"}
), how="inner", on="Scheme"
)
# Calculate the total
pipeline_value["Total Value"] = (
pipeline_value["Completed Revenue"] + pipeline_value["Forecasted Remaining Revenue"] + pipeline_value[
"Forecasted Revenue, Unconfirmed HAs"]
)
# TODO: Insert model figures
model_results = pd.DataFrame(
[
{
# This one, we don't have sales data
"HA Name": "HA 15",
"Model Expected Additional ECO4 (unit level)": None,
"Model Expected Total ECO4 (unit level)": 296,
"Model Expected Additional GBIS (unit level)": None,
"Model Expected Total GBIS (unit level)": 209,
},
{
"HA Name": "HA 16",
# Old before re-run
# "Model Expected Additional ECO4 (unit level)": 418,
# "Model Expected Total ECO4 (unit level)": 1820,
# "Model Expected Additional GBIS (unit level)": 576,
# "Model Expected Total GBIS (unit level)": 612,
# IN the partial sales data, WFT have completed 1407 ECO4, 36 GBIS
"Model Expected Additional ECO4 (unit level)": 411 + 342 + 235,
"Model Expected Total ECO4 (unit level)": 1407 + 411 + 342 + 235,
"Model Expected Additional GBIS (unit level)": 223,
"Model Expected Total GBIS (unit level)": 36 + 223,
},
{
"HA Name": "HA 24",
"Model Expected Additional ECO4 (unit level)": 224,
"Model Expected Total ECO4 (unit level)": 848,
"Model Expected Additional GBIS (unit level)": 552,
"Model Expected Total GBIS (unit level)": 552,
},
{
"HA Name": "HA 25",
"Model Expected Additional ECO4 (unit level)": None,
"Model Expected Total ECO4 (unit level)": 1709 + 59,
"Model Expected Additional GBIS (unit level)": None,
"Model Expected Total GBIS (unit level)": 2004 + 107,
}
]
)
sales_data_formatted["Remaining ECO4 Sales"] = (
sales_data_formatted["Forecasted ECO4 Sales"] - sales_data_formatted["Oct ECO4 Sales (adjusted for variance)"]
)
sales_data_formatted["Remaining GBIS Sales"] = (
sales_data_formatted["Forecasted GBIS Sales"] - sales_data_formatted["Oct GBIS Sales (adjusted for variance)"]
)
sales_data_formatted["Completed ECO4 Revenue"] = (sales_data_formatted[
"Oct ECO4 Sales (adjusted for variance)"] *
historical_eco4_price)
sales_data_formatted["Completed GBIS Revenue"] = (sales_data_formatted[
"Oct GBIS Sales (adjusted for variance)"] *
historical_gbis_price)
ha_subset_with_sales = ["HA 15", "HA 16", "HA 24"]
has_subset_with_sales_value = sales_data_formatted[
sales_data_formatted["HA Name"].isin(ha_subset_with_sales)
].copy()[
[
"HA Name",
"Oct ECO4 Sales (adjusted for variance)",
"Oct GBIS Sales (adjusted for variance)",
"Remaining ECO4 Sales",
"Remaining GBIS Sales",
"Forecasted ECO4 Sales",
"Forecasted GBIS Sales",
"Completed ECO4 Revenue",
"Completed GBIS Revenue"
]
]
has_subset_with_sales_value["Remaining ECO4 Revenue"] = has_subset_with_sales_value[
"Remaining ECO4 Sales"] * ECO4_NEW_RATES
has_subset_with_sales_value["Remaining GBIS Revenue"] = has_subset_with_sales_value[
"Remaining GBIS Sales"] * GBIS_NEW_RATES
has_subset_with_sales_value["Remaining Total Revenue"] = (
has_subset_with_sales_value["Remaining ECO4 Revenue"] + has_subset_with_sales_value["Remaining GBIS Revenue"]
)
model_results["Model Expected Additional ECO4 Revenue"] = (
model_results["Model Expected Additional ECO4 (unit level)"] * ECO4_NEW_RATES
)
model_results["Model Expected Additional GBIS revenue"] = (
model_results["Model Expected Additional GBIS (unit level)"] * GBIS_NEW_RATES
)
model_results["Model Expected Additional Total Revenue"] = (
model_results["Model Expected Additional ECO4 Revenue"] + model_results[
"Model Expected Additional GBIS revenue"]
)
# Show more columns with pandas
pd.set_option('display.max_rows', 500)
pd.set_option('display.max_columns', 500)
pd.set_option('display.width', 1000)
# Look at HA 16
ha16_model = model_results[model_results["HA Name"] == "HA 16"]
has_subset_with_sales_value[has_subset_with_sales_value["HA Name"] == "HA 16"]
# WFT: For HA 16: 4,598,190 ECO4, 57,000 GBIS
# Model:
# Look at HA 24
ha24_model = model_results[model_results["HA Name"] == "HA 24"]
has_subset_with_sales_value[has_subset_with_sales_value["HA Name"] == "HA 24"]
# Look at HA 15
ha15_data = has_subset_with_sales_value[has_subset_with_sales_value["HA Name"] == "HA 15"]
ha15_portfolio_value = ha15_data["Completed ECO4 Revenue"] + ha15_data[
"Completed GBIS Revenue"] + ha15_data["Remaining Total Revenue"]
# # This doesn't have sales data so in the model analysis, we just value the ha as a whole
ha15_model = model_results[model_results["HA Name"] == "HA 15"]
ha15_value = ha15_model["Model Expected Total ECO4 (unit level)"].iloc[0] * ECO4_NEW_RATES + \
ha15_model["Model Expected Total GBIS (unit level)"].iloc[0] * GBIS_NEW_RATES
model_results["Expected ECO4 Revenue"] = model_results["Model Expected Total ECO4 (unit level)"] * ECO4_NEW_RATES
model_results["Expected GBIS Revenue"] = model_results["Model Expected Total GBIS (unit level)"] * GBIS_NEW_RATES
model_results["Expected Total Revenue"] = model_results["Expected ECO4 Revenue"] + model_results[
"Expected GBIS Revenue"]
model_results[model_results["HA Name"].isin(["HA 15"])]
# We now create a final excel with all of the data
# We want:
# 1) aggregates
# 2) sales_data_formatted
# 3) remaining_has_aggregate
# 4) remaining_has
# 5) problem_has_summary
# Function to get the maximum column width
def get_col_widths(dataframe):
# First we find the maximum length of the index column
idx_max = max([len(str(s)) for s in dataframe.index.values] + [len(str(dataframe.index.name))])
# Then, we concatenate this to the max of the lengths of column name and its max value for each column, row-wise
return [idx_max] + [max(dataframe[col].astype(str).map(len).max(), len(col)) for col in dataframe.columns]
# Create a Pandas Excel writer using XlsxWriter as the engine
with pd.ExcelWriter('HA Pipeline Analysis.xlsx', engine='xlsxwriter') as writer:
# Write each dataframe to a different worksheet without the index
for df, sheet in [(aggregates, 'Forecasted Sales'),
(sales_data_formatted, 'Sales Data'),
(remaining_has_aggregate, 'Remaining HAs Value'),
(remaining_has, 'Remaining HAs data'),
(pipeline_value, 'Pipeline Value'),
(problem_has_summary, 'Problem HAs Analysis'),
(problem_has_data, 'Problem HAs Data')
]:
df.to_excel(writer, sheet_name=sheet, index=False)
# Auto-adjust columns' width
for i, width in enumerate(get_col_widths(df)):
writer.sheets[sheet].set_column(i, i, width)
Reference in a new issue View git blame Copy permalink