Business Intelligence Basics: Having a Target/Goal (Part 4)

Business Intelligence Basics: Having a Target/Goal (Part 4)

  • Comments 1
  • Likes

Subtitle: "An Incredibly Simple Example of Budget Modeling"

Most budget versus actual comparisons reduce down to the comparison of two fully-qualified account balances:

Although there are many variations, a “fully-qualified account” is defined within most business accounting systems as a combination of an organizational identifier called a “cost center” and a financial “bucket” called an “account”.

Many of your reading this probably have some concept of a budget.  Your budget probably has one cost center such as “Ward’s House”, and it probably has multiple accounts such as “Automotive Expense”, “Food Expense”, and “Income”.

Now, think about what is going through your mind as you are building your budget for the next year.  You almost certainly do not automatically know what the charges to your “Automotive Expense” account are going to be each month.  Rather, something like the following is probably going through your head:

Ok.  It looks like the cost of gasoline is going to be coming down a bit.   Therefore, I can probably plan for a bit less fuel expense per month ($125).

But, the Toyota is about to hit 100,000 miles, so we are going to have to get its scheduled maintenance ($725) in February…and it looks like it is going to need tires ($350) around March.

Welcome to the world of budget modeling.

You are taking the facts that you know (scheduled maintenance required for the Toyota in February) and translating them into account activity ($725 in “Automotive Expense” in February).

After February is history, you will be able to see how much you actually spent on “Automotive Expense” and compare it to your budget.

Well, businesses do the same thing.

Let’s assume that “Generic Corporation” has two employees for which it wants to budget.  (It will ultimately need to budget for all its employees, but let’s focus on these two.)

 

Position

Annual Salary

Joe Acme

Developer

$50,000.00

Frank Smith

Analyst

$65,000.00

Both employees work for the “Information Technology Department”, cost center 5200.

Since Generic Corporation tracks results for each month, the first step of our modeling is to translate annual salary to monthly salary. (Remember, we need to compare actual to budget for the same time periods.)

 

January

February

March

April

May

June

July

August

September

October

November

December

Joe Acme

$4,166.67

$4,166.67

$4,166.67

$4,166.67

$4,166.67

$4,166.67

$4,166.67

$4,166.67

$4,166.67

$4,166.67

$4,166.67

$4,166.67

Frank Smith

$5,416.67

$5,416.67

$5,416.67

$5,416.67

$5,416.67

$5,416.67

$5,416.67

$5,416.67

$5,416.67

$5,416.67

$5,416.67

$5,416.67

One of the accounts that we need to calculate is “Salary Expense”, account 3201.  This one is pretty straightforward.  It is the sum of the salaries:

 

January

February

March

April

May

June

July

August

September

October

November

December

Joe Acme

$4,166.67

$4,166.67

$4,166.67

$4,166.67

$4,166.67

$4,166.67

$4,166.67

$4,166.67

$4,166.67

$4,166.67

$4,166.67

$4,166.67

Frank Smith

$5,416.67

$5,416.67

$5,416.67

$5,416.67

$5,416.67

$5,416.67

$5,416.67

$5,416.67

$5,416.67

$5,416.67

$5,416.67

$5,416.67

Salary Expense

$9,583.33

$9,583.33

$9,583.33

$9,583.33

$9,583.33

$9,583.33

$9,583.33

$9,583.33

$9,583.33

$9,583.33

$9,583.33

$9,583.33

So, for example, the fully-qualified budget for cost center 5200, account 3201, for February is $9,583.33.

Another account that we need to calculate is “Retirement Tax”, account 3205.  It is charged at a rate of 1.45%, for the employee’s initial $55,000 of income.  (For those of you acquainted with Social Security, this is similar to Social Security, but I have intentionally not made it exactly the same.)

 

January

February

March

April

May

June

July

August

September

October

November

December

Joe Acme

$4,166.67

$4,166.67

$4,166.67

$4,166.67

$4,166.67

$4,166.67

$4,166.67

$4,166.67

$4,166.67

$4,166.67

$4,166.67

$4,166.67

YTD Salary

$4,166.67

$8,333.33

$12,500.00

$16,666.67

$20,833.33

$25,000.00

$29,166.67

$33,333.33

$37,500.00

$41,666.67

$45,833.33

$50,000.00

Retirement Tax

$60.42

$60.42

$60.42

$60.42

$60.42

$60.42

$60.42

$60.42

$60.42

$60.42

$60.42

$60.42

 

 

 

 

 

 

 

 

 

 

 

 

 

Frank Smith

$5,416.67

$5,416.67

$5,416.67

$5,416.67

$5,416.67

$5,416.67

$5,416.67

$5,416.67

$5,416.67

$5,416.67

$5,416.67

$5,416.67

YTD Salary

$5,416.67

$10,833.33

$16,250.00

$21,666.67

$27,083.33

$32,500.00

$37,916.67

$43,333.33

$48,750.00

$54,166.67

$59,583.33

$65,000.00

Retirement Tax

$78.54

$78.54

$78.54

$78.54

$78.54

$78.54

$78.54

$78.54

$78.54

$78.54

$12.08

$0.00

 

 

 

 

 

 

 

 

 

 

 

 

 

Total Retirement Tax

$138.96

$138.96

$138.96

$138.96

$138.96

$138.96

$138.96

$138.96

$138.96

$138.96

$72.50

$60.42

Notice how our budgeted expenses for November for Frank Smith decrease because his salary has surpassed the initial income threshold of $55,000.

Therefore, for example, the fully-qualified budget for cost center 5200, account 3205, for November is $72.50.

Seems pretty simply, doesn’t it.  Well, this example is simple, but for a typical business, budget modeling can become very complex.

Imagine modeling expected sales revenue for a corporation.  What do we know about our customers’ purchase patterns?  Are there outside factors such as the weather or economy that are going to impact our sales?  Will our sales revenue be capped by our ability to produce our product?  Is our new marketing campaign going to boost sales?

Imagine modeling expected expenses associated with making and selling the products:  What is the cost of fuel going to be?  Are our suppliers going to change the prices they charge us for our raw materials?  What is cost of labor (people) for making the products going to be?  Are we going to have to raise their salaries to keep them? 

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment