Data Year 2 Quarter Year 3 Quarter 1 2 3 4 1 2 Budgeted unit sales 40,000 60,000 100,000 50,000 70,000 80,000 Selling price per unit $8 per unit Accounts receivable, beginning balance $65,000 Sales collected in the quarter sales are made 75 Sales collected in the quarter after sales are made 25 Desired ending finished goods inventory is 30 of the budgeted unit sales of the next quarter Finished goods inventory, beginning 12,000 units Raw materials required to produce one unit 5 pounds Desired ending inventory of raw materials is 10 of the next quarter's production needs Raw materials inventory, beginning 23,000 pounds Raw material costs $0 80 per pound Raw materials purchases are paid 60 in the quarter the purchases are made and 40 in the quarter following purchase Accounts payable for raw materials, beginning balance $81,500 Enter a formula into each of the cells marked with a below Review Problem Budget Schedules Construct the sales budget Year 2 Quarter Year 3 Quarter 1 2 3 4 1 2 Budgeted unit sales Selling price per unit Total sales Construct the schedule of expected cash collections Year 2 Quarter 1 2 3 4 Year Accounts receivable, beginning balance First quarter sales Second quarter sales Third quarter sales Fourth quarter sales Total cash collections Construct the production budget Year 2 Quarter 1 2 1 2 3 4 Year Budgeted unit sales Add desired finished goods inventory Total needs Less beginning inventory Required production Year 3 Quarter Construct the raw materials purchases budget Year 2 Quarter 1 1 2 3 4 Year Required production (units) Raw materials required to produce one unit Production needs (pounds) Add desired ending inventory of raw materials (pounds) Total needs (pounds) Less beginning inventory of raw materials (pounds) Raw materials to be purchased Cost of raw materials per pound Cost of raw materials to be purchased Construct the schedule of expected cash payments Year 2 Quarter 1 2 3 4 Year Accounts payable, beginning balance First quarter purchases Second quarter purchases Third quarter purchases Fourth quarter purchases Total cash disbursements SHOW ALL FORMULAS

The Answer is in the image, click to view ...

Question: Data Year 2 Quarter Year 3 Quarter 1 2 3 4 1 2 Budgeted unit sales 40,000 60,000 100,000 50,000 70,000 80,000 Selling price per

Data	Year 2 Quarter				Year 3 Quarter
	1	2	3	4	1	2
Budgeted unit sales	40,000	60,000	100,000	50,000	70,000	80,000

Selling price per unit	$8	per unit
Accounts receivable, beginning balance	$65,000
Sales collected in the quarter sales are made	75%
Sales collected in the quarter after sales are made	25%
Desired ending finished goods inventory is	30%	of the budgeted unit sales of the next quarter
Finished goods inventory, beginning	12,000	units
Raw materials required to produce one unit	5	pounds
Desired ending inventory of raw materials is	10%	of the next quarter's production needs
Raw materials inventory, beginning	23,000	pounds
Raw material costs	$0.80	per pound
Raw materials purchases are paid	60%	in the quarter the purchases are made
and	40%	in the quarter following purchase
Accounts payable for raw materials, beginning balance	$81,500

Enter a formula into each of the cells marked with a ? below
Review Problem: Budget Schedules

Construct the sales budget	Year 2 Quarter				Year 3 Quarter
	1	2	3	4	1	2
Budgeted unit sales	?	?	?	?	?	?
Selling price per unit	?	?	?	?	?	?
Total sales	?	?	?	?	?	?

Construct the schedule of expected cash collections	Year 2 Quarter
	1	2	3	4	Year
Accounts receivable, beginning balance	?				?
First-quarter sales	?	?			?
Second-quarter sales		?	?		?
Third-quarter sales			?	?	?
Fourth-quarter sales				?	?
Total cash collections	?	?	?	?	?

Construct the production budget	Year 2 Quarter					1	2
	1	2	3	4	Year	?	?
Budgeted unit sales	?	?	?	?	?	?
Add desired finished goods inventory	?	?	?	?	?	?
Total needs	?	?	?	?	?	?
Less beginning inventory	?	?	?	?	?	?
Required production	?	?	?	?	?

						Year 3 Quarter
Construct the raw materials purchases budget	Year 2 Quarter					1
	1	2	3	4	Year	?
Required production (units)	?	?	?	?	?	?
Raw materials required to produce one unit	?	?	?	?	?	?
Production needs (pounds)	?	?	?	?	?
Add desired ending inventory of raw materials (pounds)	?	?	?	?	?
Total needs (pounds)	?	?	?	?	?
Less beginning inventory of raw materials (pounds)	?	?	?	?	?
Raw materials to be purchased	?	?	?	?	?
Cost of raw materials per pound	?	?	?	?	?
Cost of raw materials to be purchased	?	?	?	?	?

