how to fill Manufacturing Cost per unit on excel.

template attached

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 $15.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 itself. 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 an 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 eredit 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). 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 $15.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 itself. 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 an 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 eredit 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)