Question

Required information Chapter 8: Applying Excel [LO8-2, LO8-3, LO8-4] The Chapter 8 Form worksheet is to...

Required information Chapter 8: Applying Excel [LO8-2, LO8-3, LO8-4] The Chapter 8 Form worksheet is to be used to create your own worksheet version of the Review Problem in the text. Chapter 8: Applying Excel: Excel Worksheet (Part 1 of 2) Download the Applying Excel form and enter formulas in all cells that contain question marks. For example, in cell B26 enter the formula "= B5". Check your worksheet by changing the budgeted unit sales in Quarter 2 of Year 2 in cell C5 to 75,000 units. The required production for the year should be 274,000 units. The cost of raw materials to be purchased for the year should be $1,106,800, whereas the total cash disbursements for the year should be $1,095,980. If you do not get this answer, find the errors in your worksheet and correct them. Save your completed Applying Excel form to your computer and then upload it here by clicking “Browse.” Next, click “Save.” You will use this worksheet to answer the questions in Part 2.

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 40,000 60,000 100,000 50,000 70,000 80,000
Selling price per unit $8 $8 $8 $8 $8 $8
Total sales $320,000 $480,000 $800,000 $400,000 $560,000 $640,000

Homework Answers

Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
Chapter 13: Applying Excel: Excel Worksheet (Part 1 of 2) Download the Applying Excel form and...
Chapter 13: Applying Excel: Excel Worksheet (Part 1 of 2) Download the Applying Excel form and enter formulas in all cells that contain question marks. For example, in cell C22 enter the formula "= B10". Note: The present value factors could be computed using the built-in Excel function PV, but we recommend using the formulas in Appendix 13B. Verify that your worksheet matches the example in the text. Check your worksheet by changing the discount rate to 10%. The net...
Data                                    Year 2 Quarter     &
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...
Requirement 2: The company has just hired a new marketing manager who insists that unit sales...
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:      Data Year 2 Quarter Year 3 Quarter 1 2 3 4 1 2   Budgeted unit sales 45,000 70,000 110,000 70,000 85,000 90,000   Selling price per unit $7 per unit          Data Year 2 Quarter Year...
Data                                    Year 2 Quarter     &
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...
Exercise 8-10 Production and Direct Materials Budgets [LO8-3, LO8-4] Pearl Products Limited of Shenzhen, China, manufactures...
Exercise 8-10 Production and Direct Materials Budgets [LO8-3, LO8-4] Pearl Products Limited of Shenzhen, China, manufactures and distributes toys throughout South East Asia. Three cubic centimeters (cc) of solvent H300 are required to manufacture each unit of Supermix, one of the company’s products. The company now is planning raw materials needs for the third quarter, the quarter in which peak sales of Supermix occur. To keep production and sales moving smoothly, the company has the following inventory requirements: The finished...
Requirement 2: The company has just hired a new marketing manager who insists that unit sales...
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:      Data Year 2 Quarter Year 3 Quarter 1 2 3 4 1 2   Budgeted unit sales 50,000 70,000 120,000 75,000 80,000 100,000   Selling price per unit $7 per unit                     a. What...
Exercise 8-16 Direct Materials and Direct Labor Budgets [LO8-4, LO8-5] The production department of Zan Corporation...
Exercise 8-16 Direct Materials and Direct Labor Budgets [LO8-4, LO8-5] The production department of Zan Corporation has submitted the following forecast of units to be produced by quarter for the upcoming fiscal year: 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter Units to be produced 11,000 14,000 13,000 12,000 In addition, 11,000 grams of raw materials inventory is on hand at the start of the 1st Quarter and the beginning accounts payable for the 1st Quarter is $6,000. Each unit...
Exercise 8-16 (Algo) Direct Materials and Direct Labor Budgets [LO8-4, LO8-5] The production department of Zan...
Exercise 8-16 (Algo) Direct Materials and Direct Labor Budgets [LO8-4, LO8-5] The production department of Zan Corporation has submitted the following forecast of units to be produced by quarter for the upcoming fiscal year: 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter Units to be produced 9,000 12,000 11,000 10,000 In addition, 15,750 grams of raw materials inventory is on hand at the start of the 1st Quarter and the beginning accounts payable for the 1st Quarter is $5,600. Each...
Exercise 8-3 Direct Materials Budget [LO8-4] Two grams of musk oil are required for each bottle...
Exercise 8-3 Direct Materials Budget [LO8-4] Two grams of musk oil are required for each bottle of Mink Caress, a very popular perfume made by a small company in western Siberia. The cost of the musk oil is $1.80 per gram. Budgeted production of Mink Caress is given below by quarters for Year 2 and for the first quarter of Year 3: Year 2 Year 3 First Second Third Fourth First Budgeted production, in bottles 80,000 110,000 170,000 120,000 90,000...
Problem 8-30 Integration of the Sales, Production, and Direct Materials Budgets [LO8-2, LO8-3, LO8-4] Milo Company...
Problem 8-30 Integration of the Sales, Production, and Direct Materials Budgets [LO8-2, LO8-3, LO8-4] Milo Company manufactures beach umbrellas. The company is preparing detailed budgets for the third quarter and has assembled the following information to assist in the budget preparation: a. The Marketing Department has estimated sales as follows for the remainder of the year (in units): July 38,000 October 28,000 August 86,000 September 55,000 November 14,500 December 15,000 The selling price of the beach umbrellas is $13 per...