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