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 $12

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 budgeted unit sales of next QTR

Finished goods inventory, beginning 12,000

Raw materials required to produce one unit 5 pounds

Desired ending inventory of raw materials is 10% of next Qtr production needs

Raw materials inventory, beginning 23,000 pounds

Raw material costs $0.80 per pound

Raw materials purchases are paid 60% in Qtr purchases are made and 40% in Qtr following purchase

Accounts payable for raw materials, beginning balance $81,500

Direct labor cost per hour $15

Direct labor hour per unit 0.2

Variable MOH rate $2

Total fixed MOH $60,000

Variable S&A expense rate $1.80

Minimum cash balance $50,000

Annual Interest rate 12%

BASED on above info what is the EXCEL formulas for the missing info below to get correct answers?

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 ? ? ? ? ?

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!

Q:

Formulas only please in Microsoft Excel. Instructions are listed under the data. 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...

Q:

Chapter 9: 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,...

Q:

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,...

Q:

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...

Q:

Need help answering the worksheet with the info provided Chapter 7: 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...

Q:

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...

Q:

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...

Q:

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,...

Q:

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...

Q:

Accounting help. Basically, complete the cells that have a '?' in them. If you could tell me how you got that answer, I'd greatly appreciate it. Thank you. Data Year 2 Quarter Year 3 Quarter 1 2 3 4...

Q:

The objective of an experiment performed by our students is to determine the effect of pin fins on the thermal resistance between a flat plate and an airstream. A 25.9-mm-square polished aluminum...

Q:

Ames Trading Co. has the following products in its ending inventory . Compute lower of cost or market for inventory applied separately to eachproduct. Product Quantity Mountain bikes Skateboards...

Q:

Machine A First Cost: P 22 , 000 P22,000 Annual Expenses: P 4 , 300 + P 11 , 600 + P 3 , 800 + 3 % 22 , 000 P4,300+P11,600+P3,800+3%22,000 Salvage Value: P 5 , 500 P5,500 Life: 5 years Machine B...

Q:

\f

Q:

1. To what extent do you think the organisations mentioned here would benefit from using the Health and Safety Executives stress management standards as a part of their stress policies?

Q:

describe the Health and Safety Executives approach to stress management.

Q:

3. Do you think that measures of health, safety and wellbeing should be included as a part of the balanced scorecard approach in organisations?