Build_a_Spreadsheet_09_35

Build_a_Spreadsheet_09_35 - April May June Planned...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
DATA INPUT April 10,000 desk-and-chair sets May 12,000 desk-and-chair sets June 15,000 desk-and-chair sets Each desk-and chair set requires: 10 board feet of pine planks 1.5 hours of direct labor Costs for material: $0.50 per board foot $20.00 per hour for direct-hour wages and fringe benefits Division ends each month with enough wood to cover next month's production requirements of : 10% Division ends each month with enough finished-goods inventory to cover next month's sales of: 20% Selling price per set $50 SOLUTION 1. Complete sales budget schedule: Sales budget: April May June Sales (in sets) 10,000 12,000 15,000 Sales price per set $50 $50 $50 Sales revenue $500,000 $600,000 $750,000 2. Complete production budget schedule: Production budget: April May June Sales 10,000 12,000 15,000 Add: Desired ending inventory 2,400 3,000 3,000 Total requirements 12,400 15,000 18,000 Less: Projected beginning inventory 2,000 2,400 3,000 Planned production 10,400 12,600 15,000 3. Complete raw-material purchases schedule: Raw- material purchases:
Background image of page 1
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: April May June Planned production (sets) 10,400 12,600 15,000 Raw material required per set (board feet) 10 10 10 Raw material required for production (board feet) 104,000 126,000 150,000 Add: Desired ending inventory of raw material, in board feet (10% of next month's requirement) 12,600 15,000 16,000 Total requirements 116,600 141,000 166,000 Less: Projected beginning inventory of raw material, in board feet (10% of current month's requirement) 10,400 12,600 15,000 Planned purchases of raw material (board feet) 106,200 128,400 151,000 Cost per board foot $0.50 $0.50 $0.50 Planned purchases of raw material (dollars) $53,100 $64,200 $75,500 4. Complete direct-labor budget schedule: Direct-labor budget: April May June Planned production (sets) 10,400 12,600 15,000 Direct-labor hours per set 1.5 1.5 1.5 Direct-labor hours required 15,600 18,900 22,500 Cost per hour $20.00 $20.00 $20.00 Planned direct-labor cost $312,000 $378,000 $450,000...
View Full Document

Ask a homework question - tutors are online