Question

Requirement 2: The company has just hired a new marketing manager who insists that unit sales can be dramatically increased by dropping the selling price from $8 to $7. The marketing manager would like to use the following projections in the budget: Year 2 Quarter Year 3 Quarter Data Budgeted unit sales Selling price per unit 50, eee $7 65, eee 110,eee 70, eee 90, eee 180, eee 1 Chapter 8: Applying Excel 3 Data Year 3 Quarter 5 Budgeted unit sales 50.000 65.000 110.000 70.000 90.000 100.000 S $ 65 on 7 . Selling price per unit 8 Accounts receivable, beginning balance 9 . Sales collected in the quarter sales are made 10 • Sales collected in the quarter after sales are made 11 . Desired ending finished goods inventory is 12 Finished goods inventory, beginning 13. Raw materials required to produce one unit 14 Desired ending inventory of raw materials is 15 • Raw materials inventory, beginning 16 • Raw material costs 17 . Raw materials purchases are paid 18 and 19 Accounts payable for raw materials, beginning balance 7 per unit 65,000 75% 25% 30% of the budgeted unit sales of the next quarter 12,000 units 5 pounds 10% of the next quarter's production needs 23.000 pounds 0.80 per pound 60% in the quarter the purchases are made 40% in the quarter following purchase 81,500 s a. What are the total expected cash collections for the year under this revised budget? Expected cash collections for the year the year

Answer 1

Solution a:

Schedule of expected cash collection
Particulars	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Year
Accounts receivables beginning	$65,000.00				$65,000.00
Cash received for Q1 Sale	$262,500.00	$87,500.00			$350,000.00
Cash received for Q2 Sale		$341,250.00	$113,750.00		$455,000.00
Cash received for Q3 Sale			$577,500.00	$192,500.00	$770,000.00
Cash received for Q4 Sale				$367,500.00	$367,500.00
Budgeted Cash Collection	$327,500.00	$428,750.00	$691,250.00	$560,000.00	$2,007,500.00

Solution b:

Production Budget
Particulars	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Year	Quarter 1, next year
Budgeted sales unit	50000	65000	110000	70000	295000	90000
Add: ending inventory (30% of next quarter sales)	19500	33000	21000	27000	27000	30000
Less: Beginning inventory	12000	19500	33000	21000	12000	27000
Estimated production unit	57500	78500	98000	76000	310000	93000

Solution c:

Budgeted Cost of raw material purchases
Particulars	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Year
Budgeted Production units	57500	78500	98000	76000	310000
Raw material per unit (In Pounds)	5	5	5	5	5
Total requirement of raw materials	287500	392500	490000	380000	1550000
Add: Desired ending inventory (10% of next quarter production needs)	39250	49000	38000	46500	46500
Less: Beginning inventory	23000	39250	49000	38000	23000
Budgeted purchase units of raw material (In Pounds)	303750	402250	479000	388500	1573500
Raw material cost per pound	$0.80	$0.80	$0.80	$0.80	$0.80
Budgeted cost of purchases	$243,000.00	$321,800.00	$383,200.00	$310,800.00	$1,258,800.00

Solution d:

Schedule of expected cash disbursement for merchandise purchases
Particulars	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Year
Accounts Payable, Beginning	$81,500.00				$81,500.00
Q1 Purchases	$145,800.00	$97,200.00			$243,000.00
Q2 Purchases		$193,080.00	$128,720.00		$321,800.00
Q3 Purchases			$229,920.00	$153,280.00	$383,200.00
Q4 Purchases				$186,480.00	$186,480.00
Total payments	$227,300.00	$290,280.00	$358,640.00	$339,760.00	$1,215,980.00

Solution e:

Yes, this is potential problem as required production in quarter 3 is exceeding 80000 units