Versions Compared

Old Version 1

changes.mady.by.user Michelle Ng (Unlicensed)

Saved on

compared with

New Version 2

changes.mady.by.user Michelle Ng (Unlicensed)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Objective: To export a spreadsheet of all your subscriptions per product, group of products, or vendor and calculate price increases in Excel.

Note

This will not include cancellation records or upcoming potential subscriptions that aren’t Accepted.

Steps

  1. Go to the reports Module

  2. In the main menu, 📖 copy the report titled “Price Increases Report Template”

  3. In the “Edit the report” interface, you can further customize this report.

  4. Filter for the product / group of products / vendor in the left navigation sidebar

Expand
titleExpand for different ways to filter your report

Filter for a specific product

  1. Expand the PRODUCTS box in the left navigation side bar

  2. Type the whole or part of the product name in the search bar, e.g.

Image Added

  1. Hit the Enter key to apply the filter

Filter for a group of specific products

  1. Expand the list under “Name”

  2. Click on Show more

Image Added

  1. Look for and check off the products you want in the pop-up.

Image Added

  1. Click outside the box to close it

Filter for all products from a vendor

  1. Expand the PUBLISHERS box in the left navigation side bar

  2. Type the whole or part of the vendor name in the search bar, e.g.

Image Added

  1. Hit the Enter key to apply the filter

  1. Click the REFRESH button to preview

  2. Click SAVE REPORT & GENERATE

You should receive a spreadsheet in your email shortly.

In Excel

After downloading and opening your spreadsheet, you may want to filter for a subset of fiscal years.

Expand
titleIf you would like more guidance, expand this box for suggested steps.

  1. Select all cells with data

  2. Apply a filter

  3. Click on the grey square button in the “Start Date” column header

  4. In the drop down, click on the + sign expand the start and end years of your reporting period

Image Added

  1. Uncheck the months that fall outside the fiscal year. For example, if the reporting period is FY22/23 and FY 23/24, I will uncheck all months after March 2024.

Image Added

  1. Click Ok to close the drop down and see your filtered results.

Alternatively, to delete all unwanted records:

  1. Follow steps 1-3 above

  2. Click the box next to the year to unselect everything

  3. Expand the start and end years of your reporting period

  4. Check all the months that you don’t want

  5. Click ok

  6. Select all displayed rows and delete them

Image Added

  1. Clear the filter

Image Added

Only the subscription records that fall within your reporting period should remain.

Otherwise, the spreadsheet will contain all subscriptions going back to 2012, if applicable.

To calculate price increases:

Panel
panelIconIdatlassian-info
panelIcon:info:
bgColor#79E2F2

Some renewals may have been registered as “new” subscription type in CM due to a data migration issue. The “Price last year” field will be empty for these. In such cases, please:

  1. Refer to the previous row

  2. Manually copy and paste the “Sales price (Original currency)” value.

Expand
titleIf you would like more guidance, expand this box for suggested steps.

  1. Move the “Price last year” column to the right of the “Sales price (Original currency)” column

  2. Perform data cleaning, if necessary (see note above)

  3. Insert a new column to the right of “Price last year” and name it “Price Increases”

  4. Insert the following formula =H4/I4-1 in cell J4

    1. where column H is “Sales price (Original Currency)”

    2. where column I is “Price last year”

    3. where column J is “Price Increases”

  5. Drag the formula in J4 down tol fill the column

  6. Select the “Price Increases” column and apply the Percentage format to it