Allocating Expenses Automatically

We are recording and breaking down our sales base on the Global Dimensions. For Global Dimension 1 Code we setup as Profit Centers with the values of PC01, PC02 and etc. Global Dimension 2 Code we setup as Regions with the values of RG01, RG02 and etc. At this point we can do sales analysis for each Profit Center and Region. This is works perfectly. The problem that we have is when allocating the sales expenses. We are manually prorating the expenses and apply it one by one to each region. We prorate the sales expenses base of the contribution percentage of each region to the total sales. For example 1 region contributes 15% of the total sales, then, the sales expenses will also apply as 15% of the total sales expenses. It’s a time consuming job, especially for the profit center with many regions. Is it possible for the system to allocate the sales expenses automatically to the regions without any modification? Or probably some tricks that can solve the problem? P.S. Only Profit Centers with more than 10 regions will use this prorating calculation. Thank you for any helps. ZEN

I think you need to have your NSC make a mod to do this.

You will not get all of it but the recurring journals have the ability to allocate, and you can allocate to the same account splitting by global dimension. If the percentages are fixed you can create the entry as a line and the allocation split will apportion the line correctly. Only once to set up, but you still need to manually enter the lines based upon sales and sales expenses, assuming of course I am understanding you correctly!

Zen, I think there are many idfferent interpretations to your question. How for example are you recording sales expenses? I was assuming through purchase invoices, I guess Steven is assuming through journals, bith sem viable, and now that I think aboutit there are more otpions too. You will need to give a clear description of what costs you are distributing, how you are posting them,adn how you are distributing them.

I just thought if my answer completely confused him he would tell me how he was recording expenses! As you say David, many ways to interpret the question, we wait with anticipation I am sure! hope the trip to the mountains was fun and relaxing.

Yes its some times hard when in your mind you nderstand the questions, and can’t see why it is hard for others to see. And yes I am back, and had a great time to relax.

The way I read it, I agree with Steve and use Recurring Jnls and allocation the amounts by percentage.

David, Yes, we are recording our sales expenses thru Purchase Invoice. Steven, About the recurring journal that is actually the last option that we are going to do; if there is no other solutions. As you said, it’s needs to be done manually and it becomes an issue for multiple regions with multiples expenses. I am trying to find the best solution for this situation. ZEN

Hi Zen, glad I interpreted your requirement correctly. I have seen this request many times, thats why I assumed that was what you wanted. Really if you are doing this a lot, then customization is the only route, I know how big a taks this is for users, and ust getting it correct is a big job. Basically Recurring journals are not going to help you much, since these costs are not recurring, and its just s much work to modify a recurring journal for each invoice as it is to just do the work manually. I am guessing that what you do now, is to split the purchase line into multiple cost lines,a dn use different dimensions on each one, is that correct?

You are correct David. That’s exactly what we are doing right now. We split the purchase line into multiple cost lines and use different dimensions for each line. ZEN

Well in that case ignore the recurring journals and modify the system to do what you want it to do. There is nothing that as standard will ease this situation for you.

Is it going to be a major modification? Instead, I am thinking to create new G/L Accounts and pull the sales expenses under these G/L Accounts. Please advise about this idea. ZEN

Hi Zen I am not a developer so I have no idea on the time to modify the system, however if you split the accounts out you will lose all of the flexibility of dimension reporting and analysis across the board. I cannot comment on the regional analysis you require but the ability to splice and dice the data will be lost if you verticalise the GL in this manner. I would guess you need the power of analysis by region and would therefore advocate you investigate the modification prior to re-designing the GL and losing the reporting flexibility to enable quicker journal entry.

You are right Steven. That’s a very nice thought. Like Nils said “you can’t see the tree because of the forest”…that is me…[:D] Hhm…it seems there is no way than the customization. Thank you guys for the help. Two thumbs up for you! [:)] ZEN

HI Zen, I just wanted to confirm my guess of waht you were doing before going into any great detail. Actually there is a pretty simple way fo creating the mod you need. The first question is “How do you setup the percentage splits?”. I see that it is based on Sales, but what sales. For example a/ On a periodic basis, (say monthly) you review sales for the previous period, and then create a nominal distribution value for each region. And then that percentage is valid for the whole of the next month. The advantage of this is that the numbers are stable for the next month, and the calculations are easy. Problem is that the cost distribution for next month is based on last months sales, so it only works well if sales distribution is fairly constant. (Compare this to standard cost in Inventory). b/ At the end of a period (say a month) you post the expense invoices for that month. At that time you then calculate the total sales and the distribution percentages, so you know the actual sales, and thus the distribution applies correctly. The advantage is of course that you have a nice even and correct cost distribution for the previous month. The problem is that you can’t post any Costs untill all the revenue is posted, so timing is an issue. (Compare this to Average cost). c/ You need to post Expenses as they come, and then each time there is a sale, you want to then distribute those costs exactly to the correct region required. The advantage, is that the costs are always exact. THe disadvantage is that it is a huge issue to design the process, and a large task to implement. COmpare this to FIFO. Let me know which of these is closest, OR tell me d/ and I will explain the next step.

David, [Wow!]I am amazed with your deep analysis. I read all over again and again all the options very carefully. Point (c) is the one that we are doing. ZEN

[B)] I guess it always is the hardest option. Ok it will take a bit more time, I will get back to you on it though, just not right now. I will have one more question you can maybe answer now. When an expense invoice come in, how do you determine the sales period it applies to, i.e. is it sales inthe last week, month, calendar month year current year etc.

I am highly appreciating your helps. It’s a big thank you from me. We apply it to Month to Date Sales. ZEN