Speaker 1: In this presentation, we will work a problem related to expense allocation to departments. The information is going to be in white that we're then going to use to fill out the parts in blue as we go through the worksheet. Our information up top will start with our table. We have the expense allocation to departments. We have the allocation base, which is going to be that's going to be what we're going to use in order to make allocation of the indirect costs, payroll requisitions. We have the total expenses for, in this case, the salaries and supplies. We have the service department, so we have a service department 1 and a service department 2, and then a sales department 1 and a sales department 2. You can see what's going to happen here is the service departments are going to be items that aren't going to be generating revenue. We're going to be allocating and allocating costs to the service department and ultimately the costs of the service departments will then be allocated to the sales department, the revenue generating areas. Let's see how that would work, how this process would play out. We're going to break the costs out into the direct expenses and indirect expenses. The direct expenses are going to be applied directly to the department. We know exactly where they go. We simply apply those expenses to the department. For salaries and supplies, then we could say these are going to be given to us. They're going to be given directly to the department, incurred by the department. We don't have, in other words, an allocation problem with them. Then, of course, the indirect expenses, those are going to be the items that we're going to have to use some type of allocation base to apply out. What we know then is the total indirect expenses that we need to apply out in some way to the departments. In other words, for example, the rent and the utilities, we know what we paid and we know we paid for them for all the departments within a particular building. Let's say, and we know all these departments were in their service department 1, service department 2, sales and sales 2. Now we need to apply those out in some format. We're going to use the floor space in order to do that. We're going to scroll back down and we're going to say, okay, well, first we're going to need the floor space per department. We're going to say, okay, service department 1, service department 2, sales department 1, sales department 2. Here's the square footage. We're going to use that square footage to apply out the rent and utilities. You can see how square footage you would think would be an applicable or useful or relevant basis to use. That's what we're going to use for our percentage analysis. This is going to equal for service department 1, 200 divided by 2,000 and enter. Service department 2 equals 200 divided by 2,000 and enter. Then we have the sales department 1 equals 600 divided by 2,000 and enter. Then sales department 2 equals 1,000 divided by 2,000 and enter. Then if we sum these up, we've got the 10%, 10%, 30%, and 50% or 100%. Now we can use these percentages to allocate out these items up top. And so we'll simply do that by saying, all right, we're going to take for the service department 1, the 10,000 total times our percentage down here, which is 10% tab. And then for service department 2, this equals the 10,000 total times, and we're going to pick up the 10% for service department 2 tab. And then for sales department 1, this equals the 10,000 times the 30% tab. And then for sales department 2, this equals the 10,000 times, scrolling down the 50% and enter. Okay, so now we'll do the same thing with utilities, same process. Service department 1 equals the 1,000 times the 10% tab. Service department 2 equals the 1,000 times the 10% tab. Sales department 1 equals the 1,000 times 30% tab. And then sales department 2 equals 1,000 times 50% tab. And obviously, we use these ratios to break them out. If we add them up, 10,000 up top and 1,000 for the utility. So the totals, therefore, then are the same. If we sum these up looking for the total department expenses by saying equals the sum of, we'll take all the expenses all the way down from the direct expenses and the indirect expenses for the totals here. That's going to be the 2,200 for all the way down. We're going to autofill that across. I'm going to put my cursor on this little highlighted, this little box and autofill across. So we have the same relative formula all the way across. So there we have that. Now we want to take a look at these service department expenses. Notice that we have these two expenses for the service department. We've now allocated the cost that we need to to them, including the rent and the utilities. And then we need to now allocate these to the sales center that actually generate revenue. So in other words, these two departments are all service department costs, which we do in order to generate revenue. And therefore, we ultimately need to allocate the costs of them to the revenue generating the profit centers. So now we're going to have to say, okay, how can we allocate the 2,002 and the 3,004 to the two departments, the sales department one and sales department two? Well, one way we can do this, we can use an activity base. We're going to use sales first as the activity base. So if we scroll down and we look at the two departments, sales for department one, we're going to say is 40,000 and sales for department two is 48,000. And we're going to use that to have a ratio analysis. So we're going to say, all right, if sales for department one, these are profit centers now with 40 and for department two was 48, that adds up to 88,000. The ratio of equals the 40,000 for department one divided by the 88,000 total gives us 45%. For department two equals the 48,000 divided by the total 88,000. Enter, and then we'll sum these up equals the sum of the 45 and the 55 providing the 100%. So now we'll use these ratios to apply out. So what we're going to do up top is we're going to say, all right, well, here's the 2,200. I'm going to apply that out to the sales department. So the way to do that, I want to make this go down to zero. So I'm going to say, this is going to be applied out to department, the sales departments in accordance with the ratio. First, I'm going to say negative of this number, because I want to total this up and eliminate this as we go and show the elimination of the service department. So this will total up then, of course, to zero when we total it. And then we're going to apply it out to the two sales departments in accordance with our ratios. So in order to do this, instead of hitting equals, I'm going to say negative again, because I want to flip the sign to make this positive. So I'm going to say negative of this number times. And then I'm going to go down to our ratios 45% for department one and enter. And then we'll do the same for the next department. So this is sales department two, negative of this number times. And then we're going to go down to our ratio for department two, 55% and enter. And then if we add those two up, of course, that adds up to our 2,200. Now for the next item, we're going to do the same thing for the sales department two with this amount, making it go to zero and applying it out between the sales department, because this is our other service department. We're going to apply out. However, we're going to use a different activity base. So for this one, we're going to use employees, the number of employees. So we're going to say, okay, department one has 28 employees. Department two has 40 employees for 68. And you might say, well, why would we use employees? Maybe we think it's a better activity base to apply out. We're looking for something that's going to be a driver of, you know, the proper type of allocation that we're going to make. So we're saying employees we think is a good ratio to use to apply out this service. So we're going to say, all right, that's going to be equal to the 28 employees for department one divided by the total for the two departments, 41%. Department two is going to be the 40 divided by the 68 or 59%. Adding those two up, summing them up, the 41 and the 59 is 100%. We're going to use these now to allocate out this cost. So I'm going to go back into service department two. I'm going to say negative of this number to make it go down to zero again, negative of the 3400 tab. Now we're going to go into the sales department one. I'm going to allocate this out with our ratios. To do that, I'm going to say negative to flip the sign to make it positive of this number times, and then we'll go down to our ratio 41% for department one tab. Going to do this again. Now we're in sales department two, negative of this number times, and then we'll scroll back down and we're looking for the 59% tab. So then if we add these two up, of course, we get the 3400, 3400. Okay, so now we're just going to sum these up across, right? So we're taking this sum, I'll sum it up here, equals the sum of this 2002 and this negative 2002, making it go down, of course, to zero. We'll do that same allocation across. And we'll see, of course, that the service departments go down to zero because now we've allocated their costs to the sales departments. So now we've allocated them to the sales departments and there we have our items. So now we'll take a look at our income statement for the service departments. So we're going to say our service department income statements, and we're going to say this is going to start off with sales. We got the total on the inner column. So we're going to start off with sales. So sales is going to be up top. Of course, we calculated it here in our calculations. So I'm going to pick up the 40,000 sales for Department 2 equals the 48,000. And then, of course, if we sum that up, we get the 88,000. And then the cost of goods sold, this will actually be a hard-coded number for us. It's not in our data set up top. So it's going to be the 20,000, the 18, and the 38,000. And then the gross profit, we'll subtract those two out. This equals the 40,000 minus the 20,000. This equals the 48,000 minus the 18,000. We could sum them across here for the 50 or subtract out the 88 minus the 38 for the 50. Okay, so then we have the operating expenses. We've got the salaries. We're going to go for Department 1. So the sales department for the salaries is going to be the 6,000. And again, we're looking at the sales department. So it's going to be the 6,000. And then we're going to go to the sales department 2 equals. And if we scroll up, that's going to be the 11. And now you might be thinking, well, what about these other service departments? Remember that we allocated out the total of those service departments to the sales departments. So we did take care of those. We've included these two amounts in the sales totals basically at the end of our worksheet. So then we're going to go to the, we could total that up. So we'll say equals the sum of these two items. Then we're going to go to the supplies for Department 1 in C38 equals. Scrolling back up, we're looking for the supplies. And we're going to pick up the 400 tab. And then the supplies for sales department 2 equals. Scrolling up the 700, enter. Then we'll sum those two up equals the sum of these two items. And then we're looking for the rent. We're going to say that that equals. And we'll scroll back up looking for the rent. That's going to be the 3,000 tab. And the rent for Department 2 equals. Scrolling up, that's going to be the 5,000 and enter. We could sum that up and total that equals the sum of those two items. And then the utilities, we're going to say this equals. Scrolling back up for the utilities, we want the 300 tab. And equals, scrolling back up, the 500, enter. And if we sum that up, and then we've got the service department allocation. So this equals, if we scroll back up, the allocation of service department 1 was 1,000. So now all the costs here, we now broke out. And now we're allocating for service department 1. All of these costs are here. We're breaking them out to sales department 1. The service department 1 to sales department 1 here. And then the other side going to sales department 2. So that's going to be this number. And if we sum that up, we'll get the total service department that is allocated to the sales department. And then we'll do the same for service department 2 that we're allocating to sales department 1. And that's going to be here. So here's service department 2 allocated 14, 1,004 tab. Second one is going to be the 2,000 and enter. So we'll sum that up. And then we'll have our total expenses equals the sum of these items. Going to copy that across, autofill, put our cursor on it, autofill across. And then net income equals the gross profit minus the total operating expenses and enter. We can autofill that across like so. And there we have that. So the next thing we can do is we can take a look at this in a departmental contribution to overhead type of format. We'll just basically reformat this information a little bit differently. So this is another way we could see this information. So we're going to start off with the sales. Same starting point. Tab, tab. I'm going to say sales department 1 is going to be the same number. Sales department 2, same number. We'll sum that up. Same format. Cost of goods sold equals same information up top. Tab. And we'll say sales department 1 equals the 20,000. Tab. Sales department 2 equals the 18. Enter. And we'll sum that up. So we'll sum those two items and that'll give us our gross profit. Same information. And then we'll subtract these out. This equals the 88 minus the 38. Tab. This equals the 40 minus the 20. Tab. This equals the 48 minus the 18. And you could autofill that across as well. And then we're going to have our, this is where we differ. We're going to have our direct expenses. So we're going to have our direct expenses and that's going to include salaries. So I'm going to say equals the salaries. Tab equals, well let's go on to sales department 1. Equals the 6,000. Tab equals for department 2, the 11,000. We can sum these up then. Equals the sum of those two items. And then we've got the supplies. So this equals the supplies. Tab. Tab. For department 1 equals the 400. Tab. For department 2 equals the 700. Enter. Summing those up equals the sum of 4 and 7. For 1,100. And that gives us our total direct expenses. We'll sum that up. Equals the sum of those items. Autofill that across. And then we have the department contribution. So we're going to subtract this out. This equals the 20,000 minus the 6,400. We'll copy that across. And we could copy that across this way. We could sum this up either way. I'll do it the same. This minus this. And these two added up will add up to the same number. So remember this is the number that we want to break out because it's going to tell us basically how well these department did. In other words, if this was negative, we might say that a department might be in danger of being closed. And then we have our allocation. The items that are indirect that we may allocate to the departments to help us with decision making. But it doesn't help us with the decision as to whether or not we should close a company or not. Because these items are really just allocated. And they don't have the same type of control over them. So therefore, if we were to eliminate a department, we wouldn't necessarily eliminate the indirect costs. So we're just going to show them in total now without the allocation. So we'll just show them in the total column here. So these are going to be the indirect expenses. And those are going to include rent. So we're going to scroll back up. The rent and tab. This is going to be equal. And we're just going to pick up the total line now. Which is, of course, a bigger number. So enter. And then the utilities. Utilities, tab. And that's going to equal the total. The $800. Enter. Service Department 1. Tab. Equals. The $2,200. Service Department 2. Tab. Equals. The $3,400. And then we've got the total indirect expenses. If we sum these up. And that's the $1,404. And so our income. Let's go ahead and underline this. Our net income will equal the department contribution minus the total indirect expenses. And we'll go ahead and double underline this one. So there we have it. Now note, again, we could allocate out these indirect expenses. However, it's just an allocation to do so. So you might see them allocated out. You might see the same format kind of with these allocated out. But just recall and remember that this is an important subtotal when making decision making. Especially in terms of whether a company is contributing to the organization or not. Thank you for your time.