Question

חד H $ $ A B C D E G 193 Cleaning Service 194 You are running a cleaning service. 195 During the winter you also provide snow removal services. 196 Write the Excel formula in the yellow cells below that calculate the appropriate values. 197 198 Receive $150,000 from customers for cleaning services provided. 199 Receive $50,000 from customers for snow removal. 200 Pay $80,000 to workers providing the cleaning services. 201 Pay $20,000 cash for supplies 202 Pay $5,000 in interest on a loan 203 Receive $2000 interest on cash in the bank 204 Pay $5,000 cash on a advertisement in the local newspaper 205 Pay for your office phone bill of $2,000 206 Pay for a PC at the beginning of the year for $3,600 (straight line depreciate - 3 years) 207 Pay $2,000 for accounting services 208 $ $ $ 150,000 50,000 80,000 20,000 5,000 2,000 5,000 2,000 3,600 2,000 $ $ $ $ $

- Please answer the questions using an Excel Formula

- Answers need to be an Excel Formula

Thank you

Answer 1

1. Solution -

A B F G H I J 198 199 200 201 202 с E Revenue $150,000 from Customers for cleaning Service provided Receive $50,000 from customers for snow removal Pay $80,000 to workers providing the cleaning services Pay $20,000 cash for Supplies Pay $5,000 in interest on Loan Receive $2000 interest on cash in the bank Pay $5,000 cash on a advertisement in the local newspaper Pay for your office phone bill of $2,000 Pay for a PC at the beginning of the year for $3,600 (Straight Line Depreciation - 3 Years) Pay $2,000 for Accounting Service $ 150,000 $ 50,000 $ 80,000 $ 20,000 $ 5,000 $ 2,000 $ 5,000 $ 2,000 $ 3,600 $ 2,000 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 Revenue Cost of Revenue SG&A (excluding Depreciation) Depreciation Interest Expense (Income)* Taxes (30% of Pretax Income) $ 200,000 $ 80,000 $ 4,000 $ 1,200 $ 3,000 219 220 221 222 Gross Profit $ 120,000 Earnings before Interest Taxes, Depreciation & Amortisation (EBITDA)* $ 116,000 Earnings before Interest and Taxes (EBIT) $ 114,800 Pretax Income $ 111,800 Net Income $ 78,260 223 224

2. Formula -

A C D E F G H B Revenue $150,000 from Customers for cleaning Service provided Receive $50,000 from customers for snow removal Pay $80,000 to workers providing the cleaning services Pay $20,000 cash for Supplies Pay $5,000 in interest on Loan Receive $2000 interest on cash in the bank Pay $5,000 cash on a advertisement in the local newspaper Pay for your office phone bill of $2,000 Pay for a PC at the beginning of the year for $3,600 (Straight Line Depreciation - 3 Years) Pay $2,000 for Accounting Service 150000 50000 80000 20000 5000 2000 5000 2000 3600 2000 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 =H198+H199 =H200 Revenue Cost of Revenue SG&A (excluding Depreciation) Depreciation Interest Expense (Income)* Taxes (30% of Pretax Income) =H207+H205 =H206/3 =+H202-H203 Gross Profit Earnings before Interest Taxes, Depreciation & Amortisation (EBITDA)* Earnings before Interest and Taxes (EBIT) Pretax Income Net Income =F213-F214 =F220-F215 =F221-F216 =F222-F217 =F223-(F223*30%)

Explantion -

1. Pay $20,000 cash for Supplies -

Supplies Purchased is part of Current Assets in the Balance Sheet. Hence not added in the Income Statement.

2. Depreciation on PC Costing = $3600 for 3 Years

= $3,600/3 = $1,200 for Depreciation