Question

Please include the breakdown of what figures are going into what cells. Thank you!

3 Cleaning Service You are running a cleaning service. During the winter you also provide snow removal services. Write the Excel formula in the bellow yellow cells that calculate the appropriate values. $ $ $ > Receive $150,000 from customers for cleaning services provided. Receive $50,000 from customers for snow removal. Pay $80,000 to workers Pay $20,000 cash for supplies Pay $5,000 in interest on a loan Receive $2000 interest on cash in the bank Pay $5,000 cash on a advertisement in the local newspaper Pay for your phone bill of $2,000 Pay for a PC at the beginning of the year for $3,600 that you depreciate over 3 years Pay $2,000 for accounting services 150,000 50,000 80,000 20,000 5,000 2,000 5,000 2,000 3,600 2,000 > > > How much are: w No00o w NP00W NHO 00 Revenues Cost of Revenues SG&A (excluding Depreciation) Depreciation Interest Expense (Income) Taxes (30% of Pretax Income) Gross Profit Earnings Before Interest, Taxes, Depreciation & Amortization (EBITDA)* Earnings Before Interest and Taxes (EBIT)* Pretax Income Net Income au Margin) EBIT Margin (Operating Margin) * EBITDA is EBIT plus depreciation and amortization.

Answer 1

we will first select the head for each cost

Cleaning services	Revenue	$150,000
Snow removal	Revenue	$50,000
Pay to worker	Cost for revenue as it is a direct cost related to providing service	$80,000
Cash for supplies	Cost for revenue as it is directly related to providing service	$20,000
Pay interest	Interest cost	$5,000
Received interest	Interest income	$2,000
Advertisement	It is a selling expense related to increase the selling of service SG&A	$5,000
phone bill	It is an administrative expense SG&A	$2,000
Depreciation	depreciation expense per year= cost/useful life =$3600/3 =$1,200	$3,600
Accounting service	it is an administrative expense related to office. SG&A	$2,000

Answer:

	Answer	Explanation
Revenues	$200,000	$150,000+$50,000
Cost of revenues	$100,000	$80,000+20,000
SG&A	$9,000	$5,000+$2,000+$2,000
Depreciation	$1,200
Interest expense	$3,000	($5,000-$2,000)
Taxes(30%)	$26,040	(200,000-100,000-9,000-1,200-3,000)*30%
Gross profit	$100,000	(Revenue-Cost of Revenue)($200,000-$100,000)
EBITDA	$91,000	($200,000-100,000-90,000)
EBIT	$89,800	($91,000-$1200)(EBITDA - Depreciation)
pre tax income	$86,800	(200,000-$100,000-$9,000-$1,200-$3,000)
Net income	$60,760	(pre tax income-taxes)($86,800-$26,040)
EBIT margin	45%	(EBIT/REVENUE)($89,800/200,000)