PLEASE HELP!

due tonight

the white cells are supposed to have formulas and the blue cells is where my given data goes from the worksheet.

thank you

BRONCO CORPORATION CASE - Preparing Operating Budgets Broncos Corp. manufactures wooden frames with the bronco medallion for framing the college degrees awarded by Westem Michigan University. Each Bronco frame sells for $150 and requires: - 5 linear feet of special Oak wood that costs $4,00 per foot - Other direct material package (each package includes one Bronco Medallion, a glass fice, and a cardboard cutout) that costs $25 each package (bought from an outside supplier) - 30 minutes of labor hours to build (Labor cost averages $15.00 per hour) Bronco Corp. has the following inventory policies: - Ending finished goods inventory should be 25% of next month's sales, - Ending inventory of Oak wood should be 30% of next month's production need. - Ending inventory of other direct material package should be 20% of next month's production need. (the supply chain for the other direct material packages is quite efficient and therefore, company maintains a smaller inventory of these packages as compared to Oak wood inventory) As per the Marketing and Sales department of the Bronco Corp, sales are high in the month of graduation and the month following the graduation during Fall and Spring each year. Therefore, months of December, January, April, and May are considered high demand months. In a November budget meeting of tho current year, the Sales Manager provided following estinates of unit sales for the upeoming months (December current year - May next year): Variable manufacturing overhend is incurted at a rate of $12.50 per frame produced. Annual fixed manufacturing overhead is estimated to be $300,000(505,000 per month) for expected production of 20,000 frumes for the year. Fixed nelling and administrative expenses are eatimated at \$31,000 per month and variable selling and administrative expensen are estimated af 515.00 per unit sold. months of December, January, April, and May are considered high demand months. In a November budget meeting of the current year, the Sales Manager provided following estimntes of unit steles for the upcoming months (December current year - May next year): Variable manufacturing overhead is incurred at a rate of $12.50 per frame produced. Annual fixed ntanufacturing overhead is estimated to be $300,000($25,000 per month) for expected production of 20,000 frames for the year. Fixed selling and administrative expenses are estimated at $31.000 per month and variable selling and administrative expenses are estinated at 515.00 per unit sold. Of its sales each month, 80% is collected in the same month and remaining 20% in the month following the sales. Of the purchase of Oak wood, 60% is paid for duriag the mionth of purchase and remaining 40% is paid in the following month. Budgeted Oak wood purchase for December is 534,200 . Other direct material package purchases are all paid for in the month of purchase. Also, all other operating costs are paid during the month incurred. Monthly fixed manuficturiag overhesd includes $5,000 in depreciation. During January. Bronco Cop. plans to pay $110.000 for a piece of equipment to replace old equipenent. Bronco had $61,000 cash on hand on January 1. The company has a policy to maintain a monthly minimam cash balance of 550,000 . The company may boriow any aetount using the credit line provided by their benk fo pay for deficits and maintaia the minimum reyuired balance of cach. Borrowings of any part of the bomowinger may be paid off in the month there it excess cash avaitible (fenore interest on borrowinge). Requiremente: 1. Uving the information provided above perpuse the following trutgete foe the firat quater March) total for each busiget. a. Kales hadget Bronco had \$61,000 eash on hand on January 1. The company has a policy to maintain a monthly minimum cash balance of $50,000. The company may borrow any amount using the credit line provided by their bank to pay for deficits and maintain the minimum required balance of cash. Botrowings or any part of the borrowings may be paid off in the month there is excess cash available (Ignore interest on borrowings). Requirements: 1. Using the information provided above prepare the following budgets for the first quarter (January - March) for Bronco Corporation. Inclade each month and quarter I (JaniaryMarch) total for each budget. a. Sales budget b. Production budget c. Direct Materials budgets (Hint: You will need TWO Direct Mfaterial Purchater budgets - one for the Oak wood and another for the Direct Material Package.) d. Direct labor budget e. Manufacturing Overheads budget f. Budgeted Cost of Goods Sold (COGS) g. Selling and administrative expenses budget 2. Prepare Bronco's budgeted income statement for quarter 1 (Jantary-March). 3. Prepare the following for Bronco for quarter 1: a. Budgeted cash receipas/collection each moath (including quarter I total) b. Budgeted eash payments each month (including quarter 1 total) c. Cash budget of Bronco for quarter 1 . 4. After completing requirements 13 above revise the budget spreadsheet in a new workabeet to include following changes (you can copy and paste the "Original data") spreadsheet into the "Revised estimates" tab and incocporate following changes): Bronco Copp. is contemplating inereasiag the selling price of the frames by 10% during the high sales months (graduation months) of December- Janaary and Apri--May. The management of Broneo believes that it would affoct unit sales marginally only, thereby, reducing unit sules by 5%6 in those months. How would theie two changes affect the income of Quarter 1 ? Based on the analysis, should Bronce Corp. increase the price of frames tempararily during those high densand months? Complete the Cenclusios tab. 97 Net operatirs income [loss] .03 Prior month's saies collected, 2015 104 Budreted Iotal cash collection/receipt. 109 Bud cavi jiayment towals currens month purch sse[6004) 1111 Bud. Total Cash pymem toward Oakwood purchase 112 Badected Purthase of other DM packoges (5) (all cach) 11. Eud. Cash pwyent towards Dt cost 114 fladerted MDH costs. 115 iess bepreciotion onst (non cash expense) the Mudzemes ska tegicnses 117 buderted Copital Eqenaiture. 11. phidefeted Total Cosh Paymens 119 122. Doginning Const Dalance. S. Preliminary cish taidnte Bertwinet. 12t p-pawinert of Thinapal 24. twingerin easance BRONCO CORPORATION CASE - Preparing Operating Budgets Broncos Corp. manufactures wooden frames with the bronco medallion for framing the college degrees awarded by Westem Michigan University. Each Bronco frame sells for $150 and requires: - 5 linear feet of special Oak wood that costs $4,00 per foot - Other direct material package (each package includes one Bronco Medallion, a glass fice, and a cardboard cutout) that costs $25 each package (bought from an outside supplier) - 30 minutes of labor hours to build (Labor cost averages $15.00 per hour) Bronco Corp. has the following inventory policies: - Ending finished goods inventory should be 25% of next month's sales, - Ending inventory of Oak wood should be 30% of next month's production need. - Ending inventory of other direct material package should be 20% of next month's production need. (the supply chain for the other direct material packages is quite efficient and therefore, company maintains a smaller inventory of these packages as compared to Oak wood inventory) As per the Marketing and Sales department of the Bronco Corp, sales are high in the month of graduation and the month following the graduation during Fall and Spring each year. Therefore, months of December, January, April, and May are considered high demand months. In a November budget meeting of tho current year, the Sales Manager provided following estinates of unit sales for the upeoming months (December current year - May next year): Variable manufacturing overhend is incurted at a rate of $12.50 per frame produced. Annual fixed manufacturing overhead is estimated to be $300,000(505,000 per month) for expected production of 20,000 frumes for the year. Fixed nelling and administrative expenses are eatimated at \$31,000 per month and variable selling and administrative expensen are estimated af 515.00 per unit sold. months of December, January, April, and May are considered high demand months. In a November budget meeting of the current year, the Sales Manager provided following estimntes of unit steles for the upcoming months (December current year - May next year): Variable manufacturing overhead is incurred at a rate of $12.50 per frame produced. Annual fixed ntanufacturing overhead is estimated to be $300,000($25,000 per month) for expected production of 20,000 frames for the year. Fixed selling and administrative expenses are estimated at $31.000 per month and variable selling and administrative expenses are estinated at 515.00 per unit sold. Of its sales each month, 80% is collected in the same month and remaining 20% in the month following the sales. Of the purchase of Oak wood, 60% is paid for duriag the mionth of purchase and remaining 40% is paid in the following month. Budgeted Oak wood purchase for December is 534,200 . Other direct material package purchases are all paid for in the month of purchase. Also, all other operating costs are paid during the month incurred. Monthly fixed manuficturiag overhesd includes $5,000 in depreciation. During January. Bronco Cop. plans to pay $110.000 for a piece of equipment to replace old equipenent. Bronco had $61,000 cash on hand on January 1. The company has a policy to maintain a monthly minimam cash balance of 550,000 . The company may boriow any aetount using the credit line provided by their benk fo pay for deficits and maintaia the minimum reyuired balance of cach. Borrowings of any part of the bomowinger may be paid off in the month there it excess cash avaitible (fenore interest on borrowinge). Requiremente: 1. Uving the information provided above perpuse the following trutgete foe the firat quater March) total for each busiget. a. Kales hadget Bronco had \$61,000 eash on hand on January 1. The company has a policy to maintain a monthly minimum cash balance of $50,000. The company may borrow any amount using the credit line provided by their bank to pay for deficits and maintain the minimum required balance of cash. Botrowings or any part of the borrowings may be paid off in the month there is excess cash available (Ignore interest on borrowings). Requirements: 1. Using the information provided above prepare the following budgets for the first quarter (January - March) for Bronco Corporation. Inclade each month and quarter I (JaniaryMarch) total for each budget. a. Sales budget b. Production budget c. Direct Materials budgets (Hint: You will need TWO Direct Mfaterial Purchater budgets - one for the Oak wood and another for the Direct Material Package.) d. Direct labor budget e. Manufacturing Overheads budget f. Budgeted Cost of Goods Sold (COGS) g. Selling and administrative expenses budget 2. Prepare Bronco's budgeted income statement for quarter 1 (Jantary-March). 3. Prepare the following for Bronco for quarter 1: a. Budgeted cash receipas/collection each moath (including quarter I total) b. Budgeted eash payments each month (including quarter 1 total) c. Cash budget of Bronco for quarter 1 . 4. After completing requirements 13 above revise the budget spreadsheet in a new workabeet to include following changes (you can copy and paste the "Original data") spreadsheet into the "Revised estimates" tab and incocporate following changes): Bronco Copp. is contemplating inereasiag the selling price of the frames by 10% during the high sales months (graduation months) of December- Janaary and Apri--May. The management of Broneo believes that it would affoct unit sales marginally only, thereby, reducing unit sules by 5%6 in those months. How would theie two changes affect the income of Quarter 1 ? Based on the analysis, should Bronce Corp. increase the price of frames tempararily during those high densand months? Complete the Cenclusios tab. 97 Net operatirs income [loss] .03 Prior month's saies collected, 2015 104 Budreted Iotal cash collection/receipt. 109 Bud cavi jiayment towals currens month purch sse[6004) 1111 Bud. Total Cash pymem toward Oakwood purchase 112 Badected Purthase of other DM packoges (5) (all cach) 11. Eud. Cash pwyent towards Dt cost 114 fladerted MDH costs. 115 iess bepreciotion onst (non cash expense) the Mudzemes ska tegicnses 117 buderted Copital Eqenaiture. 11. phidefeted Total Cosh Paymens 119 122. Doginning Const Dalance. S. Preliminary cish taidnte Bertwinet. 12t p-pawinert of Thinapal 24. twingerin easance