Plant Performance Data file is the source. This is updated on a daily
basis by the plant staff. The data from that file will be used to create
the 3 Invoices.



Other than this it is also required to create a dashboard containing

1. Year to date and month to date summary of the different charges in the invoice segregated by Charge category and Customer.

2. Also Year to date and month to date consumption of power by the different equipment and facilities.

3 Also Year to date and Month to date consumption by different equipment of the same type.



There are 3 companies.



BSF, CSF and TSF are the 3 companies. The BSF, TSF and CSF worksheets in
the Plant Performance data file will be your data source. (My team has
been trained to generate that data on a daily basis.)



Using this data you will have to generate

1. 3 Invoices for the 3 companies on a monthly basis. (I think this is
very simple as most of the charges are either fixed or calculated using
very simple formula. In my Mind this is how i see this working - There
will be an Invoice XL file you will create for me, either as a separate
file or as worksheets in the Plant Performance data XL file. They will
read data from the BSF, CSF and TSF worksheets to generate the invoices.
)

2. generate the dashboard on a daily basis. (the dashboard can be a
worksheet of the Plant Performance data XL Spreadsheet or a seperate
file.)



Please review Requirement Spec Worksheet of the attached file for the
Dashboard and Invoice Requirements and mapping to the associated data
fields. It Also Has the template for the dashboard.

The INV TSF/BSF/CSF Mar 19 worksheets have the actual invoice templates.

it is important that I keep the CSF, TSF and BSF worksheets as it gives
me access to the daily data for troubleshooting purposes.

Is it possible to have 3 diferrent invoice worksheets that will allow
for the selection of a year and month as input and based on that
selection, generate the invoice in that worksheet ?



In the Requirement Spec worksheet I have identified which worksheet and
which columns will give you the data required to calculate the diferrent
charges in the invoice. For example please scroll to row 41 of the
Requirement Spec worksheet. To Calculate 1.2 Towards Freezing charges
[removed] 18/- per slab use data from TSF!AG column. THis has been identified
in column E42 and highlighted in yellow. The fixed charges have been
highlighted in RED.



For the dashboard i need the consumption of Units and hours by current
week, current month and year to date for the identified Metrics,



In the Week column I have identified from which column and in which
worksheet you can get the source data from. For example IN Column C6 of
the Requirements Worksheet I have identified that you can get the data
for Hours Consumed by KC 21(Flake Ice) for TSF from TSF worksheet K47
Column.. You will see that there is no data for KC 21(Flake Ice) Under
CSF and BSF which means that CSF and BSF do not have flake ice machines.
Hence no calculations are required for those companies for KC 21(Flake
Ice). Now you have to use the data from TSF worksheet K47 Column to
calculate how many hours the KC21 has run in the current week, how many
hours KC21 has run in the current month and how many hours KS 21 has run
for the current year. This is the same calculation that is required for
all the Metrics. One year is from April of the current year to march of
the next year.



At this point I dont need graphs. Once i get this data correctly as a next step i will go towards graps and other analytics.