Buns Bakery: Creating and Using a Master Budget

BUNS BAKERY Cookies only
Please read these instructions before beginning your work.
You may work alone or with a partner. If you work with a partner, turn in one solution with both names on it.
Prepare a master budget for cookies only, using the following information:
Your starting point for Year 2 sales is Q4 of Year 1. The unit volume for Q4 of Year 1 is 65,000 cookies.
PREPARE THE SALES BUDGET
One dozen = one unit for sales.
Plan the unit volume for Year 2 as follows:
•	Q1 unit volume is 20% lower than Q4 of Year 1.Round to nearest whole number.
•	Q2 unit volume is 5% more than Q1 of Year 2.Round to nearest whole number.
•	Q3 unit volume is 5% more than Q2 of Year 2.Round to nearest whole number.
•	Q4 unit volume is 20% more than Q3 of Year 2.Round to nearest whole number.
•	Q1 unit volume for Year 3 is calculated as follows:
o	Take Q3 unit volume and multiply by 105%. Multiply by 105% again. Round the result to zero decimals.Round to nearest whole number.

The selling price per unit is $5.25 for Year 2.
PREPARE THE EXPECTED CASH COLLECTIONS BUDGET
Cash sales represent 10% of sales. Credit sales represent 90% of sales.

For beginning accounts receivable, use the beginning balance sheet found in the Excel file.

The collection schedule for credit sales is as follows:
•	30% is collected in the quarter of the sale.
•	45% is collected in the quarter following the sale.
•	25% is collected in the next quarter.

The beginning A/R balance includes:
•	$57,891 (remaining balance from Q3, Year 1)
•	$194,513 (70% remaining to collect from Q4, Year 1). 
o	Note: to calculate the correct collected in Q1 and Q2 of Year 2, you must first divide $194,513 by 70%. This gives you the total amount of credit sales (from Q4 of Year 1) that will be collected. 30% was collected in Q4 Year 1. 45% will be collected in Q1 Year 2, and the remaining 25% is paid in Q2 of Year 2.
PREPARE THE PURCHASES BUDGET
The desired inventory level for finished goods is 2 days’ of the next quarter- sales in units. (Assume a 90-day quarter). You would calculate this by taking the next quarter- unit sales, dividing by 90 days and multiplying by 2. Round to the nearest unit.
For your beginning inventory, use the beginning balance sheet in the Excel file.
We pay for 85% of our purchases in the current quarter and the remaining 15% is paid in the next quarter. The beginning A/P balance represents the 15% remaining to be paid from December purchases.
PREPARE THE EXPECTED CASH PAYMENTS BUDGET
Assume that we purchase the cookies already baked and packaged from a subcontractor, with every-other-day deliveries for freshness. We pay $4.10 per dozen.
PREPARE THE SELLING & ADMINISTRATIVE BUDGET
Variable selling expenses are for sales commissions at 1% of total sales.
Fixed selling expenses are as follows:
 

In addition, Buns will pay a $5,000 Top Management bonus (additional salary expense) in Q1 Year 2.
PREPARE THE CASH BUDGET
Buns Bakery wants to maintain a cash balance of $25,000 each quarter. They are able to borrow in increments of $1,000. Interest is not compounded. The annual interest rate is 12%. Buns pays back the loan and interest as early as possible - while still maintaining the $25,000 minimum cash balance. 
The beginning cash balance is found on the beginning balance sheet provided in the Excel file.
PREPARE BUDGETED FINANCIAL STATEMENTS
Prepare an income statement for Year 2.
Prepare a balance sheet and statement of retained earnings for Year 2. Use the beginning balance sheet in the Excel file for Property Plant and Equipment, Common Stock, and Retained Earnings, Mortgage Payable. For this budget exercise, we are ignoring mortgage payments to keep it simpler.