Question

On January 1^st, 2018. After its first year of operation, CC’s president, Allen Hale, is now trying to prepare the company’s master budget for the first two months (January and February) of 2019. Since you are his good friend and an accounting student, Mr. Hale has asked you to prepare the budget.

Based on the budgets from 2018, Mr. Hale has gathered the following information:

• Projected Sales for January 2019             $124,300
• Average Monthly Sales Increase                         3%
• Average Monthly Salary Expenses           $ 36,400
• Average Monthly Rent Expenses             $ 5,200
• Average Monthly Utility Expenses          $   6,400
• Average Monthly Misc. Expense             $   3,200
• Supplies Expense-percent of sales                        3%
• Commission-percent of sales                                6%

Begin your project by carefully reading all instructions given here and reviewing the balance sheets and budget worksheets contained in the Excel workbook.

Fill in the data table in the upper left hand corner of the “Budgets” worksheet (found on the second tab in the Excel workbook) with the values above and other values found in the assumptions given below. Using this information, prepare Chippewa Chocolates’ master budget for the first two months of 2019. All amounts should be rounded to whole dollars as necessary. Apply the following assumptions:

                  

1. Customers typically pay 45% of total sales in cash and the remaining 55% on account. The company expects to collect 100% of the credit sales in the month following the sale.

2. The cost of goods sold is 40% of budgeted sales and the company desires to maintain a minimum ending inventory equal to 25% of the next month’s cost of goods sold. All purchases are made on account.

3. The company pays 80% of inventory purchases in the month of purchase and the remaining 20% in the following month.

4. Depreciation expense on existing equipment is $4,000 each month. In addition, CC will spend $180,000 on January 1 for additional equipment. The equipment is expected to have a $7,200 salvage value and a four-year (48-month) useful life.

5. Salaries, utilities, and sales commissions are paid the month after they are incurred; all other expenses are paid in the month in which they are incurred.

6. The company borrows and repays funds on its credit line in increments of $1,000 on the last day of each month as necessary to maintain its targeted $44,000 cash balance. It pays interest of .3% per month in cash on the last day of the month on cash borrowed in the prior month.

Needs to be formatted on Excel

Answer 1

				jan	feb
	sales(=1.03*sale of prev mnth)			124300	128029
	COGS(0.40*sale)			49720	51212
	supply expense(0.03*sales)			3729	3841
	commission(0.06*sales)			7458	7682
	RECEIPT AMOUNT
	sales(=1.03*sale of prev mnth)			124300	128029
	cash sales(0.45*sales)			55935	57613
	credit sales(0.55*sales)			68365	70416
	collection of credit sales (in next /following mnth)				68365
	COGS			49720	51212
	closing inventory(0.25*next mnth cogs)			12803	13187
	opening			0	12803
	net purchase			62523	51596
	payment(80%*net purch same mnth)			50018	41277
	BALANCE(20%*net purch on next mnth)				12505
					(0.20*62523)

	CASH BUDGET
	RECEIPT
	sales - cash			55935	57613
ADD	- credit sales collection				68365
	amount before loan (A)			55935	125978
	Payments
	purchase			50018	53781	(41277+12505)
ADD	salary			36400	36400
ADD	utility			6400	6400
ADD	commission				7458
ADD	Rent			5200	5200
ADD	misc exp			3200	3200
ADD	suplly expense			3729	3841
ADD	equipment			180000
	INTEREST   0.3%*LOAN				810	0.3%*270000
	Payments (B)			284947	117090
	net before loan (A-B)           C			-229012	8888
	minimum closing cash balance   (D)			40000	40000
	loan (D-C)			269012	31112
	In terms of 1000			270000	32000