Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Can I get help with the excel part? with explanations? Broncos Corp. manufactures wooden frames with the bronco medallion for framing the college degrees awarded

Can I get help with the excel part? with explanations?
image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
Broncos Corp. manufactures wooden frames with the bronco medallion for framing the college degrees awarded by Western 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 face, 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 the current year, the Sales Manager provided following estimates of unit sales 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 manufacturing 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 estimated at S15.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 during the month of purchase and remaining 40% is paid in the following month. Budgeted Oak wood purchase for December is $34,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 manufacturing overhead includes $5,000 in depreciation. During January, Bronco Corp. plans to pay $110,000 for a piece of equipment to replace old equipment. Bronco had $61,000 cash 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. Borrowings or any part of the borrowings may be paid off in the month there is excess cash available (Ignore interest on borrowings). otal Sales Revenue (\$) A 8$3300,000.00C$240,000.00D$135,000.00E$114,000.00FG$315,000.00H$270,000.00 Production Budget Budgeted Sales (in units) Add: Required Ending inventory of Fin. Goods Less: Bud. Beginning Inventory of Fin. Goods Budgeted Production (in units) Bud. Production (in units) Oak Wood Purchase Budget Oak Wood per frame (linear feet) Oak wood needed for Production (linear leet) Add: Ending inventory of Oak wood (linear feet) Less: Ben. Inventory of Oak wood (linear feet) Budgeted Oak wood Purchase (in linear feet) Oak wood Cost per foot Total Budgeted Oak wood Purchase (\$) Qud. Production (in units) Other Direct Material Package Purchase Budget December lanuary February March Quarter 1 April Other DM package per frame (in units) Other DM packages needed for Production (units) Add: Ending inventory of other DM packages (units) Lessi Bez. Ifwentory of other DM packages (units) Bud. Purchase of Other DM packages (in units) Dther Direct Naterial Padkape Purchase Butget Bud. Produstion (in erits) December ianuary Folthary March Quarter I Apre Other Dy dackare perfiame (nu)urita) Other DPM 7ackaga reedes for Preduction (unta) Ade Endinz invernogy of orher DM peckades (units) Bus Purntaye of other DM packses / lis onits! Goxt bet bsckont Tolal buat hutchine Cent at elier ord pectages is? Nud Profuction in ufute? orkourser irsme bud bL Haurs reeded fise Drodution ticiote ter trour (\$) hud 0(Cove

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Internal Control Anti Fraud Program Design For The Small Business

Authors: Steve Dawson

1st Edition

1119065070, 978-1119065074

More Books

Students also viewed these Accounting questions