please complete all parts and number 1

ExcelTemplateAssignment_Ch09 (1) - Excel Search AutoSave 01 File Home Insert Page Layout Formulas Data Review View Help DYMO Label QuickBooks G 1 AL B P9.1A Prepare budgeted income statement and supporting budgets Cook Farm Supply Company manufactures and sells a pesticide called Snare. The following - data are available for preparing budgets for Snare for the first 2 quarters of 2017 1. Sales: quarter 1, 40,000 bags; quarter 2, 56,000 bags. Selling price is $60 per bag. 2. Direct materials: each bag of Snare requires 4 pounds of Gumm at a cost of $3,80 per 5 pound and 6 pounds of Tarr at $1.50 per pound. 23. Desired inventory levels: Type of Inventory Snare (bags) Gumm (pounds) Tarr (pounds) January 1 8,000 9,000 14,000 April 1 15,000 10,000 20,000 July 1 18,000 13,000 25,000 44. Direct labor: direct labor time is 15 minutes per bag at an hourly rate of $16 per hour. 15 5. Selling and administrative expenses are expected to be 15% of sales plus $175,000 per quarter. 16 6. Interest Expense is $100,000. 17 7. Income taxes are expected to be 30% of income before income taxes. 18 Your assistant has prepared two budgets: (1) The manufacturing overhead budget shows expected 19 costs to be 125% of direct labor cost, and (2) The direct materials budget for Tarr shows the cost of Tarr 20 purchases to be $297,000 in quarter 1 and $439,500 in quarter 2. 22 Instructions 23 Prepare the budgeted multi-step Income statement for the first 6 months and all required operating budgets by 24 quarters. (Note: Use variable and fixed in the selling and administrative expense budget.) Do not 25. prepare the manufacturing overhead budget or the direct materials budget for Tarr. 26 NOTE: Enter a number in cells requesting a value; enter either a number or a formula in cells with a "?". NS COOK FARM SUPPLY COMPANY Sales Budget For the Six Months Endina.lune 30 2017 AutoSave .00 File Home H 2 Insert Excel TemplateAssignment Cho9 (1) Excel Formulas Data Review View Help S earch DYMO Label Quil Page Layout fo B29 COOK FARM SUPPLY COMPANY Sales Budget For the Six Months Ending June 30, 2017 Quarter 1 Six Months 35 Expected unit sales 36 Unit selling price 37 Total sales Value $60 Value $60 $60 COOK FARM SUPPLY COMPANY Production Budget For the Six Months Ending June 30, 2017 Quarter Six Months ValueValue Value Value 45 Expected unit sales 46 Add: Desired ending finished goods units 47 Total required units 48 Less: Beginning finished goods units 49 Required production units Value Value Months COOK FARM SUPPLY COMPANY Direct Materials Budget - Gumm For the Six Months Ending June 30, 2017 Quarter 56 57 Units to be produced Value Value 58 Direct materials per unit (lbs.) X 4lbs x 4lbs 59 Total pounds needed for production 160 Add: Desired ending direct materials (lbs.) Value Value 61 Total materials required 1 09: 2 9: 49 :11 P9-1AP9.5A CD9 | Ready ? File Home Insert Page Layout Formulas Data Review View Help DYMO Label QuickBooks B29 . 1 x for | C | 0 | SIX Months COOK FARM SUPPLY COMPANY Direct Materials Budget - Gumm For the Six Months Ending June 30, 2017 Quarter 2 57 Units to be produced Value Value 58 Direct materials per unit (lbs.) X 4lbs x 4lbs 59 Total pounds needed for production ? 60 Add: Desired ending direct materials (lbs.) Value Value 61 Total materials required 62 Less: Beginning direct materials (lbs.) Value Value 63 Direct materials purchases 64 Cost per pound X $3.80 x $3.80 la direct materiale purchasee 65 Total cost of direct materials purchases 2 7 7 ? COOK FARM SUPPLY COMPANY Direct Labor Budget For the Six Months Ending June 30, 2017 Quarter 1 2 73 Units to be produced Value Value 74 Direct labor hours per unite X 1/4 X 1/4 75 Total required direct labor hours ? ? 76 Direct labor cost per hour 77 Total direct labor cost ? ? Six Months COOK FARM SUPPLY COMPANY Selling and Administrative Budget For the Six Months Ending June 30, 2017 9: 469-11 P9-1A P9-5A CD | 09: 2 Ready Type here to search @ele Search AutoSave 01 H 2 File Home Insert Excel TemplateAssignment cho9 (1) Excel Formulas Data Review View Help Page Layout DYMO Label QuickB B29 COOK FARM SUPPLY COMPANY Selling and Administrative Budget For the Six Months Ending June 30, 2017 Quarter Six Months 85 Budgeted sales in units Value Value 87 Variable (.15 x sales) 88 Fixed 89 Total Value Value Value Value COOK FARM SUPPLY COMPANY Budgeted Income Statement For the Six Months Ending June 30, 2017 95 Sales revenue 96 Cost of goods sold 97 Gross Profit 98 Selling and administrative expenses Value 99 Income from operations 100 Interest expense Value 101 Income before income tax 102 Income tax expense (30%) 103 Net Income 105 107 Cost per Bag 108 109 Unit Cost Total Cost Element Direct Materials 189: 2 9:4 | 19:11P9-1A Quantity CDO P9-5A Ready # Type here to search @el B29 1X fi G H ? 93 Budgeted Income Statement 94 For the Six Months Ending June 30, 2017 95 Sales revenue 96 Cost of goods sold 97 Gross Profit 98 Selling and administrative expenses 99 Income from operations 100 Interest expense | Value 101 Income before income tax 102 Income tax expense (30%) 103 Net income Cost per Bag Quantity Unit Cost Total Cost Element Direct Materials Gumm Tarr Direct Labor Manufacturing overhead (125% of direct labor cost) Total 4 pounds 6 pounds 1/4 hour Value Value Value 119 1201 Assume that the expected unit sales in Quarter 1 changed to 36,000 bags of Snare. Also assume that the amount of direct material (Gumm) used changed to 5 pounds per bag, and, that the direct labor rate changed to $18 per hour. Revise the budgets and budgeted Income statement to reflect these changes 122 123 124 E9-2 19: 41E9-11 P9-1A P9-5A CD9 Ready Type here to search