Linking Subscriptions or Churn Rate Forecasts
- Advanced
Your plan financials are built around links
between tables. Users experienced with spreadsheets can easily link their
Add-in subscription forecast to their existing Sales Forecast table. This
will ensure that data on subscriptions and cancellations is reflected
in the plan financials.
Check Your Settings
- Set your table
settings for a value-based Sales
Forecast.
(Why?)The complexity of the subscription (churn rate) model requires that
you calculate subscriptions and cancellations in a units-based table.
However, the result of these calculations is reflected in the bottom row
of the Add-in table: Total
Sales. The easiest way to incorporate your add in table is to link
this single Total Sales row, cell for cell, to a data-entry Sales row
in an existing value-based Sales Forecast
table.
If you absolutely must use units-based forecasting to represent your
business situation, you may want to build your own churn rate forecasting
into your existing Sales Forecast
table, using the Add-in as an example.
Create the Link
- Open the Add-in Sales Forecast Subscription table that you have imported into your plan.
- Find cell C16, total month one sales. This is the cell to which you will link.
- Now open the standard Sales
Forecast table spreadsheet, and choose a Sales row which will display subscription forecast
sales data. (Show me...)
 |
|
| A
| B
|
| 3
| Sales
|
|
| 4
| Buttons and Bumper Stickers
| 0%
|
| Magazine Subscriptions | 0%
|
| 6
| Internet Sales
| 0%
|
| 7
| Total Sales
|
|
| 8
|
|
|
| 9
| Direct Cost of Sales
|
|
- Click on the first
monthly sales cell for this row, and type in the following formula to link this cell to the other table: ='Subscription Sales Forecast'!C16
- That cell in the Sales
Forecast table is now linked to the Subscription table (Show me...)
 |
|
| A
| B
|
| 3
| Sales
|
|
|
| 4
| Buttons and Bumper Stickers
| 0%
| $0
|
| Magazine Subscriptions
| 0%
| $1,995
|
| 6
| Internet Sales
| 0%
| $0
|
| 7
| Total Sales
|
| $1,995
|
| 8
|
|
|
|
| 9
| Direct Cost of Sales
|
| Jan
|
| 10
| Buttons
|
| $0
|
| 11
| Bumper Sticker
|
| $0
|
| 12
| Magazine Production and Mailing
|
| $0
|
| 13
| Internet Sales
|
| $0
|
| 14
| Subtotal Direct Cost of Sales
|
| $0
|
Fill in the monthly cells
- To fill in
the remaining months with links to their related cells in the Add-in table,
simply select all monthly cells in the chosen row in your main Sales
Forecast table, and click Fill Right on the Edit menu.
The formula will automatically look to the corresponding monthly cell
in the Add-in table. (Show me...)
 |
|
| A
| C
|
| 3
| Sales
|
|
|
| 4
| Buttons and Bumper Stickers
| $0
| $0
|
| Magazine Subscriptions
| $1,995
| $4,868
|
| 6
| Internet Sales
| $0
| $0
|
| 7
| Total Sales
| $1,995
| $4,868
|
Fill in the yearly cells
- To link sales
cells for yearly amounts past year one, repeat steps 1 through 5 (above)
for each yearly cell, linking to the corresponding yearly total for each
year in your plan. Year one will automatically calculate based on your
monthly projections.
TIP: Additional Premier-only
Option
Premier
Users who have chosen to include a second year of monthly data
can link, cell for cell, to second year monthly forecasts in the Add-in
subscription forecast. To see and adjust these cells, select column O
and column AD, and on the Format menu, point to Rows and click Unhide.
Finish your Sales Forecast table
- Finally,
add any cost of sales data relating to your subscription sales into a
row in your Sales Forecast table. (Show
me...)
 |
|
| A
| D
|
| 3
| Sales
|
|
|
| 4
| Buttons and Bumper Stickers
| $0
| $0
|
| 5
| Magazine Subscriptions
| $1,995
| $4,868
|
| 6
| Internet Sales
| $0
| $0
|
| 7
| Total Sales
| $1,995
| $4,868
|
| 8
|
|
|
|
| 9
| Direct Cost of Sales
| Jan
| Feb
|
| 10
| Buttons
| $0
| $0
|
| 11
| Bumper Sticker
| $0
| $0
|
| Magazine Production and Mailing
| $798
| $1,947
|
| 13
| Internet Sales
| $0
| $0
|
| 14
| Subtotal Direct Cost of Sales
| $798
| $1,947
|
Any changes you make in your Subscription Forecasting
table will now be automatically reflected in your Sales
Forecast table, and in all other tables using Sales data (Profit and Loss, Cash
Flow, Balance Sheet, and
Ratios).
Warning: If you use the Sales Forecast Wizard to adjust
your sales total for the subscription row later, your links to the Subscription
(Churn Rate) Model add-in table will be lost; data in those cells
will be converted to simple dollar values. However, using the Sales Forecast
Wizard for other rows in the Sales Forecast
table will not affect your links.
And don't forget to fill in the rest of
your Sales Forecast!
Related Topics