Look at attached file
Sheet1 Revenue:Year 1Year 2Year 3Year 4Year 5 Sales (Net of returns)500,0001,000,0002,000,0001,500,0001,000,000 Prepayments received during the year from distributors100,000200,000500,000300,000- 0 Amount of the prepayment earned by Mary's during the year50,000130,000278,000200,000- 0 Inventory Expenses: Raw materials(100,000)(220,000)(400,000)(300,000)(180,000) Labor (200,000)(350,000)(500,000)(400,000)(150,000) Indirect costs(70,000)(155,000)(200,000)(160,000)(90,000) Inventory produced (# of bags of candy)740,0001,611,1112,750,0002,150,000840,000 Inventory left at end of year (# of bags of candy)200,000350,000400,000200,00040,000 Other Expenses: Salaries (Management and Sales People)(50,000)(100,000)(175,000)(125,000)(65,000) Mary Sweet's Bonus(20,000)(30,000)(40,000)(30,000)- 0 Repairs and Maintainance(30,000)(30,000)(30,000)(30,000)(30,000) Advertising(10,000)(20,000)(30,000)(10,000)- 0 Utilities(37,000)(72,500)(110,000)(86,000)(42,000) Research and Development(70,000)(60,000)(30,000)(10,000)- 0 Insurance Premiums(12,000)(12,000)(12,000)(12,000)(12,000) Business Meals(10,000)(10,000)(10,000)(10,000)(10,000) Fines for environmental accidents (they are common for Mary's)- 0(5,000)- 0(18,000)- 0 Trademark infringement settlement- 0- 0(50,000)- 0- 0 Mary Sweet owns 100% of a candy business named Mary's Sweet Shoppe Corporation (hereafter Mary's), headquartered in Phoenix, Arizona. Mary's has a December 31 year-end and is an accrual basis taxpayer. On a recent business trip to the U.K. (October 2019), Mary Sweet discovered "Turnip Bites". To her surprise, the combination of turnips, sugar, and a few secret ingredients makes a delicious candy. Mary Sweet believes "Turnip Bites" can be a success in the U.S. and wants to add them to her existing candy business. As the brand name "Turnip Bites" belongs to a U.K. company, she plans to call the candy "Turnip Power Bites" in the U.S. She plans to market "Turnip Power Bites" as a healthy candy. However, she anticipates that "Turnip Power Bites" will probably only be a fad (because people will eventually work out they are not very healthy), and plans to sell all of the assets related to "Turnip Power Bites" after 5 years. She needs your help to calculate the Net Present Value (NPV) of this new line of candy. She wants you to show all your calculations in this workbook (create as many worksheets as you think are necessary, but present all your work in a summary calculation in one worksheet). Here are her plans for the “Turnip Power Bites” project. Unless otherwise noted, assume the cash for all revenues is received in the year listed in the table below and the cash for all expenses is disbursed in the year listed below. In January of 2020 (Year 1) Mary's will borrow $1,000,000 to finance the purchase of assets from a competitor's business. The interest rate on the debt is 5%, but due to Mary's high credit rating the first payment is not due until the end of Year 3 (that is, $50,000 payment each year from Year 3 to Year 5). The competitor's business has the following assets: Building with a market value of $500,000, candy making equipment with a market value of $300,000, and office furniture with a market value of $200,000. As the competitor's business has been operating at a loss, the transaction does not involve the purchase of goodwill. Assume this purchase qualifies as an expansion of Mary's candy business rather than the start of a new business. Mary's anticipates having to purchase additional candy making equipment in May of Year 4 for $50,000 (this equipment will have a five year MACRS life). Mary’s wants to recover the cost of these assets as quickly as possible. In the past, Mary's has also successfully used individual distributors to increase sales. They plan to use distributors to sell "Turnip Power Bites". Distributors prepay for use of the trademark "Turnip Power Bites". See the table below for details about these prepayments. Also, Mary's believes that the U.K. company may sue because "Turnip Power Bites" is so close to the trademarked name "Turnip Bites". They anticipate this lawsuit would be settled in Year 3. Assume Mary's uses an "average cost method" to calculate cost of goods sold. This means they calculate "Inventory available for sale" using the cost of the ending inventory from the prior year plus the costs capitalized into inventory in the current year. To determine cost per unit, Mary's divides "Inventory available for sale" by the total number of units, calculated as the ending inventory units from the prior year plus the inventory units produced in the current year. Using this information, they are able to calculate cost of goods sold. Mary’s elects the recurring item exception and pays any accrued compensation (such as Mary Sweet's bonus) within 2 1/2 months and all other qualifying accruals within 8 1/2 months. Mary's spends too much on asset purchases to qualify for the Section 179 deduction. Assume bonus depreciation will return to 50% after 2022. Also remember, net operating loss carryforwards generated in 2020 can offset 100% of taxable income in future years when they are used. In December of the 5 year, Mary's anticipates selling the building for $450,000, the candy making equipment for $100,000 (which includes the equipment purchased in Year 1 and Year 4), and the office furniture for $50,000. In December of the 5 year, Mary's will also pay $1,000,000 to extinguish the debt. Whatever inventory is have left on hand will be donated to the local food bank (assume Mary’s will receive a deduction for the cost of the donated inventory). Mary's has an ordinary and capital gain marginal tax rate of 21%. You only need to calculate the cash tax expense for the federal income tax (that is, ignore payroll, state, and local taxes). The discount rate is 8%.