Prepare a master budget for the three-month period ending June 30, 2012. You MUS
ID: 2453805 • Letter: P
Question
Prepare a master budget for the three-month period ending June 30, 2012. You MUST use formulas in all cells, not constant numbers. That means all cells in your budget must be linked to the data from TAB 1 or completed data from TAB 2. A template has been provided. Include the following detailed budgets:
1. a. A sales budget by month and in total.
b. A schedule of expected cash collections from sales, by month and in total.
c. A merchandise purchases budget in units and in dollars. Show the budget by and in total.
d. A schedule of expected cash disbursements for merchandise purchases, by month and in total.
2. A cash budget. Show the budget by month and in total.
TAB 3/INCOME STATEMENT
1. A budgeted income statement for the three month period ending June 30. Use the contribution approach. You MUST use formulas in all cells, not constant numbers. A partial template has been provided.
TAB 4/BALANCE SHEET
1. A budgeted balance sheet as of June 30. You MUST use formulas in all cells, not constant numbers. A template has been provided.
DATA:
The company desires a minimum ending cash balance each month of $10,000.
The ties are sold to retailers for $10.00 each. Recent and forecasted sales in units are as follows:
January (actual) 25,000 June 60,000
February (actual) 30,000 July 35,000
March (actual) 32,000 August 40,000
April 40,000 September 32,000
May 50,000
The large buildup in sales before and during June is due to Father’s Day. Ending inventories are supposed to equal 80% of the next month’s sales in units. The ties cost the company $6 each.
Purchases are paid as follows: 50% in the month of purchase and the remaining 50% in the following month. All sales are on credit, with no discount, and payable within 15 days. The company has found, however, that only 25% of a month’s sales are collected by month-end. An additional 50% is collected in the following month, and the remaining 25% is collected in the second month following sale. Bad debts have been negligible.
The company’s monthly selling and administrative expenses are as follows:
Variable:
Sales commissions $1.50 per tie
Fixed:
Wages and salaries $22,000
Utilities 14,000
Insurance 1,200
Depreciation 1,500
Miscellaneous 3,000
All selling and administrative expenses are paid during the month, in cash, with the exception of depreciation and insurance expired. Land will be purchased during May for $20,000 cash. The company declares dividends of $12,000 each quarter, payable in the first month of the following quarter.
The company has an agreement with a bank that allows it to borrow in increments if $1,000 at the beginning of each month, up to a total loan balance of$100,000. The interest rate on these loans is 1% per month, and for simplicity, we will assume that interest is not compounded. At the end of the quarter, the company would pay the bank all of the accumulated interest on the loan and as much of the loan as possible (in increments of $1,000), while still retaining at least $10,000 in cash.
Here is the given Balance Sheet
This is the layout it should have. I Have started it and believe I have everything right until I get to 1D and I think it starts to go wrong from there. Thank you!!
Explanation / Answer
SALES BUDGET APRIL MAY JUNE QUARTER Sales in Units 40000 50000 60000 150000 Price per unit 10 10 10 10 Sales Value 400000 500000 600000 1500000 SCHEDULE OF EXPECTED SALES COLLECTIONS FEB MAR APR MAY JUNE JULY AUGUST SEPTEMBER Sales made/expected - Units 30000 32000 40000 50000 60000 35000 40000 32000 Sales made/expected - Value 300000 320000 400000 500000 600000 350000 400000 320000 COLLECTIONS FROM SALES From sales of February 75000 150000 75000 March 80000 160000 80000 April 100000 200000 100000 May 125000 250000 125000 June 150000 300000 150000 Total Collections 335000 405000 500000 MERCHANDISE PURCHASE BUDGET APR MAY JUNE JULY AUGUST Opening Balance -units 32000 32000 40000 48000 28000 32000 Purchase-units 32000 48000 58000 40000 39000 33600 Sales-units 32000 40000 50000 60000 35000 40000 Closing balance-units 32000 40000 48000 28000 32000 25600 Merchandise purchase in Dollars 192000 288000 348000 240000 234000 201600 EXPECTED CASH DISBURSEMENTS FOR MERCHANDISE PURCHASES For Purchases of March 192000 96000 96000 April 288000 144000 144000 May 348000 174000 174000 June 240000 120000 120000 Total monthly payment 240000 318000 294000 CASH BUDGET FOR THE QUARTER APR MAY JUNE RECEIPTS Receipts from Debtors 335000 405000 500000 1240000 Bank Borrowing 12000 47000 59000 Total Receipts 347000 452000 500000 PAYMENTS Payments for merchandise 240000 318000 294000 852000 Sales commission 60000 75000 90000 225000 Wages and Salaries 22000 22000 22000 66000 Utilities 14000 14000 14000 42000 Insurance 0 0 0 0 Miscellaneous 3000 3000 3000 9000 Purchase of Land 20000 20000 Dividend for previous quarter 12000 12000 Repayment of Bank Loan 59000 59000 Payment of Interest on Bank loan 710 710 Total Payments 351000 452000 482710 Surplus/Deficit for the month -4000 0 17290 Opening Balance of Cash 14000 10000 10000 Closing Balance of Cash 10000 10000 27290 BUDGETED INCOME STATEMENT for the quarter Sales Revenues 1500000 Variable expenses Cost of ties 900000 Sales Commission 225000 Total variable 1125000 Contribution 375000 Fixed Costs Wages and Salaries 66000 Utilities 42000 Misc 9000 Insurance 3600 Depreciation Information not available Total fixed cost 117000 INCOME FROM OPERATIONS 258000 Interest 710 Net Income 257290 Less Dividends for the quarter 12000 Income after appropriations 245290