May 2022 Tip of the Month: Budgeting via Line Item Schedules

#1

Budgeting with Prophix Line Item Schedules is easy and powerful. Organizations can be more organized, flexible, and detailed in their budgeting, which makes it easier to analyze the budget information.

The Line Item Schedule feature in Prophix enables you to enter account data at a more detailed level based on multiple lines of transactions that populate the account total amount. A common use case for Line Item Schedules is when an organization would like to do subledger planning. Each transaction line represents a different expense or revenue item that affects the total value of the account. Expenses such as office supplies, marketing expenses, or travel expenses can be broken down into multiple categories.

This article explains how to populate an account using a Line Item Schedule.

We will assume that travel expenses are to be populated exclusively through a Line Item Schedule so that we can break down expenses into more cost segments for in-depth analysis.

Step #1: Model Manager - Account Dimension Set-up

  • Go to Account Dimension in Model Manager, and locate the Travel expenses accounts
  • Navigate to the Calculation Definition section on the right-hand side
  • Choose the option that Requires line item detail for data entry under the Calculation Method

Note: Apply the same steps for all leaf-level input accounts that require the same actions.

image
image.png975×491 75.5 KB

Step #2: Data Entry - Line Item Schedule

The next step in Ad-hoc Analysis is to use what we selected in Step 1. To populate the travel accounts, I created a simple data view with data entry enabled.

  1. Open the data view and enable the Data Entry Mode
  2. Upon entering data, you will be prompted with a message saying, “This account can only be updated using Line Item Schedules.” as supporting detail
  3. By clicking Ok, you’ll open the Line Item Schedule screen and be able to populate the account with information
  4. Complete the supporting details in the account as needed. Once the data entry is complete click Save. Upon returning to the data view you will find the account populated accordingly

image
image.png975×262 77.2 KB

image

image
image.png962×208 63.8 KB

Step #3: View Line Items Schedule Report - Supporting Details

After populating the target expense account via Line Item Schedule, you can view all accounts that have Line Item support details and use the information in reporting. To do that follow the below steps:

On the data view toolbar menu click on the Support Details option

3 options will appear, click View Report , and the Line Item Detail report will be generated to view online, or you can download the report to an Excel file for offline use

image
image.png975×245 70.9 KB

image
image.png975×269 60.4 KB

Hopefully, this helps.

Good Luck!

1 Like
#2

Very interesting. I think this is something we would want to use but haven’t. Does the check box ‘Requires line item detail’ affect anything from our integration perspective? Also, are there similar data entry capabilities in template studio?

1 Like
#3

Thanks, Josh, I’m glad it was helpful to you.

Checking that box makes it mandatory to populate the desired accounts only via the Line Item schedule. Yes, if you have other processes that update the same account via the Import process, then that does affect. Please consult with a PA consultant for more information. In addition, there are a few other data entry capabilities available depending on your goal and the template setup.

I would recommend scheduling some time with a Product Adoption consultant to help with the setup.
Please click here to book time.

Thanks,

#4

@jeff.king this post may be particularly helpful for you and your team after this week’s session!

#5

I like the View report feature and how can I export it to Excel. Thanks,

1 Like
#6

I like the consistency this will provide, will give it a try.

1 Like
#7

Wow, thanks so much! We recently ran a budget planning cycle based on using the Line Item Schedule, but there were definitely a few people who just ignored instructions to use the LIS.
I manually aggregated the LIS into one report for our CFO outside of Prophix, but it would be even more helpful to have a static report that could be viewed independently on it’s own. I guess that would require this to be considered a dimension?

1 Like
#8

We rely on these heavily in our org. Agreed with Angelique - It would be helpful to have reports we could format with headers and other information that appears on the table of contents.

Also - an understanding of how the schedule handles it when we switch a month from projection to actuals in the template.

2 Likes
#9

Line Item Schedule is great but the report when downloaded to excel could be much better. It requires a lot of manual formatting which cannot be done within Prophix.

3 Likes
#10

Hi Angelique,

Great to hear that the information was helpful to you.

Actually, the line item details will be entered on the leaf level of the existing Account Dimension. To obtain line item details information directly, you can use the Supporting Details menu options available via either the Data View or Template Studio Reports as well as the ability to download to excel. For more help, you can schedule a session with a PA consultant.

Thanks,

#11

Hi Jamie,

Thank you for your feedback. Is this something you could share with our development team?
Click here to give your feedback directly to our development team. We’d really appreciate it.

Thanks,

#12

Hi Jeanette,

I appreciate your feedback very much. Could you share this with our development team? You can send your feedback directly to our development team by clicking here. It would be much appreciated.

Thanks,

#13

Hi Mohamed,
I’ve done that multiple times since 2019 to no avail…still hopeful though.

1 Like
#14

Hello - we recently ran into a major problem with line item schedules. We’re on our second year budgeting in Prophix. When you add or delete line items from the line item schedule, it adjusts the prior year’s line schedules. The adds aren’t a big deal. However the deletes essentially destroy a lot of data from the prior year. So in your travel example above, if “vendor 3” is no longer being used in 2022 budget, and a user says “oh why is this line in my schedule, it’s not needed” and deletes it - then those dollars, and that detail, simply disappear from 2021.

From a design perspective, we have a single version for budget, and allow the time frame to govern the existence of the different budgets. This makes a lot of sense to us as then you are always writing reports to show “budget” and can for example compare budget 2022 and budget 2023. You also don’t have to re-write reports each year to pull in new distinct “budget 2022” and “budget 2023” versions.

Has anyone else run into this issue while using this otherwise good functionality?

1 Like
#15

Yes, we have. Users found that there were too many lines that they were not using from year to year. So they stopped using it.

#16

Retrieving line item detail is time consuming and when exported via a report, the file is not formatted well and takes time to update.

Having to budget at the lowest level, you always have to be in that view to view line item detail, other than line item reports which are buggy and not clean. Also the “unexplained balance” line is frustrating and confuses our users.

1 Like
#17

Ah - thanks for the replies. I think we’ve logged as a product request (or will? but could use help from the Prophix team here perhaps?). Seems pretty critical to me to resolve this, as the carrying over as Heather stated will lead to completely abandoning the function in probably one more year, and the risk of huge damage to our budget/forecast data is not tolerable. I think this is a critical and great functionality, but requires hardening on this aspect for it to hold water.

#18

We use Line Item Detail quite a bit but it is nice to know you can set up it that you must use Line Item Detail for an account.

Also I agree with Luke Matthews comment that the “Unexplained Balance” line is confusing to users. We change it after we pull the report to Excel to “Actual to Plan Variance” and then I still have to explain what that means to our users.

#19

We require line item details for some accounts and I encourage budget managers to use it for as many accounts as make sense.

One issue we have is when trying to save Line Item Details while the DPM is posting. It creates duplicates that can be confusing.

#20

@rob.benedict, we have a similar issue. Still trying to figure out how to handle that. In our case we are using it for a rolling reforecast so it’s always building upon prior data. After a few rounds the lines add up and the earliest no longer apply, but deleting the rows creates a data issue.