would you be able to help me with the excel sheet that is attatched? I need to fill in the the blank boxes for p12-36 managerial accounting

TYVA - facts Input Quantities per Unit of Output (per pair of sandals) Regular Deluxe 1.3 yards 1.5 yards 0 2 board ft Input Prices Direct materials: per yard per board foot Direct manufacturing labor: per mfg labor-hour (DMLH) Setup-hours per batch Cloth Wood $5.25 $7.50 $15.00 5 hours 2 hours Direct Materials Inventory (FIFO) Beginning inventory Target ending inventory Cost of beginning inventory 1.c 7 hours 3 hours Cloth 610 yards 386 yards $3,219 Wood 800 board ft 295 board ft $6,060 2,150 units 2,950 units 1.3 1.5 0 2 1.c (continued) 0 bd ft 5,900 bd ft 7,220 yds 5,900 bd ft $3,219 -610 6,610 -800 5,100 $5.25 $7.50 $6,060 34,703 38,250 $44,310 $37,922 Wood 7,220 yds 386 yds 7,606 yds 610 yds 6,996 yds Regular Deluxe Total 1.b $5.25 $7.50 Total Output Units Produced Cost of goods manufactured Cost of goods available for sale Deduct ending finished goods inventory, June 30 (requirement f) Cost of goods sold $614,340 1.f (continued) Beginning finished goods inventory, June 1 Direct materials used (requirement c) Direct manufacturing labor (requirement d) Manufacturing overhead (requirement e) 1.h Cost of Goods Sold Budget For the Month of June, 2013 ( Total nonmanufacturing costs $92,625 ) $82,232 471,000 68,139 Nonmanufacturing Costs Budget For the Month of June, 2013 Marketing and general administrations Shipping $23,250 5,000 pairs / Hrs per setup 40 pairs per shipment X X Direct Materials Total Cloth 8% $66,000 Wood Finished goods $15.00 1,875 Regular Deluxe $67,875 Total ending inventory Direct Mfg Labor Hours per Unit 5 7 Total Hours 10,750 20,650 31,400 Hr Wage Rate $15.00 $15.00 Total $161,250 309,750 $471,000 Total $7.88 $15.00 $105.00 $1.08 $12.60 $1.35 $142.91 Pairs per Hours per batch unit 2 50 0.04 3 50 0.06 Quantity $825,000 Deluxe 3,000 Regular 600 Deluxe 3,600 650 2,950 Unit Costs of Ending Finished Goods Inventory For the Month of June, 2013 Regular Deluxe Cost per Input per Total Input per Unit of input unit output unit output $5.25 1.3 $6.83 1.5 $7.50 0 0.00 2 $15.00 5 75.00 7 $18.00 0.04 0.72 0.06 $1.80 5 9.00 7 $1.35 1 1.35 1 $92.90 $77,192 Cloth Wood Direct manufacturing labor $115,875 Machine Setup Processing Inspection Total Note: 621,371 737,246 Regular 122,906 Deluxe 1.g Machine setup Regular Deluxe Processing Inspection Total Note: Regular 2,000 400 2,400 250 2,150 2,150 2,950 $40,463 $40,463 June-Nonmfg costs Marketing & general admin Shipping per shipment 8% Sales revenue $15.00 # of shipments Manufacturing Overhead Costs Budget For the Month of June, 2013 Total Total Regular Deluxe Total Rate Denominator Activity $18.00 Setup-hours $1.80 DMLHs $1.35 # pairs of sandals $18.00 $18.00 $1.80 43 59 $1.35 2 3 31,400 $1,548 3,186 56,520 5,100 6,885 $68,139 Pairs per Total batch batches 2,150 50 43 2,950 50 59 5,100 Direct Manufacturing Labor Costs Budget For the Month of June, 2013 $36,729 $36,729 Total Revenues $240,000 585,000 $825,000 Product Production Budget For the Month of June, 2012 Budgeted unit sales Add: target ending finished goods inventory Total required units Deduct: beginning finished goods inventory $82,232 Units of finished goods to be produced 5,900 bd ft 295 bd ft 6,195 bd ft 800 bd ft 5,395 bd ft June-Mfg Overhead costs Setup per setup-hour Processing per DMLH Inspection per pair = 1 1.e Revenues Budget For the Month of June, 2013 Units Selling Price 2,000 $120.00 3,000 $195.00 1.f 6,996 5,395 5,000 Deluxe 3,000 pairs $195.00 600 650 $92,625 All Sandals 50 pairs 40 pairs 1.d Direct Material Purchases Budget For the Month of June, 2013 Cloth Physical Units Budget To be used in production Add: Target ending direct material inventory Total requirements Deduct: beginning direct material inventory Purchases to be made Cost Budget Cloth Wood Total 2,795 yds 4,425 yds Total quantity of direct materials to be used Cost Budget Available from beg. Direct materials inventory To be purchased this period Cloth 7,220 Wood 5,900 Direct materials to be used this period Wood Regular 2,000 pairs $120.00 400 250 $23,250 Batch and shipping information One batch of sandals One shipment 1.a Direct Material Usage Budget in Quantity and Dollars For the Month of June, 2013 Cloth Physical Units Budget Direct materials required for Regular Deluxe Sales and Finished Goods Inventory (FIFO) Expected sales in units Total Selling price Target ending inventory in units Beginning inventory in units Beginning inventory in dollars Ending Inventories Budget For the Month of June, 2013 Cost per unit 386 yards 295 bd ft 400 600 Total $5.25 $7.50 $2,026.50 2,212.50 $92.90 $142.91 $37,160 $85,746 $4,239 122,906 $127,145 ATTENTION: The facts of this case are listed at the top of the worksheet above the black bar. All of the schedules for requirement #1 are completed on this worksheet. Statistical and/or financial information pulled directly into a schedule from the fact section are identified in "black" font. Statistical and/or financial information pulled from a schedule are identified in "red" font. Please review each schedule carefully. For this worksheet, you will prepare three budget statements. It is important that you understand the purpose of each schedule, in order to properly analyze the statements. Financial information from all of the schedules, except for 1.b, are necessary to complete the budgeted statements (i.e., Cash, Income Statement, and Balance Sheet). NOTE A: Some statement info will come from textbook & some from schedules TYVA Cash Budget NOTE B: Finish the calculations in this section June 30, 2013 Additional Information provided in problem. Cash Balance, June 1 $9,435 * All sales are on account - this would be Accounts receivable! Add receipts: 1.a 1st month 60% collected 825000 495000 Collections from May accounts receivable 2nd month 38% collected Collections from June accounts receivable 495,000 bad debt 2% not coll. 825000 16500 Total collection from customers 495,000 * All purchases are on account - this would be Accounts payable! Total cash available for needs 504,435 1.c 1st month 80% pay 77192 Deduct cash disbursements: 2nd month 20% pay off bal Direct material purchases in May * All costs are paid in the month incurred. Direct material purchases in June 0 * Cash dividend declared and paid in June Direct manufacturing labor 471,000 1.d Direct Mfg labor (all paid in June) Manufacturing overhead 0 * May 31 taxes owed are paid in June. No inc tax owed in June. Nonmanufacturing costs 0 * Processing & setup costs 70% are paid and 30% depreciated. Taxes 1.e 68139 70% Dividends * Marketing & Gen Admin costs 90% are paid & 10% depreciated. Total disbursements 471,000 1.h 67875 90% Financing (Interest paid) 750 * Monthly interest payments on long-term loan paid monthly. Ending cash balance, June 30 $32,685 150000 6% 12 750 TYVA Budgeted Income Statement For the Month of June, 2013 Revenues (Sales) Cost of goods sold Gross margin Operating (nonmanufacturing) costs Bad debt expense Interest expense (for June) Total expenses Net income % of Sales $825,000 100% 0% 825,000 100% 1.a 1.g 1.h 16,500 750 17,250 $807,750 TYVA Budgeted Balance Sheet 2% 98% % of ttl Assets June 30, 2013 Assets Cash Accounts receivable Less: Allowance for doubtful accounts Inventories Direct materials Finished goods Fixed assets Less: Accumulated depreciation Total Assets Liabilities and Equity Accounts payable Interest payable Long-term debt Common stock Retained earnings Total liabilities and equity $32,685 $0 16,500 A/R -16,500 0 870,000 163,564 5% 825000 40% -2% 0% 706,436 98% $722,621 100% 1.f 1.f Mfg OH NonMfg AccDepr Beg Bal-> 68139 30% 67875 10% 136335 20442 6788 163564 Must equal! $0 750 150,000 0% 0% 21% 0% 1,506,654 208% $1,657,404 229% 77192 20% Beg Bal Dividends Net income 698904 0 $807,750 1506654 PROBLEM 12-36 BUDGETED STATEMENTS