Chapter 7: Applying Excel Data April 20,000 Мay 50,000 June July 25,000 Augist 15,000 Budgeted unit sales 30,000 $10 per unit Selling price per unit . Accounts receivable, beginning balance Sales collected in the quarter sales are made Sales collected in the quarter after sales are made Desired ending finished goods inventory is Finished goods inventory, beginning Raw materials required to produce one unit Desired ending inventory of raw materials is Raw materials inventory, beginning Raw material costs Raw materials purchases are paid Direct Labor per Unit Labor Rate 0.05 per DHL $10 per hour 1500 per month $20 per DHL $50,000 per month $20,000 per month $30,000 70% Minimum Labor Hours 25% Variable Manu O/H 20% of the budgeted unit sales of the next month 4,000 units 5 pounds 10% of the next month's production needs 13,000 pounds $0.40 per pound 50% in the month the purchases are made 50% in the month following purchase Fixed Manu O/H Montly Depreciation Variable S&A Expenses Fixed S&A Expenses S&A Depreication $0.50 per unit s $70,000 per month $10,000 per month Other Information: and Accounts payable for raw materials, beginning balance $12,000 LOC of $75,000 @ 16% Minumum Cash Balamce $30,000 Paid Dividend in April Purchased Equipment May Purchased Equipment - June April 1st Cash Balance $49,000 Review Problem: Budget Schedules $143,700 $48,300 Construct the sales budget $40,000 June Аpril Мay Quarter July August Budgeted unit sales Selling price per unit 0 Land $50,000 $200,000 $146,150 $10 Common Stock $0 Retained Earnings on April 1st Total sales $0 $0 $0 S0 S0 $125,000 Equipment Construct the schedule of expected cash collections April Мay June Quarter Accounts receivable, beginning balance April sales $0 $0 May sales sales $0 June sales $0 $ July sales Total cash collections $0 $0 $0 $0 $0 Construct the production budget Quarter Аpril May June July Budgeted unit sales Chapter 7 Form + Total cash collections $0 $0 $0 $0 $0 Construct the production budget April May June Quarter July Budgeted unit sales Add desired finished goods inventory Total needs Less beginning inventory Required production Construct the raw materials purchases budget Аpril May June Quarter 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 5 5 5 5 5 $0.40 $0 $0 $0 $0 Construct the schedule of expected cash payments May Аpril June Quarter Accounts payable, beginning balance April purchases May purchases June purchases July purchases $0 $0 $0 $0 Total cash disbursements $0 $0 $0 $0 Construct the Direct Labor Budget May Аpnil June Quarter Required Production Direct Labor per Unit Labor Hours Required 0.05 Guaranted Labor Hours Labor Hours Paid Hourly Wage Rate $10 Total Direct Labor Hour Costs LO SO tе