Master the Operating Budget

You are helping Jacks Doggie Palace with their annual budgeting process. You need to create a master budget for the companys 2021, first quarters operations (January, February, March). This budget is for potential investors (friends of Jacks), whom he hopes will contribute capital to his business. Jacks sells designer dog collars.

1. The following is information from the November and December budgets:

	November	December
Credit Sales	$36,800	$38,100
Supplies	$3,800	$4,100
Payroll	$4,500	$4,500
Misc	$400	$500
Inventory Purchases	$19,700	$25,600
Ending Inv	1300 dog collars	1400 dog collars

2. You have obtained estimates for a sales forecast from Jack, the store manager and the head sales clerk. (note these are three separate forecast, do not add them together)

Sales forecast for January (units)

Head Sales Clerk 12,000

Jack 13,500

Store Manager 14,500

Expectations are for sales to increase 5% in February and decrease 2% in March (compared to February sales). Round to nearest ten units. The dog collars sell for $15 each.

3. Typically 40% of sales are made in cash and 60% are paid in credit. Jacks expects to collect credit sales as follows: 10% in the month of the sale, 60% in the month following the sale, and 30% two months following the sale. Assume there will not be any bad debt.

4. Jacks would like to maintain an ending inventory balance of 10% of next months sales. Each collar cost $8.00 each. They purchase all inventory on account. They pay 70% of their purchases in the month of purchase and 30% the month after purchase. April sales are budgeted to be 14,000 units.

5. They will have the following expenses
   1. Insurance - $200 per month
   2. Licensing fees - $160 per month
   3. Supplies - 2% of total monthly sales
   4. Advertising - $250 per month
   5. Payroll expense - $4,500 per month
   6. Miscellaneous expenses - 4.5% of total monthly sales

6. Jack has decided that they need a new computer system and expects to buy it the beginning of January. The equipment will cost $20,000. The computer system has a useful life of 5 years and salvage value of $200. They will begin depreciating the computer system in January. (Straight line depreciation).

7. They pay insurance, licensing, and advertising in the month incurred; they pay supplies, payroll, and miscellaneous expense in the month after incurred.

8. At the end of December, Jack expects to have debt of $50,000 outstanding. Currently the payment schedule is $2500 a month in principle and $100 a month in interest.

9. Ending cash balance for December is budgeted to be $45,000.

10. Decembers Retained earnings balance is $34,750

Required:

1. Open BudgetProjectTemplate from the assignment tool on Blackboard
2. Input the information given above into the data worksheet
3. Link the appropriate information from the input sheet to the appropriate cells in the various operating and financial budgets

• Sales budget
• Cash Receipts Budget
• Inventory Purchases Budget
• Cash Payments for Inventory Budget
• Selling and Administrative Expense Budget
• Cash Payments for Selling and Administrative Expense Budget
• Cash Budget
• Pro Forma Income Statement

Use the lookup function to alternate between the three sales forecast.

4. Formulas for the balance sheet have already been entered for you. If all of your formulas are correct, the balance sheet should balance.
5. Save the file using your last name, first and middle initials, and the project number, for example murphyab-xl3.xls. Turn this file in through Blackboard