I need help with the COGS part.

Calculate accurately what the total cost of goods sold (COGS) per unit should be for the company to generate 50% gross margin on its sales using correct Excel formulas. Consider the following question to guide your response:

Considering the direct material cost per unit already known, how much of this total COGS per unit is available to cover direct labor and manufacturing overhead (MOH) costs?

Posey's Pet Emporium
Selling price	4.99
Less: Target gross margin percentage	[Insert formula]
Cost of goods sold	[Insert formula]
Less: DM cost per unit	[Insert formula]
Additional cost per unit to cover DL and MOH	[Insert formula]

Posey's Pet Emporium
Budget Data
Budgeted selling price	4.99
Sales Volume Budgets
June	33,500
July	32,000
August	35,200
September	35,000
October	34,000
Percentage of Sales
Cash sales	20%
Credit collected in
Month of sale	45%
Following month	52%
Uncollectable	3%
FG (finished goods) inventory goal	15%	following month's sales volume
Target ending inventory DM (direct materials)	20%
Pounds per completed unit of DM	5
Expected cost per pound	0.25
Percentage of Purchase Payments
Month of purchase	40%
Month after purchase	60%
Budgeted purchases - June	20,000
Other cash disbursements per month	80,000
Beginning cash balance - July	57950

Posey's Pet Emporium -SALES FORECAST & CASH RECEIPTS
Sales Forecast	July	August	September	Quarter
Budgeted sales volume	32,000	35,200	35,000	102,200
Budgeted selling price	4.99	4.99	4.99	4.99
Budgeted sales revenue	159,680	175,648	174,650	509,978
Cash Receipts Budget	July	August	September	Quarter
Beginning A/R (June collections)	69,541			69,541
July cash sales	31,936			31,936
July credit sales	57,485	66,427		123,912
August cash sales		35,130		35,130
August credit sales		63,233	73,070	136,303
September cash sales			34,930	34,930
September credit sales			62,874	62874
Total cash receipts	158,961	164,790	170,874	494,625

Posey's Pet Emporium -PRODUCTION & DM PURCHASES
Production Budget	July	August	September	Quarter
Budgeted sales volume	32,000	35,200	35,000	102,200
Add: Target ending FG inventory	5,280	5,250	5,100	15,630
Total units needed	37,280	40,450	40,100	117,830
Less: Beginning FG inventory	4,800	5,280	5,250	15,330
Budgeted units to be produced	32,480	35,170	34,850	102,500
DM Purchases Budget	July	August	September	Quarter
Budgeted units to be produced	32,480	35,170	34,850	102,500
Pounds of DM per unit	5	5	5	5
Total production needs (pounds)	162,400	175,850	174,250	512,500
Add:Target ending DM inventory (pounds)	35,170	34,850	34,000	104,020
Total DM inventory needs (pounds)	197,570	210,700	208,250	616,520
Less: Beginning DM inventory (pounds)	32,480	35,170	34,850	102,500
Budgeted pounds of DM to be purchased	165,090	175,530	173,400	514,020
DM cost per pound	$ 0.25	$ 0.25	$ 0.25	$ 0.25
Total budgeted cost of DM purchases	$ 41,272.50	$ 43,882.50	$ 43,350.00	$ 128,505.00

Posey's Pet Emporium- CASH DISBURSEMENTS BUDGET
DM Cash Disbursements	July	August	September	Quarter
A/P balance from June*	12,000			12,000
July purchases	$ 16,509.00	$ 24,763.50		41,273
August purchases		$ 17,553.00	$ 26,329.50	43,883
September purchases			$ 17,340.00	17,340
Total DM Cash Disbursements	28,509	42,317	43,670	114,495
Other Cash Disbursements	80000	80000	80000	240000
Total Cash Disbursements	108,509	122,317	123,670	354,495

You are the cost accountant at Posey's Pet Emporium tasked with preparing quarterly budgets that determine the cash effects of the company's sales and production-related expenditures. The company uses a calendar year, and it is time to prepare the third-quarter budget. You have the following information: 1. The budgeted selling price for the year is $4.99 per unit. Sales volumes are budgeted as follows for the last month of quarter two, for all of quarter three, and for part of quarter four. 2. Historically, 20% of Posey's sales are cash sales. Of the remaining credit sales, 45% are collected in the month of sale, while 52% are collected the following month. The remainder is deemed uncollectible. 3. Management sets its ending finished goods inventory goal at 15% of the following month's sales volume. The accounting team expects this policy will be met at the beginning of the second quarter. 4. The target ending inventory for Posey's primary direct material is 20% of the following month's production needs. Each completed unit requires five pounds of direct materials at an expected cost of $0.25 per pound. The budgeted production for October is 34,000 . 5. Posey's pays for 40% of its purchases in the month of purchase and 60% the month after purchase. Total budgeted purchases in June are $20,000. 6. Posey's ending cash balance on June 30 was $57,950. 7. Posey's non-production cash disbursements are estimated at $80,000 per month