How to use a drop-down list within the PROFILE function

PrevContributor
PrevContributor
in Modeling

Hi - struggling to get my head around this. Not sure if I'm getting the syntax wrong, or whether what I'm trying to do simply isn't possible. I want the model user to be able to make a selection in a drop-down list from a number of different allocation profiles to use in the PROFILE function. To hold the different profiles, I need to have a 3-dimensional module ("Profiles") containing Lists for "Profiles" and "Relative Periods", and a single line-item to hold the profile value. In my module that is going to perform the calculation, I have line items for: Value: the line item that needs to be profiled; Profile: the profile to be selected (formatted on the List "Profiles"); the PROFILE function. How can I set up the PROFILE function to enable the user to determine which profile to use? Thanks!

Tagged:

Best Answer

  • PrevContributor
    PrevContributor
    Answer ✓
    Hi Gerald,

    I believe you just need one more line item in your 2nd module to make this work. After you select the Profile from your List-formatted Line Item, you need another Line Item to do a Lookup on that selected Profile to get the schedule. I will try to use the example you described, but will add in Labels so you can see the syntax.

    Module 1
    Let's call the first module "Depreciation Schedules." It has one Line Item called "Value" and two Lists: "Profiles" which lists the different schedules such as 2-year, 3-year, 5-year, etc. and "Relative Periods" listed as 1-50 to hold the depreciation spread schedule.

    Easiest way to view is to pivot the module so that "Profiles" are on rows, "Relative Periods" are on columns, and Line Items are on pages. Then, save as default view.

    Module 2

    The second module is called "Fixed Assets" and has the four Line Items described below. It also has a List called "Capital Purchases."

    - Purchase Value: entered amount for Capital Purchase
    - Profile: formatted as the List "Profiles." Time Scale for this Line Item should be set to Not Applicable.
    - Depreciation Schedule: Time Scale for this Line Item should be set to Not Applicable. Applies To should be set to "Capital Purchases" AND "Relative Periods." Formula is Depreciation Schedules.Value[LOOKUP: Profile]
    - Calculated Depreciation: Formula is PROFILE(Purchase Value, Depreciation Schedule)

    So again, the only thing I added is the Depreciation Schedule line item in order to do a LOOKUP on the appropriate Profile schedule for each Capital Purchase.

Categories