Construct the schedule of expected cash payments	Year 2 Quarter
	1	2	3	4	Year
Accounts payable, beginning balance	?				?
First-quarter purchases	?	?			?
Second-quarter purchases		?	?		?
Third-quarter purchases			?	?	?
Fourth-quarter purchases				?	?
Total cash disbursements	?	?	?	?	?

SHOW ALL FORMULAS

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Accounting Questions!

Chapter 8: Excel Case Data Year 2 Quarter Year 3 Quarter 1 2 3 4 1 2 Budgeted unit sales 40,000 60,000 100,000 50,000 70,000 80,000 Selling price per unit $8 per unit Accounts receivable, beginning...

Construct the raw materials purchases budget???? Data Year 2 Quarter Year 3 Quarter 1 2 3 4 1 2 Budgeted unit sales 40,000 60,000 100,000 50,000 70,000 80,000 Selling price per unit $8 per unit...

Chapter 8: Applying Excel Data Year 2 Quarter Year 3 Quarter 1 2 3 4 1 2 Budgeted unit sales 40,000 60,000 100,000 50,000 70,000 80,000 Selling price per unit $8 per unit Accounts receivable,...

Chapter 8: Applying Excel Data Year 2 Quarter Year 3 Quarter 1 2 3 4 1 2 Budgeted unit sales 40,000 60,000 100,000 50,000 70,000 80,000 Selling price per unit $8 Accounts receivable, beginning...

Data Year 2 Quarter Year 3 Quarter 1 2 3 4 1 2 Budgeted unit sales 40,000 60,000 100,000 50,000 70,000 80,000 . Selling price per unit $8 per unit . Accounts receivable, beginning balance $65,000 ....

Hello, I Data Year 2 Quarter Year 3 Quarter 1 ' 2 ' 3 ' e1 ' 1 ' 2 Budgeted unit sales 40,000 60,000 100,000 50,000 70,000 80,000 - Selling price per unit $8 per unit - Accounts receivable, beginning...

m F G Nm B D H 2 3 Data Year 2 Quarter Year 3 Quarter 2 4 3 1 2 5 Budgeted unit sales 40,000 60,000 100,000 50,000 70,000 80,000 6 7 - Selling price per unit $8 per unit 8 - Accounts receivable...

Q. 3 Sterling Corporation manufactures and sells a seasonal product that has peak sales in the third quarter. The following information concerns operations for Year 2the coming yearand for the first...

please answer in an excel sheet, thank you. Chapter 8: (Algo) Applying Excel: Excel Worksheet (Part 1 of 2) Download the Applying cal form and enter formules in all coi that contain question markt...

A student performs an experiment to determine the density of a sugar solution. She obtains the following results: 1.11 g/mL, 1.81 g/mL, 1.95 g/mL, 1.75 g/mL. If the actual value for the density of...

What total amount must be paid on May 4 to settle invoices dated April 20 for $650, April 24 for $790, and April 30 for $465, all with terms 1 /10, n/30?

What is the normal pH of blood?Multiple Choice 7 . 0 to 7 . 2 7 . 3 to 7 . 5 6 . 8 to 7 . 0 7 . 6 to 7 . 8

Garfield Company purchased, as a held-to-maturity investment, $80,000 of the 9%, 5-year bonds of Chester Corporation for $74,086, which provides an 11% return. Prepare Garfields journal entries for...

Atmospheric air enters the heated section of a circular tube at a flow rate of 0.005 kg/s and a temperature of 20C. The tube is of diameter D = 50 mm, and full developed conditions with h = 25 W/m 2 ...

Killroy Company owns a trade name that was purchased in an acquisition of McClellan Company. The trade name has a book value of $3,500,000, but according to GAAP, it is assessed for impairment on an...

Speedys Scooters plans to sell a standard scooter for $55 and a chrome scooter for $70. Speedys purchases the standard scooter for $30 and the chrome scooter for $40. Speedy expects to sell one...

Estimated cost and operating data for three companies for the upcoming year follow: Predetermined overhead rates are computed using the following allocation bases in the three companies: Required:...

Tori and some other childeen experimented with balls to see which ones rolled. First using a piece of cardboard to use as a ramp, they angled it on a block to the floor to test the balls. Then Tori...

Which is the most appropriate statement? a. Corporate Governance has been described by Adrian Cadbury as the way organizations are directed or controlled. b. Corporate Governance has been described...

Insert the missing phrase: a. Corporate governance codes and policies have come to be relied on to re-establish the performance/conformance balance to ensure . . . . . . . . . . . . . . . . . . . . ....

Which is the most appropriate statement? a. The Stewardship concept means directors owe this responsibility to the parties who have a vested interest in the organization. They work for and on behalf...