Question

The Chapter 8 Form worksheet is to be used to create your own worksheet version of the Review Problem in the text. 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:
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,880 $7 65,280 128,880 65,082 85,880 95,280 1 Chapter 8: Applying Excel 3 Data Year 3 Quarter 5 Budgeted unit sales 50.000 65.000 120.000 65.000 85.000 95.000 S $ 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 - Raw materials required to produce one unit 14 . Desired ending inventory of raw materials is 15 - Raw materials inventory, beginning c . 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 16 $ 5

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			$630,000.00	$210,000.00	$840,000.00
Cash received for Q4 Sale				$341,250.00	$341,250.00
Budgeted Cash Collection	$327,500.00	$428,750.00	$743,750.00	$551,250.00	$2,051,250.00

Solution b:

Production Budget
Particulars	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Year	Quarter 1, next year
Budgeted sales unit	50000	65000	120000	65000	300000	85000
Add: ending inventory (30% of next quarter sales)	19500	36000	19500	25500	25500	28500
Less: Beginning inventory	12000	19500	36000	19500	12000	25500
Estimated production unit	57500	81500	103500	71000	313500	88000

Solution c:

Budgeted Cost of raw material purchases
Particulars	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Year
Budgeted Production units	57500	81500	103500	71000	313500
Raw material per unit (In Pounds)	5	5	5	5	5
Total requirement of raw materials	287500	407500	517500	355000	1567500
Add: Desired ending inventory (10% of next quarter production needs)	40750	51750	35500	44000	44000
Less: Beginning inventory	23000	40750	51750	35500	23000
Budgeted purchase units of raw material (In Pounds)	305250	418500	501250	363500	1588500
Raw material cost per pound	$0.80	$0.80	$0.80	$0.80	$0.80
Budgeted cost of purchases	$244,200.00	$334,800.00	$401,000.00	$290,800.00	$1,270,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	$146,520.00	$97,680.00			$244,200.00
Q2 Purchases		$200,880.00	$133,920.00		$334,800.00
Q3 Purchases			$240,600.00	$160,400.00	$401,000.00
Q4 Purchases				$174,480.00	$174,480.00
Total payments	$228,020.00	$298,560.00	$374,520.00	$334,880.00	$1,235,980.00

Solution e:

Yes, this is a potential problem because desired production for quarter 3 is more than 90000 units.