Question

Attaboy sells sports jerseys. The jerseys are manufactured by a supplier that makes them according to Attaboy’s specifications. You are the chief financial officer for Attaboy and you need to prepare a master budget for Year4. Attaboy needs the budget for the year, broken down by quarters.

Here is Attaboy’s balance sheet as of the end of Year3 (which, of course, is the beginning of Year4)

Cash 36,800$

Accounts Receivable 353,000

Inventory 10,000

PP&E 415,000

Accumulated depreciation (200,000)

$614,800

Acct Payable 426,000

Capital stock 150,000

Retained earnings 38,800

Total L&S E 614,800

You have gathered the following information about expected activity:

1.The marketing department provided projected sales for Attaboy. In Quarters 1 and 2, each jersey is expected to sell for $40. A $2 price increase is predicted in Quarter 3 and a $3 increase in Quarter 4.

Quarter 1, Year4: 21,000jerseys

Quarter 2,Year4: 25,000jerseys

Quarter 3,Year4: 23,000jerseys

Quarter 4,Year4: 22,000jerseys

Quarter 1,Year5: 23,000jerseys

2.Attaboy sells all of the merchandise on credit. Historically,Attaboy receives 70% of each quarter's sales during the quarter, 20% in the next quarter, and 10% in the quarter after that. Accounts receivable at 12/31/Year3are expected to be collected as follows:$238,000 in Quarter 1 and $115,000 in Quarter 2.

3.Attaboy estimates that it will have a gross margin percentage of 60% in Year4. They suffered from a shortage of inventory at the end of Year4, so they plan to keep inventory levels much higher in Year4. Inventory levels are budgeted to be 35% of the next quarters cost of goods sold.They pay for inventory purchases in the quarter following the purchase.

4.Variable selling and administrative costs are estimated at $10 per unit sold. Fixed selling and administrative costs are $70,000 per quarter. (This amount includes $3,000 of depreciation per quarter).

5.Attaboy will purchase land for $500,000 in the third quarter, for cash.They hope to begin construction on a flagship facility in Year5.

6.Attaboy pays quarterly dividends of $50,000.The dividends are paid in the same quarter they are declared.

7.Attaboy has to maintain a minimum cash balance of $30,000. Any projected borrowings are assumed to be borrowed at the beginning of the quarter. Payments are made at the end of the following quarters to the extent cash is available. All borrowings and repayments are made in $10,000 increments. Interest is paid at the time of repayment. Interest is calculated at 12% per year (no compounding).

Answer 1

Please hit LIKE button if this helped. For any further explanation, please put your query in comment, will get back to you.
1. Sales Budget with expected Cash Collections:
		Q1	Q2	Q3	Q4	Full Year	Q1 Next
Budgeted Sales in Unit		21000	25000	23000	22000	91000	23000
Sale Price per unit		$               40	$                40	$                42	$                43		$                          43
Budgeted Sales in Dollar		$      840,000	$    1,000,000	$       966,000	$       946,000	$      3,752,000	$                 989,000
	Total Credit Sale	Q1	Q2	Q3	Q4	Full Year	Accounts Receivable
Accounts Receivable, Beginning		$      238,000	$       115,000			$         353,000
Q1 Sale	$          840,000	$      588,000	$       168,000	$         84,000		$         840,000	$                           -
Q2 Sale	$      1,000,000		$       700,000	$       200,000	$       100,000	$      1,000,000	$                           -
Q3 Sale	$          966,000			$       676,200	$       193,200	$         869,400	$                   96,600
Q4 Sale	$          946,000				$       662,200	$         662,200	$                 283,800
Total Cash Collection		$      826,000	$       983,000	$       960,200	$       955,400	$      3,724,600	$                 380,400
2. Purchase Budget with expected cash payment:
		Q1	Q2	Q3	Q4	Full Year	Q1 Next
Budgeted Cost of Goods Sold	40% of Sale	$      336,000	$       400,000	$       386,400	$       378,400	$      1,500,800	$                 395,600
Add: Desired Inventory	35% of Next	$      140,000	$       135,240	$       132,440	138460	138460
Total Needs		$      476,000	$       535,240	$       518,840	$       516,860	$      1,639,260
Less: Beginning Inventory		$      -10,000	$     -140,000	$     -135,240	$     -132,440	$          -10,000
Budgeted Purchases		$      466,000	$       395,240	$       383,600	$       384,420	$      1,629,260
Cash Payment Schedule:		Q1	Q2	Q3	Q4	Full Year	Accounts Payable
Accounts Payable, Beginning		$      426,000				$         426,000
Q1 Pur			$       466,000			$         466,000
Q2 Pur				$       395,240		$         395,240
Q3 Pur					$       383,600	$         383,600
Q4 Pur						$                   -	$                 384,420
Total Cash Payment		$      426,000	$       466,000	$       395,240	$       383,600	$      1,670,840	$                 384,420
4. Selling and Admin Budget:
		Q1	Q2	Q3	Q4	Full Year
Sales in units		21,000	25,000	23,000	22,000	91,000
Variable S&A per unit		$          10.00	$           10.00	$           10.00	$           10.00	$             10.00
Variable S&A Cost		$      210,000	$       250,000	$       230,000	$       220,000	$         910,000
Fixed S&A per month		$        70,000	$         70,000	$         70,000	$         70,000	$         280,000
Total S&A Cost		$      280,000	$       320,000	$       300,000	$       290,000	$      1,190,000
Less: Non Cash Cost-Dep		$         -3,000	$          -3,000	$          -3,000	$          -3,000	$          -12,000
Cash Paid for S&A		$      277,000	$       317,000	$       297,000	$       287,000	$      1,178,000
5. Cash Budget:
		Q1	Q2	Q3	Q4	Full Year
Beginning Balance		$        36,800	$       109,800	$       259,800	$         37,160	$           36,800
Add: Cash Receipt		$      826,000	$       983,000	$       960,200	$       955,400	$      3,724,600
Total Cash Available		$      862,800	$    1,092,800	$    1,220,000	$       992,560	$      3,761,400
Less: Disbursments
Purchases		$      426,000	$       466,000	$       395,240	$       383,600	$      1,670,840
Selling and Admin		$      277,000	$       317,000	$       297,000	$       287,000	$      1,178,000
Dividends		$        50,000	$         50,000	$         50,000	$         50,000	$         200,000
Equipment				$       500,000		$         500,000
Total Disbursments		$      753,000	$       833,000	$    1,242,240	$       720,600	$      3,548,840
Surplus/(Deficiency)		$      109,800	$       259,800	$        -22,240	$       271,960	$         212,560
Financing:
Borrowing				$         60,000		$           60,000
Repayment				$                 -	$        -60,000	$          -60,000
Interest				$             -600	$             -600	$            -1,200
Total Financing		$                -	$                 -	$         59,400	$        -60,600	$            -1,200
Cash Balance Ending		$      109,800	$       259,800	$         37,160	$       211,360	$         211,360