Question

You have just been hired to work for a company that sells whimsical coffee mugs, Spot's Spot, Inc. The company has an exclusive franchise on the distribution of the coffee mugs, and sales have grown so rapidly over the last few years that it has become necessary to add new members to the management team. You have been given responsibility for all planning and budgeting. Your first assignment is to prepare a master budget for the next three months, starting April 1. You are anxious as follows The coffee mugs are sold to retailers for $20 each. Recent and forecasted sales in units are January (actual) $ February (acutal) $ March (acutal)$ April (budget) S May (budget) $ June (budget) $ July (Budget) August (budget) $ Sept (budget) S 23,200 29,200 43,200 68,200 103,200 53,200 33,200 31,200 28,200 The large buildup in sales is due to Mother's and Father's Day, your biggest holidays of the year. Purchases are paid for as follows: 50% in the month of purchase and the remaining 50% in the following month. All sales are on credit. 20% of a month's sales are collected in the month of sale. An additional 70% is collected in the following month, and the remaining 10% is collected in the second month following sale. Bad debts have been negligible. The company's monthly selling and administrative expenses are given below Variable Sales Comissions 4% of sales Fixed: Advertising Rent S Salaries $ Utilities S 360,000 34,000 138,000 15,000 4,600 30,000 Insurance Depreciation $

Purchases are paid for as follows: 50% in the month of purchase and the remaining 50% in the following month. All sales are on credit. 20% of a month's sales are collected in the month of sale. An additional 70% is collected in the following month, and the remaining 10% is collected in the second month following sale. Bad debts have been negligible

Answer 1

Budgeted Income Statement
For the Three months Ended June 30
Sales Revenue		$ 44,92,000.00	=224600*20
Variable Expenses
Cost of Goods Sold	$    12,57,760.00		=224600*5.6
Commissions	$      1,79,680.00		=4492000*4%
Total Variable Expenses		$ 14,37,440.00
Contribution Margin		$ 30,54,560.00
Fixed Expenses :
Salaries and wages	$      4,14,000.00		=138000*3
Utilities	$          45,000.00		=15000*3
Insurance	$          13,800.00		=4600*3
Advertising	$    10,80,000.00		=360000*3
Rent	$      1,02,000.00		=34000*3
Depreciation	$          90,000.00		=30000*3
Total Fixed Expenses		$ 17,44,800.00
Net Operating Income		$ 13,09,760.00
Interest Expense		$            450.00
Net Income		$ 13,09,310.00

Budgeted Balance Sheet
30 june
Assets
Cash	$    11,77,070.00
Accounts Receivable	$    10,57,600.00	=2064000*10%+1064000*80%
Inventory	$          74,368.00	=13280*5.6
Prepaid Insurance	$          15,200.00	=29000-13800
Property and Equipment (net)	$    11,00,000.00	=1110000-90000+56000+24000
Total Assets	$    34,24,238.00
Liabilities and Stockholder's Equity
Accounts Payable, Purchases	$      1,26,560.00
Dividends Payable	$          27,000.00
Loans Payable, Bank	$                        -
Common Stock	$    11,20,000.00
Retained Earnings	$    21,50,678.00	=868368+1309310-27000
Total Liabilities and Stockholder's Equity	$    34,24,238.00

Cash Budget
	April	May	June	Total
Cash Balance Beginning	$           90,000.00	$           66,280.00	$     403,600.00
Add : Cash Receipts from Customers	$         936,000.00	$     1,454,000.00	$ 1,794,000.00	$    4,184,000.00
Total Cash Avaiable	$     1,026,000.00	$     1,520,280.00	$ 2,197,600.00
Less : Cash Disbursements
Merchandise Purchases	$         346,160.00	$         463,120.00	$     359,520.00	$    1,168,800.00
Sales Commission	$           54,560.00	$           82,560.00	$        42,560.00	$        179,680.00
Salaries and Wages	$         138,000.00	$         138,000.00	$     138,000.00	$        414,000.00
Utilities	$           15,000.00	$           15,000.00	$        15,000.00	$          45,000.00
Insurance	$                           -	$                           -	$                       -	$                         -
Advertising	$         360,000.00	$         360,000.00	$     360,000.00	$    1,080,000.00
Rent	$           34,000.00	$           34,000.00	$        34,000.00	$        102,000.00
Dividend Paid	$           27,000.00	$                           -	$                       -	$          27,000.00
Land Purchases	$                           -	$                           -	$                       -	$                         -
Equipment Purchases	$                           -	$           24,000.00	$        56,000.00	$          80,000.00
Depreciation	$                           -	$                           -	$                       -	$                         -
Total Cash Disbursements	$     974,720.00	$     1,116,680.00	$ 1,005,080.00	$    3,096,480.00
Excess (Deficiency) of Cash available over disbursements	$ 51,280.00	$         403,600.00	$ 1,192,520.00
Financing :
Borrowings	$ 15,000.00
Repayment			$     (15,000.00)
Interest			$        (450.00)
Total Financing	$ 15,000.00	$                           -	$     (15,450.00)
Ending Cash Balance	$           66,280.00	$         403,600.00	$ 1,177,070.00

Sales Commission is 4% of Sales i.e. April =1364000*4%, May =2064000*4%, June =1064000*4%
Insurance is paid for in November for full year, therefore no cash disbursement in other months,
Depreciation is non cash expense
Dividend for 1st Quarter paid in April
In April Excess cash is 51280 which is less than minimum cash required of $66000, therefore $15000 is borrowed
In June, since there is excess cash borrowed amount of $15000 is repaid alongwith interest of $450 i.e. ($15000 x 1% x 3)