MK Checkbook Register

MK Checkbook Register

by Donna Bayes Scott

Wouldn’t it be great to be able to track your money with little effort? How would you like a system that keeps you from embezzling your business (which means you spend your reorder money and then you have no money to place a WS order when you need to!)? And one that assures that you have the money to pay for wholesale orders and PCP when the time comes, and that you can even write yourself a paycheck? Well, now you can with my MK Checkbook Register! (Some bugs have been fixed from the original one posted.)

All you have to do is enter the date of your deposit and the deposit amount and the register does the rest! It pulls out 50% to save for reordering more products, 5% each for both PCP and Section 2, and the full retail sales tax so it's available for your next WS order, and even shows your profit of 40%, so you can write yourself a paycheck! It's Mary Kay's classic 60/40 split without having to do the math!

It even takes into account discounts, but that will be explained later. For now, just follow my examples here, but open up a copy of the register so that you can play with it as you learn. The sample is based on my personal sales tax rate of 7.55%. You will have to adjust the tax rate for yours before you begin using it (and I'll explain how later), but for now assume that your tax rate is the same as mine.

I want to thank a really smart professor friend at FHSU who loves Excel and helped me with formulas.

To Enter Deposits

The first entry on 03/19/09 was for a total deposit of $107.55. Enter that amount into the actual Register that you are playing with.

Note that it fills in all the numbers across except for Freight.

Enter $8.75 into that column for the freight cost for your next WS order. Note that it subtracts that amount from your profit. You only need to enter this once, since this is the actual cost of freight on a WS order.

Note that it is giving you totals at the bottom of the Register.

Enter $354.92 and $111.85 as your next two deposits.

Take a look at your totals. In the purple column, you'll see that you have $267.00 in your account for your next Wholesale order. Since you can get 50% with a $200 WS order, you can now place an order.

So that you don't overspend on Section 2, it is suggested that you average 5% of your WS amount. You have $26.70 that you could use to purchase Section 2.

You have $40.32 for sales tax on your Section 1 order.

Your freight is available for that expense, and you have $204.85 for your pay check.

When you take profit, be sure to write yourself a pay check from your MK checking account and deposit it into your personal account. Do not pay for personal things (groceries, house payment, etc) from your MK checking account. Transfer the money first!! The IRS frowns upon co-mingled accounts.

Note: On the actual register file, you see that the first line says it is LOCKED. Begin your entries on the next line. I had to add that Locked line which I will explain later.

Now, let's place an order. Take a look at this picture.

After You Place A WS Order

In the $$ Spent row, enter $200 in the purple column, because this is the WS on the order you just placed to the company.

You ordered exactly $20 of Section 2 items, so enter $20, in that column.

Sales tax on this order (at 7.55%) is $31.71.

And freight charges are $8.75.

Oh, yes, while we are at it, be sure to write yourself a paycheck of $204.85 and deposit it into your personal account.

Print your register at this point so that you have a hard copy showing all your deposits and the amount of your order and the money that you still have for the future.

Note that the $$ Spent are subtracted leaving a total in the last row. This is the amount of money you have left in each category.

Here's how we handle this: Take a look at this picture.

I entered each of the numbers left in the bottom row into the (Extra $) row. This will save your left-over money for your next order. Note in the picture above that (Extra $) row has added again to the $$ Spent to give new totals at the bottom. Just ignore those new numbers. If you are doing this on your practice register, you'll see what I'm talking about.

Now, zero out the Register.

Delete each date

Zero out the Deposit Amount Column

Zero out the Freight column

Delete the numbers in the $$ Spent row

LEAVE the numbers in the (Extra $) row. You have those numbers to add to your next deposit for your future orders.

Take a look below. This one has been zeroed out, because a WS order has been placed, and it is ready for the next deposit.

If you goof the register up, you can always download another copy and enter the left-over money into (Extra $) row and begin again.

Discounts

Now, let's assume that you have a sale that includes a discount. Maybe you gave your customer a 15% birthday discount on her order. The discount must come from your profit, and not from any other column, so you must account for this discount. All you have to do is enter the dollar amount of the discount in the light yellow column on the right, and your deposit amount in the column next to it, Column K (and NOT in Column B, Deposit Amount). Columns J and K will total and automatically enter into Column B and subtract that amount from the profit column, leaving all the other columns alone. Let's try it.

Note the first entry: It is a regular deposit.

Note the second entry: Since it had a discount it is entered on the right side. You enter $92.55 because that's your deposit on a sale of $100 worth of products with a 15% discount, which means the discount amount was $15, which you enter in the appropriate column. This then totals and enters $107.55 in Column B. Try it on your Register. (Enter $15 and $92.55 into the two far-right columns.)

The third entry was a purchase of $100 of Section 1from another consultant at 50% (tsk, tsk!). We do not encourage buying WS from another consultant, but it happens, so there is a place to enter that. Note line 21. Enter those discounts in that section. The reason it is there and not on a regular line above is because you don't want it subtracting PCP and Section 2 out of your profit, because there IS no profit. Doing it would would give you a negative profit. That's fine if you want to pad your Sec 2 and PCP accounts.

TIPS

Be sure that you have both a MK checking account and a personal checking account. Do not try to run your MK business from your personal account. Not only does the IRS frown on this, but bookkeeping is a nightmare.

When making a deposit ALWAYS write down the name of the customer along with her check amount on the deposit slip. You then have a written record of who has paid and when. I've gone back to these deposit slips (which I can see on-line at my bank's website) many times to verify that a customer has paid.

I have found that it is easy to write the amount of the discount right on the deposit slip, so that info is at my finger tips, too, if I need to look back.

The LOCKED rows are for your benefit to "fix" the register if a formula gets deleted.

Updating the Formulas Using the Locked Row

If you need to "fix" the formula in a certain column, just put your mouse on the little square in the lower right corner of the locked cell. (Note the arrow.) Drag this square down to the cells in the column that need to be fixed and the formulas will again work. It's an easy repair. You may have to do this when you zero out your register after placing an order.

Changing Sales Tax

Be sure to adjust the Excel file to account for your own tax rate. This one is created for mine, which is 7.55%.

To change the tax rate, go to Tools/Protection and click Unprotect Sheet.

You have to change the formulas in two columns: Column C and Column G.

Click on the yellow box in the Locked row in Column C. Look at the picture below. The formula will show above the C column (note the arrow). Change the tax rate to your rate. The 1.0 will stay there. If your tax rate is 6.25%, then it will read 1.0625. If it is 5%, then it will read 1.05. Once you have changed it, then drag the column down as instructed above (Updating the Formula Using the Locked Row) to change all the formulas in the column.

Do the same for Columns C and G in both locked rows. That's four times you need to change the tax rate and drag the formula down.

Once it is finished, then go to Tools/Protection and click Protect Sheet. Save your file to My Documents.