Ethan and Mary Varela are married. Ethan is employed as an engineer for a large multi-national company, and Mary operates a small, unincorporated business selling dental supplies and providing consulting services. She rents a small office near their residence. Ethan and Mary come to your office to seek assistance with certain aspects of their 2019 federal income tax return. They provide you with the following information: 1. Ethan's 2019 paycheck showed gross income of $104,900. 2. Mary's business consists of the following: a. Mary uses the accrual method of accounting for her business. b. Mary's sales from her business generated gross receipts of $210,000 in 2019. c. Additionally, she received $35,000 for services rendered in 2019. d. She also received $75,000 on accounts receivable from services rendered last year. e. Last year she started a new monthly service she was offering to her clients. In 2017 she received $105,000 for services to be provided monthly from July 2018 through June 2020. f. In November 2019 she also received $147,000 for consulting services to be provided monthly from November 2019 through October 2021. g. During 2019, she wrote checks for $170,120 related to her business. An Excel spreadsheet that includes a detailed list of Mary's expenditures (and a template for computing tax depreciation) is on a separate document. 3. Additional business-related information: a. She received $26,000 on March 16, 2019, from the sale of an old machine (Machine # 1) used in her business (the machine was purchased in February 2016). 4. Mary began her business in 2016 and purchased several assets during that year. She did not elect Section 179 expensing (and she elected out of bonus depreciation) at that time because her business was just beginning (with little cash inflow) and her accountant advised her that it would be better to stretch out the deductions over the depreciable lives of the assets. The following assets, purchased in 2016, are used 100% for business and have been depreciated using MACRS: a. Machine # 1 purchased in February 2016 for $40,000 (7-year property). b. Machine # 2 purchased in April 2016 for $18,000 (7-year property). c. Office computer purchased in August 2016, for $10,000 (5-year property). 5. In April 2017, Mary purchased a copying machine to be used exclusively for the business. She did not elect Section 179 or bonus depreciation for this asset. The cost of the mach/1.0 ine was $40,000. She financed the entire purchase price and has been making $610/month payments since May 2017. Of the total payments made during 2019, $3,100 was for interest. 6. In November 2019, she purchased a new Machine (#3) for $68,000. She did not elect either Section 179 or bonus depreciation on this asset. You can assume she acquired no additional assets during 2019. She paid $20,000 down and financed the balance. She has been making payments of $925/month since November 2019. Of the total payments made during 2019, $625 was for interest. 7. The Jones' investment activities during the year resulted in the following items of income (or loss): Interest received on Ocean Trst Savings Bank account $1,250 b. Interest received on State of Florida Water District bonds $600 C. Qualified dividends received on Sterling International stock $1,600 a. 8. Ethan' father died during the year. Ethan and Mary collected the $200,000 face value from a life insurance policy on Ethan's father. 9. Mary's parents gave $20,000 to Mary, and $12,000 to Ethan. 10. Ethan has a weekly poker game in his basement every Saturday. He charges an admission ($300 total per week) and has determined that his share of the monthly electric bills is $80 per month. He also incurs costs of $50 related to this illegal business each week. 11. Ethan also sells weed to his neighbors. He makes $500 per week and has expenses of $100 per week and has a cost of goods sold of $300 per week. 12. During the year, the Jones' acquired the following shares of Company A common stock: January 17 February 20 April 17 July 23 September 17 October 1 November 30 December 6 10 shares 30 shares 30 shares 40 shares 20 shares 10 shares 20 shares 40 shares Total paid $300 Total paid $600 Total paid $450 Total paid $900 Total paid $320 Total paid $420 Total paid $600 Total paid $860 They use the first-in, first-out (FIFO) method for calculating the cost of their shares. They made the following sales during the year: June 15 - Sold 50 shares for $1,300 December 20 - Sold 70 shares for $2,000 . 13. Mary is unsure how to acquire a new asset for her business. She has two options: a. For the first option-she can buy it on January 1, 2021 paying a total of $20,000 ($5,000 down and financing the remaining $15,000 with 10%/year interest payments and a final balloon payment of $15,000 at the end of year 4. The property would be 5-year MACRS property and there would be no 179 or bonus depreciation taken). b. The second option being considered is to lease the asset on January 1, 2021 for $7500/year for 5 years. For all years, the tax rate that they will pay will be 25% and she uses a discount rate of 7%. Required: Please show ALL work in Excel! I would like to see the formulas used in calculations (i.e., no hardcoding). Identify the following: 1. Total Taxable Income of Ethan and Mary (please explain why things are or are not included as well as items that would be taxed at a different rate) 2. Total Taxable Income of Mary's business (please explain why things are or are not included) 3. Total Depreciation of the assets in Mary's business (Please complete worksheet) 4. Answer to decision whether to lease or purchase the new asset. (Please show work) Amount Explanation Taxable Income 1) Paycheck 2) Business Income 7) Income from Investment Activities a) Interest b) Interest c) Dividends 8) Life insurance proceeds 9) Gift from parents 10) Poker Business a) Income b) Deductible Expenses c) Nondeductible Expenses 11) Weed Business a) Income b) Deductible Expenses c) Nondeductible Expenses 12) Short-Term Capital Gain from Sale of Common Stock Total Taxable Income $ Income: Amount Explanation $ Gross Receipts Accounts Receivable Total Income Deductible Expenses: Purchase of Office Supplies Down Payment on Machine 3 Printer Payments Advertising Telephone and Utilities Copy Machine Payments Office Rent Liabilities and Property Insurance Routine Repairs Wages Paid Employer Payroll Taxes Business Meals Business Entertainment Estimated Federal Tax Payments Contribution to Political Campaign Contribution to NLP Occupational License Depreciation Total Deductible Total Business Income $ A B C D E F G H J K L M z o R S 1 2 3 Client: Mary Varela Business Schedule: Property, plant and equipment Date: December 31, 2019 4 5 6 2016 2017 2018 MACRS MACRS MACRS 2019 deductions Section 179 Bonus MACRS Adjusted basis 7 8 9 Property Machine # 1 Machine #2 Office computer Copying machine Machine #3 Acquisition Date 2/1/16 4/1/16 8/1/16 4/1/17 11/1/19 Tax depreciation Cost Method Class 40,000 MACRS 200% DB 7 18,000 MACRS 200% DB 10,000 MACRS 200% DB 5 40,000 MACRS 200% DB 5 68,000 MACRS 200% DB 7 10 11 12 13 14 15 16 17 18 19 Additional notes: Machine #1 was sold on March 16, 2019. 20 21 22 Lease Option Macrs Year Tax Savings (25%) Depreciation 0 Annual lease payment Tax Savings (7500 * 25%) After-tax annual payment 1 2 3 4 Present value of year 0 payment Present value of years 1-4 payment After-tax cost of rent option 5 Total Year Initial Payment Interest Cost Recovery Deductions Interest Deductions Net Cash Flow Discount Factor NPV Total NPV A B C D 1 2. 3 4 5 Client: Mary Varela Business Schedule: Cash disbursements Date: December 31, 2019 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 Description Purchases of office supplies Down Payment on Machine #3 Machine #3 payments Advertising Telephone & utilities Copy Machine payments Office rent Liability & property insurance Routine repairs made Wages paid to part-time employees Employer portion of Payroll taxes (for part-time employees) Business Meals (with customers) Business Entertainment expenses Estimated federal tax payments Contribution to political campaign of friend who is running for city council Contibution to National Libertarian Party Occupational license Total cash disbursements Amount 17,400 20,000 1,850 7,200 2,500 7,320 24,000 7,500 2,000 40,000 6,350 4,400 8,000 15,000 3,000 2,000 1,600 $170,120 23 24 25 26 Ethan and Mary Varela are married. Ethan is employed as an engineer for a large multi-national company, and Mary operates a small, unincorporated business selling dental supplies and providing consulting services. She rents a small office near their residence. Ethan and Mary come to your office to seek assistance with certain aspects of their 2019 federal income tax return. They provide you with the following information: 1. Ethan's 2019 paycheck showed gross income of $104,900. 2. Mary's business consists of the following: a. Mary uses the accrual method of accounting for her business. b. Mary's sales from her business generated gross receipts of $210,000 in 2019. c. Additionally, she received $35,000 for services rendered in 2019. d. She also received $75,000 on accounts receivable from services rendered last year. e. Last year she started a new monthly service she was offering to her clients. In 2017 she received $105,000 for services to be provided monthly from July 2018 through June 2020. f. In November 2019 she also received $147,000 for consulting services to be provided monthly from November 2019 through October 2021. g. During 2019, she wrote checks for $170,120 related to her business. An Excel spreadsheet that includes a detailed list of Mary's expenditures (and a template for computing tax depreciation) is on a separate document. 3. Additional business-related information: a. She received $26,000 on March 16, 2019, from the sale of an old machine (Machine # 1) used in her business (the machine was purchased in February 2016). 4. Mary began her business in 2016 and purchased several assets during that year. She did not elect Section 179 expensing (and she elected out of bonus depreciation) at that time because her business was just beginning (with little cash inflow) and her accountant advised her that it would be better to stretch out the deductions over the depreciable lives of the assets. The following assets, purchased in 2016, are used 100% for business and have been depreciated using MACRS: a. Machine # 1 purchased in February 2016 for $40,000 (7-year property). b. Machine # 2 purchased in April 2016 for $18,000 (7-year property). c. Office computer purchased in August 2016, for $10,000 (5-year property). 5. In April 2017, Mary purchased a copying machine to be used exclusively for the business. She did not elect Section 179 or bonus depreciation for this asset. The cost of the mach/1.0 ine was $40,000. She financed the entire purchase price and has been making $610/month payments since May 2017. Of the total payments made during 2019, $3,100 was for interest. 6. In November 2019, she purchased a new Machine (#3) for $68,000. She did not elect either Section 179 or bonus depreciation on this asset. You can assume she acquired no additional assets during 2019. She paid $20,000 down and financed the balance. She has been making payments of $925/month since November 2019. Of the total payments made during 2019, $625 was for interest. 7. The Jones' investment activities during the year resulted in the following items of income (or loss): Interest received on Ocean Trst Savings Bank account $1,250 b. Interest received on State of Florida Water District bonds $600 C. Qualified dividends received on Sterling International stock $1,600 a. 8. Ethan' father died during the year. Ethan and Mary collected the $200,000 face value from a life insurance policy on Ethan's father. 9. Mary's parents gave $20,000 to Mary, and $12,000 to Ethan. 10. Ethan has a weekly poker game in his basement every Saturday. He charges an admission ($300 total per week) and has determined that his share of the monthly electric bills is $80 per month. He also incurs costs of $50 related to this illegal business each week. 11. Ethan also sells weed to his neighbors. He makes $500 per week and has expenses of $100 per week and has a cost of goods sold of $300 per week. 12. During the year, the Jones' acquired the following shares of Company A common stock: January 17 February 20 April 17 July 23 September 17 October 1 November 30 December 6 10 shares 30 shares 30 shares 40 shares 20 shares 10 shares 20 shares 40 shares Total paid $300 Total paid $600 Total paid $450 Total paid $900 Total paid $320 Total paid $420 Total paid $600 Total paid $860 They use the first-in, first-out (FIFO) method for calculating the cost of their shares. They made the following sales during the year: June 15 - Sold 50 shares for $1,300 December 20 - Sold 70 shares for $2,000 . 13. Mary is unsure how to acquire a new asset for her business. She has two options: a. For the first option-she can buy it on January 1, 2021 paying a total of $20,000 ($5,000 down and financing the remaining $15,000 with 10%/year interest payments and a final balloon payment of $15,000 at the end of year 4. The property would be 5-year MACRS property and there would be no 179 or bonus depreciation taken). b. The second option being considered is to lease the asset on January 1, 2021 for $7500/year for 5 years. For all years, the tax rate that they will pay will be 25% and she uses a discount rate of 7%. Required: Please show ALL work in Excel! I would like to see the formulas used in calculations (i.e., no hardcoding). Identify the following: 1. Total Taxable Income of Ethan and Mary (please explain why things are or are not included as well as items that would be taxed at a different rate) 2. Total Taxable Income of Mary's business (please explain why things are or are not included) 3. Total Depreciation of the assets in Mary's business (Please complete worksheet) 4. Answer to decision whether to lease or purchase the new asset. (Please show work) Amount Explanation Taxable Income 1) Paycheck 2) Business Income 7) Income from Investment Activities a) Interest b) Interest c) Dividends 8) Life insurance proceeds 9) Gift from parents 10) Poker Business a) Income b) Deductible Expenses c) Nondeductible Expenses 11) Weed Business a) Income b) Deductible Expenses c) Nondeductible Expenses 12) Short-Term Capital Gain from Sale of Common Stock Total Taxable Income $ Income: Amount Explanation $ Gross Receipts Accounts Receivable Total Income Deductible Expenses: Purchase of Office Supplies Down Payment on Machine 3 Printer Payments Advertising Telephone and Utilities Copy Machine Payments Office Rent Liabilities and Property Insurance Routine Repairs Wages Paid Employer Payroll Taxes Business Meals Business Entertainment Estimated Federal Tax Payments Contribution to Political Campaign Contribution to NLP Occupational License Depreciation Total Deductible Total Business Income $ A B C D E F G H J K L M z o R S 1 2 3 Client: Mary Varela Business Schedule: Property, plant and equipment Date: December 31, 2019 4 5 6 2016 2017 2018 MACRS MACRS MACRS 2019 deductions Section 179 Bonus MACRS Adjusted basis 7 8 9 Property Machine # 1 Machine #2 Office computer Copying machine Machine #3 Acquisition Date 2/1/16 4/1/16 8/1/16 4/1/17 11/1/19 Tax depreciation Cost Method Class 40,000 MACRS 200% DB 7 18,000 MACRS 200% DB 10,000 MACRS 200% DB 5 40,000 MACRS 200% DB 5 68,000 MACRS 200% DB 7 10 11 12 13 14 15 16 17 18 19 Additional notes: Machine #1 was sold on March 16, 2019. 20 21 22 Lease Option Macrs Year Tax Savings (25%) Depreciation 0 Annual lease payment Tax Savings (7500 * 25%) After-tax annual payment 1 2 3 4 Present value of year 0 payment Present value of years 1-4 payment After-tax cost of rent option 5 Total Year Initial Payment Interest Cost Recovery Deductions Interest Deductions Net Cash Flow Discount Factor NPV Total NPV A B C D 1 2. 3 4 5 Client: Mary Varela Business Schedule: Cash disbursements Date: December 31, 2019 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 Description Purchases of office supplies Down Payment on Machine #3 Machine #3 payments Advertising Telephone & utilities Copy Machine payments Office rent Liability & property insurance Routine repairs made Wages paid to part-time employees Employer portion of Payroll taxes (for part-time employees) Business Meals (with customers) Business Entertainment expenses Estimated federal tax payments Contribution to political campaign of friend who is running for city council Contibution to National Libertarian Party Occupational license Total cash disbursements Amount 17,400 20,000 1,850 7,200 2,500 7,320 24,000 7,500 2,000 40,000 6,350 4,400 8,000 15,000 3,000 2,000 1,600 $170,120 23 24 25 26