Please do not add or delete columns. Answer in the yellow texts boxes. Tokens indicate what is required in the answer. White cells can be used as work space.

Hansell Company's management wants to prepare budgets for one of its products, duraflex, for July 2013. The firm sells the product for $79.00 per unit and has the following expected sales ( in units) for these months in 2013: April May 4,500 June 5,510 July 5,380 6,000 August 7,100 September 7,200 The production process requires 3.5 pounds of dura-1000 and 1.7 pounds of flexplas. The firm's policy is to maintain an ending inventory each month equal to 10% of the following month's budgeted sales, but in no case less than 490 units. All materials inventories are to be maintained at 5% of the production needs for the next month, but not to exceed 900 pounds. The firm expects all inventories at the end of June to be within the guidelines. The purchase department expects the materials to cost $1.20 per pound and $4.50 per pound for dura-1000 and flexplas, respectively. The production process requires direct labor at two skill levels. The rate for labor at the K102 level is $47.00 per hour and for the K175 level is $19.00 per hour. The K102 level can process one batch of dura-flex per hour; each batch consists of 95 units. The manufacturing of duraflex also requires 0.09 of an hour of K175 workers' time for each unit manufactured. Variable manufacturing overhead is $1,150 per batch plus $73 per direct labor-hour. The company uses an actual cost system with a LIFO cost-flow assumption. In addition to variable overhead, the firm has a monthly fixed factory overhead of $49,500, of which $18,200 is depreciation expense. The firm pays all manufacturing labor and factory overhead when incurred. Hansell Company expects its trial balance on June 30 to be as follows: HANSELL COMPANY Budgeted Balances June 30, 2013 Cash Accounts receivable Allowance for bad debts Inventory Plant, property, and equipment Accumulated depreciation Accounts payable Wages and salaries payable Note payable (short term bank borrowing) Stockholders' equity $40,000 $78,000 $3,260 $23,700 $576,000 $315,000 $94,500 $20,000 $79,200 $205,740 Typically, cash sales represent 19% of sales while credit sales represent 81%. Credit sales terms are 2/10, n/30. Hansell bills customers on the first day of the month following the month of sale. Experience has shown that 57% of the billings will be collected within the discount period, 22% by the end of the month after sales, 16% by the end of the second month after the sale, and 5% will ultimately be uncollectible. The firm writes off uncollectible accounts after 12 months. The purchase terms for materials are 2/15, n/60. The firm makes all payments within the discount period. Experience has shown that 80% of the purchases are paid in the month of the purchase and the remainder are paid in the month immediately following. In addition to variable overhead, the firm has a monthly fixed factory overhead of $49,500, of which $18,200 is depreciation expense. The firm pays all manufacturing labor and factory overhead when incurred. Total budgeted marketing, distribution, customer service, and administrative costs for 2013 are $2,000,000. Of this amount, $1,000,000 is considered fixed and includes depreciation expense of $123,600. The remainder varies with sales. The budgeted total sales for 2013 are $3.960 million. All marketing and administrative costs are paid in the month incurred. The management of Hansell wishes to contribute to charitable organizations 9% of Operating Income. Management desires to maintain an end-of-month minimum cash balance of $40,000. The firm has an agreement with a local bank to borrow its short-term needs in multiples of $900 up to $96,000 at an annual interest rate of 12%. Borrowings are assumed to occur at the beginning of the month. Bank borrowing at July 15 $79,200. Required On the basis of the preceding data and projections, prepare the following budgets: a. Sales budget for July ( in dollars). b. Production budget for July ( in units). c. Production budget for August ( in units). d. Direct materials purchases budget for July ( in pounds). e. Direct materials purchases budget for July ( in dollars). f. Direct manufacturing labor budget for July ( in dollars). g. Prepare the cash budget for July 2013. h. Prepare the budgeted income statement for July 2013. Excel skills: Simple algebra Circular reference What If Analysis: Scenario Manager HANSELL COMPANY Sales Budget For July 2013 Budgeted sales in units Budgeted selling price per unit Budgeted sales HANSELL COMPANY Production Budget (in units) For July 2013 For August 2013 Desired ending inventory Budgeted sales Total units needed Beginning inventory Units to manufacture HANSELL COMPANY Direct Materials Purchases Budget ( in pounds) For July 2013 Direct Materials Dura-1000 Flexplas Materials required for budgeted production Add: Target inventories Total materials requirements Less: Expected beginning inventories Direct materials to be purchased HANSELL COMPANY Direct Materials Purchases Budget ( in dollars) For July 2013 Budgeted Purchases Pounds Expected Purchase Price per Unit Total Rate per Hour Total Dura-1000 Flexplas Budgeted purchases HANSELL COMPANY Direct Manufacturing Labor Budget For July 2013 Direct Labor Class Direct LaborHours per Batch Number of Batches Total Hours K102 K175 Total HANSELL COMPANY Cash Budget For July 2013 Cash balance, beginning Cash flow from operations: July cash sales Collections of receivables from credit sales in June: Within the discount period After the discount period Collections of receivables from credit sales in May Cash Disbursements: Materials purchases: June purchases July purchases Direct manufacturing labor Variable factory overhead batches related hours related Fixed factory overhead Variable marketing, customer services, and administrative expenses Fixed marketing, customer services, and administrative expenses Charitable contributions Total cash flow from operations Investment activities: Purchases of investments and other long- term assets Sales of investments and other long- term assets Financing activities: Interest payments, end of month New borrowing, beginning of month Repayment of existing debt, end of month Cash balance, July 31, 2013 dollars cost # batches hours rate minimum balance HANSELL COMPANY Budgeted Income Statement For July 2013 Sales perentage increments Cost of goods sold, LIFO basis* Gross margin Selling and administrative expenses: Variable Fixed Charitable donations Operating income ( loss) before tax * Actual manufacturing cost per unit; July: Direct materials: lbs cost per lb total cost cost per unit hours cost per hour toal cost cost per unit batches cost per batch total batch cost units produced cost per unit cost per hour total batch hours total cost units produced cost per unit total cost units produced Dura-1000 Flexplas Direct labor: K102 labor K175 labor Factory overhead: Batch- related DLH-related Fixed Cost per unit, units produced in July cost per unit Excel Instructions No additional Excel instructions No pivot tables, no tables/lists, no range names, no charts