Hello everyone! I am trying to build a data entry template where I can show YTD budget vs. actual numbers for each department and then manually enter forecast numbers for each department for the remaining quarters. I’m curious what methods others have used. The template is pulling in my budget and actual numbers, but I’m not sure how to create the column where I populate forecast data. My template is set up so that I can go to Pages and toggle between departments, but that’s as far as I have gotten.
Hi Heather,
To do what you are trying to achieve, you will need to include 3 dimensions in the columns: Time, Version and Time Perspective. It could look something like the example below. To do the below, you will need to enable unique combinations.
You will need to include Time Perspective in the columns so that you can input your forecast data into the [BASE] Time Perspective member, as you cannot write data to the [YTD] member (since YTD is just an aggregate of BASE). As well, if you are inputting forecast data at the quarter-level, Prophix will prompt you with various spread options to spread the quarterly input down to the months.
Hope this helps!
Lil
2 Likes
Hi Lil,
Thank you for your reply! I created a forecast column using unique combinations for Time Perspective, Time and Version. When I open the template in Data Entry Mode, it says that the cell is not editable. In Design Mode, it does not show the cells as locked, so I’m not sure what I’m missing.
Regards,
Heather
1 Like
Hi Heather,
There would be a few things to check that could be locking the cells:
- Are all Page dimension selections at the leaf-level? (i.e. the Department selection should be at a leaf member)
- If you open the template in Design Mode, is the the Time Spreading enabled?
- In Model Manager, is the Forecast Version Status set to Active and are the quarters/months you are writing to within the Start/End date?
Lilian
2 Likes
We have both a hard coded forecast set at a certain percent and also data entry mode for this to calculate out the remainder
1 Like
Hi! Did you get an answer?
I have received some responses, but I am still trying to get it to work. The snapshot below is the format I am currently working with. I used unique combinations to create the Annual Expenditures Forecast column, but it won’t allow me to edit the cells in Data Entry Mode. Then I tried the column next to it where I would hard code the forecast in Data Entry Mode, but when I save it and then open in Report Mode, none of the numbers are there.
1 Like
Noting Lilian’s comment on leaf member. if your groupings (personal services, meeting conferences & training, etc) or subtotals for a range of accounts. You could designate one account from the group to data entry into, or create a dummy forecast account in that hierarchy. you may have an extra line in the template to key into but the report will have it roll up cleanly.
1 Like
Hey Heather,
There are a few general rules when it comes to inputting data in a data-entry template:
- All dimensions should be set to the leaf-level, unless the Time/Dimension spreading is enabled under Data View Properties > Spreading (like in my screenshot above)
- The Version you are writing to must have a Status set to Active, and the time range you are trying to write to should be within the version Start and End date (like my screenshot above)
- Time Perspective should be set to BASE
- The Accounts you are trying to write data to should be set as an Input type
In terms of hardcoding the forecast into the “9 Month Forecast” column, if that column is only hardcoded on the template and not written to a specific dimension combination, then any data written in that column will not actually save into the cube. In result, when you close and re-open the template, the numbers you input will not be there. Does that make sense?
-Lil
1 Like
Yes, it does. I was able to get the template set up. I appreciate the input that you and everyone provided!
Wow this is quite the troubleshooting session - I was coming in to see if I could add my help, but it looks like it got taken care of!
1 Like
Glad to see the outcome here- I was about to suggest “check that you have all the necessary access/ rights” but I guess that’s redundant now!