Automation - Link Input Sheet to an Original Data Sheet

[vivek.budhram]

Hi All,

I hope you all are well,

Please assist me with the following project:

Basically I am looking for help to link an input sheet to the original data sheet, (Automation)

1. The original sheet being the forecast "F1 2019"(with monthly updated actual figures) and budget "B19" (stays the same throughout the budget period of 5 year plan) - Based on Revenue model only. (Customers - "subscribers) (Packages offered and at the standard price and scenario based price)

2. Scenario input sheet (where changes in the data will be made e.g. growth and price), changing the original sheet and bringing back the changes in a summary on the input sheet

The Current process:
A copy of the original Forecast is used to add various Scenarios and assumptions like changing the price (Discount) or growth (add or subtract customers on assumption of future or past events e.g Fifa world cup).
Scenario:
1. Price (Subscription Prices) e.g. Premium. We assume that keeping the price stable for the first 2 years (2019 and 2020) and then applying an increase per after until 2023
2. Growth e.g. Marketers will identify that there will be either a decease or increase in customers either cancelling and registering for a subscription – The “premium” package

The architecture I require are as follows:
1. An input sheet (Project_Scenarios) which links to the copy of the original forecast (Subs revenue - Plan - 5 Year R809 Waive Access Fee(Test))
2. “Pricing” Tab needs a search function (B2) to find the copy of the original forecast from the hard-drive or server and link to the sheet for changes that need to be made. A change in pricing as per the scenario (C10, E10, G10, I10, K10) must be able to change the pricing on the forecast tab “Prices & Growth” (AL15, AN15, AP15, AR15, AT15). This will in turn, recalculate as per the formula already set by the end user.

Figure1 – Project Scenario, Pricing Tab.PNG
Figure1 – Project Scenario, Pricing Tab

Figure2 – Subs revenue - Plan - 5 Year R809 Waive Access Fee(Test), Prices & Growth Tab.PNG
Figure2 – Subs revenue - Plan - 5 Year R809 Waive Access Fee(Test), Prices & Growth Tab

3. The above must be done for the “Growth” Tab, where the customer or subscriber number will change as per the input sheet. The year must be taken into consideration, as we would only change a certain period (Month, Year).

The “Growth” Tab must link to the copy of Scenario Subscriber growth forecast Sheet, make scenario or assumption based changes (e.g. C15, D15, E15, F15, G15), therefore link the changes to the copy of Scenario Subscriber growth forecast Sheet, the factors (%) included in the sheet will formulate the monthly figures for the year e.g. April 2018-March 2019. And then in turn change the figures on the forecast tab “Scenario R809” and then recalculate figures as per the formulas already embedded in the sheet. Bare in mind, The ” R809 @ R809 Growth” tab is the original base to compare changes.

Figure3 - Project Scenario, Growth Tab.PNG
Figure3 - Project Scenario, Growth Tab

Figure4 - R809 Waive Access Fee Subscriber growth forecast(Test), FY19 S4 Tab.PNG
Figure4 - R809 Waive Access Fee Subscriber growth forecast(Test), FY19 S4 Tab

Figure5 - Subs revenue - Plan - 5 Year R809 Waive Access Fee(Test), Scenario R809 Tab.PNG
Figure5 - Subs revenue - Plan - 5 Year R809 Waive Access Fee(Test), Scenario R809 Tab

4. Summary Tab on the input sheet (Project_Scenario) must link to the changes on the copy of the original forecast (Subs revenue - Plan - 5 Year R809 Waive Access Fee(Test)) on the 1 Pager” tab
Let’s start from the top and we can edit and make changes as we go.

I appreciate your time and effort in advance

Thank you
Regards
Vivek

[vivek.budhram]

I have uploaded the Project_scenario Spreadsheet

[vivek.budhram]

I have uploaded the R809 Waive Access Fee Subscriber growth forecast(Test)Spreadsheet

[vivek.budhram]

I Will upload a copy of the original Forecast later today, Having issues uploading the file

Thank you

[vivek.budhram]

I have uploaded the Subs revenue - Plan - 5 Year R809 Waive Access Fee(Test) (Part 1)

I had to split the original file into 5 parts, due to size

[vivek.budhram]

I have uploaded the Subs revenue - Plan - 5 Year R809 Waive Access Fee(Test) (Part 2)

[vivek.budhram]

I have uploaded the Subs revenue - Plan - 5 Year R809 Waive Access Fee(Test) (Part 3)

[vivek.budhram]

I have uploaded the Subs revenue - Plan - 5 Year R809 Waive Access Fee(Test) (Part 4)

[vivek.budhram]

I have uploaded the Subs revenue - Plan - 5 Year R809 Waive Access Fee(Test) (Part 5)

[vivek.budhram]

At the end of the Subs revenue - Plan - 5 Year R809 Waive Access Fee(Test) (Part 1-5) file

It should then be consolidated as follows to read the fileAttachment 586369

[vivek.budhram]

Hi Guys,

Just following up whether anyone can assist with automation,

Basically, the input sheet must be able to link to the original forecast, make a copy based in the scenario, changes made on the input sheet must automatically make changes on the copy of the original, pulling the results on the summary sheets as well as the 1 Pager

The original sheet was split out due to size,
But once it is consolidated as per the image attached
Subs revenue - Plan - 5 Year R809 Waive Access Fee(Test).PNG

Thank you
Regards
Vivek