to create an area of the spreadsheet for data entry, while the rest of the spreadsheet...

Transcribed Image Text:

to create an area of the spreadsheet for data entry, while the rest of the spreadsheet uses the information entered in those cells to solve the problem. The numbers given should all be put in the data entry area, labeled clearly as to what each represents. The answers to the questions below should also be clearly labeled. Your spreadsheet should work to produce answers to the questions regardless of what numbers are put into the data entry area; for example, if I change expected sales volume in April to 2,000, your spreadsheet should automatically recalculate the budget numbers to yield the appropriate answers at that sales volume. The following information is available for Boostarama Company: Expected sales volume: April May June July 1,430 units 1,800 units 1,750 units 1,000 units Selling price per unit Beginning finished goods inventory, April 1 (210 units) Direct materials needed per unit Total direct labor time per finished unit of product Direct materials cost per pound Direct labor cost per hour Additional information: a. b. C. Font $30 $5,250 5 pounds 2 hours $0.50 $8 The desired finished goods inventory every month is 15% of the next month's sales. Variable manufacturing overhead is applied based on direct labor hours. Variable manufacturing overhead rate is $2.40/DLH. Fixed overhead costs are $3,200 per month. Period expenses are $5,000 per month. REQUIRED (Please show budgets for the individual months as well as for the Quarter): Prepare sales budgets for the second Quarter. 1. 2. Prepare production budgets for the second Quarter. 3. 4. Prepare direct materials budgets for the second Quarter. Prepare direct labor budgets for the second Quarter. 5. Prepare manufacturing overhead budgets for the second Quarter. 6. 7. Prepare ending finished goods budgets for the second Quarter. Prepare cost of goods sold budgets for the second Quarter. 8. Prepare a budgeted income statement for the second Quarter. All cells should be formula-driven except those in the input area. Points will be deducted if the calculations include direct entry of data. Submit your completed Excel file to to create an area of the spreadsheet for data entry, while the rest of the spreadsheet uses the information entered in those cells to solve the problem. The numbers given should all be put in the data entry area, labeled clearly as to what each represents. The answers to the questions below should also be clearly labeled. Your spreadsheet should work to produce answers to the questions regardless of what numbers are put into the data entry area; for example, if I change expected sales volume in April to 2,000, your spreadsheet should automatically recalculate the budget numbers to yield the appropriate answers at that sales volume. The following information is available for Boostarama Company: Expected sales volume: April May June July 1,430 units 1,800 units 1,750 units 1,000 units Selling price per unit Beginning finished goods inventory, April 1 (210 units) Direct materials needed per unit Total direct labor time per finished unit of product Direct materials cost per pound Direct labor cost per hour Additional information: a. b. C. Font $30 $5,250 5 pounds 2 hours $0.50 $8 The desired finished goods inventory every month is 15% of the next month's sales. Variable manufacturing overhead is applied based on direct labor hours. Variable manufacturing overhead rate is $2.40/DLH. Fixed overhead costs are $3,200 per month. Period expenses are $5,000 per month. REQUIRED (Please show budgets for the individual months as well as for the Quarter): Prepare sales budgets for the second Quarter. 1. 2. Prepare production budgets for the second Quarter. 3. 4. Prepare direct materials budgets for the second Quarter. Prepare direct labor budgets for the second Quarter. 5. Prepare manufacturing overhead budgets for the second Quarter. 6. 7. Prepare ending finished goods budgets for the second Quarter. Prepare cost of goods sold budgets for the second Quarter. 8. Prepare a budgeted income statement for the second Quarter. All cells should be formula-driven except those in the input area. Points will be deducted if the calculations include direct entry of data. Submit your completed Excel file to