Question

You are ceo of a facility and Aetna has offered you a contract. Develop the P&L statement for the following assumptions between and 55,000 procedures in increments of 2,500:

-Aetna is offering you a contract that will pay you a flat fee of $2,750,000 across all ranges of volume

-Supply costs = $42.50 per procedure.

-You get a hazardous disposal fee of $2.50 per procedure

-Fixed costs = $1,200,000

-You will need to hire an additional salaried employee with volumes of 35,000 or more at an annual cost of $47.000

please put volumes 0 - 55,000 in increments of 2,500 into excel file so i could get totals revenues, total fixed costs, etc

Answer 1

PROFIT AND LOSS STATEMENT

	PROCEDURES VOLUME
PARTICULARS	2500	5000	7500	10000	12500	15000	17500	20000	22500	25000	27500	30000	32500	35000	37500	40000	42500	45000	47500	50000	52500	55000
OFFERED FEE(S) (A)	2750000	2750000	2750000	2750000	2750000	2750000	2750000	2750000	2750000	2750000	2750000	2750000	2750000	2750000	2750000	2750000	2750000	2750000	2750000	2750000	2750000	2750000
COST (B)
1. SUPPLY COST - $ 42.50 PP	106250	212500	318750	425000	531250	637500	743750	850000	956250	1062500	1168750	1275000	1381250	1487500	1593750	1700000	1806250	1912500	2018750	2125000	2231250	2337500
2. HAZAROUS DISPOSAL FEE - $ 2.50 PP	6250	12500	18750	25000	31250	37500	43750	50000	56250	62500	68750	75000	81250	87500	93750	100000	106250	112500	118750	125000	131250	137500
3. SALARY OF ADDITIONAL EMPLOYEES - 30,000 * $ 47.00 PP	1410000	1410000	1410000	1410000	1410000	1410000	1410000	1410000	1410000	1410000	1410000	1410000	1410000	1410000	1410000	1410000	1410000	1410000	1410000	1410000	1410000	1410000
TOTAL COST	1522500	1635000	1747500	1860000	1972500	2085000	2197500	2310000	2422500	2535000	2647500	2760000	2872500	2985000	3097500	3210000	3322500	3435000	3547500	3660000	3772500	3885000
CONTRIBUTION MARGIN ( A - B)	1227500	1115000	1002500	890000	777500	665000	552500	440000	327500	215000	102500	-10000	-122500	-235000	-347500	-460000	-572500	-685000	-797500	-910000	-1022500	-1135000
LESS: FIXED EXPENSES	120000	120000	120000	120000	120000	120000	120000	120000	120000	120000	120000	120000	120000	120000	120000	120000	120000	120000	120000	120000	120000	120000
PROFIT AND LOSS	1107500	995000	882500	770000	657500	545000	432500	320000	207500	95000	-17500	-130000	-242500	-355000	-467500	-580000	-692500	-805000	-917500	-1030000	-1142500	-1255000