Analysis of data with instructions for changing data monthly
- or -
Post a project like this1442
$250
- Posted:
- Proposals: 38
- Remote
- #2809063
- OPPORTUNITY
- PRE-FUNDED
- Completed
IT related services (data entry, virtual Assistant, Graphic designing WordPress development etc )
Lahore
288970840083303638871367435437548543761852389967039063603914341397160739929534112769
Description
Experience Level: Expert
Attached is an excel file that is redacted. What I'm looking for.
1) Setup of an excel file that as pivot tables (multiple) that will auto update when new data (this file as example) is loaded into the worksheet
2) Pivot tables needed (with filters for plant and Shp plant)
a) forecast product name by unit, with total volume, sorted by volume (largest to smallest)
b) forecast product name with volume sorted by largest to smallest with calculation that ranks the product on volume
c) intermediate product with total volume sorted by largest to smallest, with additional calculations on average monthly volume and standard deviation of the monthly volume, and a calculation on whether the volume is moving up, staying the same or moving down over time.
d) additionally the intermediate products need to be ranked based on volume
e) FYI the column % is the percentage of the intermediate that goes into product name. it normally will sum to 1 for each product, but there maybe examples that don't, have a flag that highlights in some way that the % doesn't sum to 1.
f) A pivot table that has the product name, and the intermediates (with %), with total volume sorted largest to smallest.
g) If there is a way to be able to designate a certain ranking range of a intermediate that it could auto designate a value for the product name. This is confusing to explain. For example, let's say that the top 10 (user adjustable) intermediate products have a value of 1, the intermediate products ranked 11-20 have a value of 10, and the intermediate products ranked 21 - x (end) have a value of 100. I want to be able to say that if a product name has a value of the sum of those intermediate prod numbers.
So there will be several pivot tables with the above data. I want to be able to copy and paste new data into a "raw data" worksheet and then have the pivot tables update based on the new data. Some minor manipulation of the cell ranges or refresh data, etc., is acceptable.
A short text document (with screenshots) that explains the steps to complete to refresh the data is needed also.
For this test, filter the products by those that column N (Shp Plt) is only Plant 2D and Plant 2X. Columns J and G should only include those that begin with Product or Intermediate.
I expect that there would be a couple reviews (edits).
1) Setup of an excel file that as pivot tables (multiple) that will auto update when new data (this file as example) is loaded into the worksheet
2) Pivot tables needed (with filters for plant and Shp plant)
a) forecast product name by unit, with total volume, sorted by volume (largest to smallest)
b) forecast product name with volume sorted by largest to smallest with calculation that ranks the product on volume
c) intermediate product with total volume sorted by largest to smallest, with additional calculations on average monthly volume and standard deviation of the monthly volume, and a calculation on whether the volume is moving up, staying the same or moving down over time.
d) additionally the intermediate products need to be ranked based on volume
e) FYI the column % is the percentage of the intermediate that goes into product name. it normally will sum to 1 for each product, but there maybe examples that don't, have a flag that highlights in some way that the % doesn't sum to 1.
f) A pivot table that has the product name, and the intermediates (with %), with total volume sorted largest to smallest.
g) If there is a way to be able to designate a certain ranking range of a intermediate that it could auto designate a value for the product name. This is confusing to explain. For example, let's say that the top 10 (user adjustable) intermediate products have a value of 1, the intermediate products ranked 11-20 have a value of 10, and the intermediate products ranked 21 - x (end) have a value of 100. I want to be able to say that if a product name has a value of the sum of those intermediate prod numbers.
So there will be several pivot tables with the above data. I want to be able to copy and paste new data into a "raw data" worksheet and then have the pivot tables update based on the new data. Some minor manipulation of the cell ranges or refresh data, etc., is acceptable.
A short text document (with screenshots) that explains the steps to complete to refresh the data is needed also.
For this test, filter the products by those that column N (Shp Plt) is only Plant 2D and Plant 2X. Columns J and G should only include those that begin with Product or Intermediate.
I expect that there would be a couple reviews (edits).
Scott G.
100% (15)Projects Completed
9
Freelancers worked with
6
Projects awarded
67%
Last project
10 Oct 2022
United States
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
Hello! Is Excel is a fix requirement for this project? Can you imagine to use a database solution to get the things done?
913835
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies