I need help with the attached excel file..

MBA684 Module 1 Graded Assignment (Available points=100pts) *You are not allowed to do this assignment with other people. *You are not allowed to discuss this assignment with other people. *Show all of your work for any possible partial credit. *All necessary calculations should be done on this excel file to show how to get your outputs. Then, I can figure out the whole process as to how your answers were obtained. Therefore, simply showing numbers as a result does not earn any point. * Be sure to answer exactly what is asked in the problems. *Save your work file in the following format of the file name: M01_Assignment_Last Name_First Name.xls (x). Any submission through the email will not be accepted. *Absolutely....\"One File Policy!!!\" You have to pack all your answers into this excel file. If you need a word document to write your answer down, please incorporate MS word document into this excel file. Follow these operations to import MS document into the excel file: Insert (from menu bar)>Object...>choose \"Word Document\" from the drop-down list. I II III IV V VI VII 1 2 3 4 1 2 3 1 2 1 2 3 1 2 Available Points 6 6 6 6 7 7 7 6 6 6 6 6 6 6 6 7 100 S wiss Waste (S C W) ompany prepares a draft of its I/ Sfor 2007 at the end of its fiscal year. The depreciation expense is calculated from a plant used in treating sewage . S S company does not have any depreciable assets other than the plant. o, W The plant was purchased at the beginning of the fiscal year, 2007. The cost of the plant was $200,000. Also, an operation manager estimates its useful life as 10 years. The plant falls in 20-year 150% class in MAC for the tax purpose (Assume that this property class requires half-year convention). If necessary, RS refer the corporate income tax schedule to the following corporate tax schedule: Corp. Taxable Income At Least Tax But 100,000) 335,000 10,000,000 34% 113,900 + .34x(Inc > 335,000) 10,000,000 15,000,000 35% 3,400,000 + .35x(Inc > 10,000,000) 15,000,000 18,333,333 38% 5,150,000 + .38x(Inc > 15,000,000) 35% 6,416,667 + .35x(Inc > 18,333,333) $ 0 18,333,333 - Tax Calculation .15x(Inc > 0) $ 7,500 + .25x(Inc > 50,000) 13,750 + .34x(Inc > 75,000) Also, refer to the following tables for the MAC method. RS SW Co. I/S Sales Cost of Good Sold Gross Profit 2007 $1,000,000 366,000 634,000 Selling, General and Administrative Expenses Depreciation Expense* Earnings Before Tax(EBT)** 270,000 20,000 344,000 Tax Expense Net Income 116,960 227,040 *=SW employs Straight-Line Depreciation Method for the purpose of accounting **=Accounting Income Now, S C is going to prepare a tax report. Therefore, the company has to calculate W o. \"taxable income\" . The IRScode requires the firms to follow MAC Sas an acceptable R depreciation method for the tax purpose. The plant falls in 20-year 150% class. 1. Prepare the depreciation schedule for the next 10 years including 2007 (i.e., 2007-2016) using MACRS (Round up to the nearest(whole) dollar). Also, indicate when DB method switches to S-L method. Year Depreciation $ year 1(2007) year 2(2008) year 3(2009) year 4(2010) year 5(2011) year 6(2012) year 7(2013) year 8(2014) year 9(2015) year9(2015) year10(2016) . . . . . . year 21 (2027) 2. What is the taxable income for the year of 2007? 3. How much should the company pay for tax(i.e., tax payable) for 2007? 4. Suppose that the marginal tax rate of the company is 30%. If SW sells the plant at the end of 2009 for $180,000, how much tax should the company pay (or be refunded) for the transaction? Assume that tax rates on capital gain would be the same as that on ordinary income. ( you would need to identify the net value of the plant at the end of 2009) On January 2, Year 1, J ones C orporation purchased a truck for $39,000. The truck has a 5-year estimated life and a $4,000 estimated salvage value. J ones expects to drive the truck 100,000 miles during its useful life. Prepare the depreciation schedule for Year 1 through Year 5 using each of the following depreciation methods; S traight-line method, 200 declining balance method, and S um-of-years-digits method. You have to construct the depreciation schedules to answer this question. Make sure that all of your calculations should be done on the excel formula bar to show how you obtained your answers. * You may use the templates given below to answer the questions. Or, you may answer using our own templates. 1. S-Lmethod Depreciation Basis Depreciation Exp. Year 1 Year 2 Year 3 Year 4 Year 5 Total 2. 200 declining balance method Straight Line Rate (S-L Rate) Declining Balance Rate (DB Rate) Beg NBV DB-rate Depreciation Exp. Year 1 Year 2 Year 3 Year 4 Year 5 3. Sum-of-years-digits method Depreciation Basis Year 1 Year 2 Year 3 Year 4 Year 5 Years to the end of Year 5Depreciation Expenses Sum-of-Years-Digits End NBV J Meier is the sole owner of Meier C ill orp., which provides her only source of income. J has always paid herself entirely by drawling dividends ill from her corporation. A friend suggested that as long as she is earning about what she would have to pay someone else to run the business, she might be better off paying herself a salary instead of dividends, because she would avoid the problem of double taxation. If J company earns ill's $120,000 all of which she will pay to herself, how much will she take home under each method? Assume a corporate tax rate of 30%, a personal tax rate of 25% and a 15% tax on dividends. Sole Proprietorship C-Corporation In 2008, S aratoga C ompany had the following financial data: Operating income $500,000 Interest received $50,000 Interest paid $30,000 Dividend received $100,000 Dividend paid 150,000 Dividend of $100,000 was received from Findlay Inc. which is one of the companies that S aratoga company invest. As of the end of 2008, S aratoga C ompany owns 10% of Findlay, Inc. 1. Using the corporate tax rate table given below, what was the company's tax Liability (just federal corporate income tax) for the year 2008? Corp. Taxable Income At Least Tax But 100,000) 335,000 10,000,000 34% 113,900 + .34x(Inc > 335,000) 10,000,000 15,000,000 35% 3,400,000 + .35x(Inc > 10,000,000) 15,000,000 18,333,333 38% 5,150,000 + .38x(Inc > 15,000,000) 35% 6,416,667 + .35x(Inc > 18,333,333) $ 0 18,333,333 - Tax Calculation .15x(Inc > 0) $ 7,500 + .25x(Inc > 50,000) 13,750 + .34x(Inc > 75,000) 2. What is the marginal tax rate and average tax rate? 1. Tibbs Inc. had the following data for the year ending 12/31/07: Net income = $300; Net operating profit after taxes (NOPAT) = $400; Total assets = $2,500; Short-term investments = $200; Stockholders' equity = $1,800; Total debt = $700; and Total operating capital = $2,300. What was its return on invested capital (ROIC)? 2. Wells Water Systems recently reported $8,250 of sales, $4,500 of operating costs other than depreciation, and $950 of depreciation. The company had no amortization charges, it had $3,250 of outstanding bonds that carry a 6.75% interest rate, and its federal-plus-state income tax rate was 35%. In order to sustain its operations and thus generate sales and cash flows in the future, the firm was required to spend $750 to buy new fixed assets and to invest $250 in net operating working capital. How much free cash flow did Wells generate? 3. HHH Inc. reported $12,500 of sales and $7,025 of operating costs (including depreciation). The company had $18,750 of investor-supplied operating assets (or capital), the weighted average cost of that capital (the WACC) was 9.5%, and the federal-plus-state income tax rate was 40%. What was HHH's Economic Value Added (EVA), i.e., how much value did management add to stockholders' wealth during the year? Zumbahlen Inc. has the following balance sheet. Its EBIT is $300 and tax rate is 40%. Also, the weighted averag cost of capital of Jumbahlen Inc. is 10%. 1. How much total net operating capital does the firm have? 2.Calculate the Economic Value Added (EVA). Cash Short-term investments Accounts receivable Inventory Current assets Gross fixed assets Accumulated deprec. Net fixed assets Total assets 20 Accounts payable 50 Accruals 20 Notes payable 60 Current liabilities 150 Long-term debt 140 Common stock 40 Retained earnings 100 Total common equity 250 Total liab. & equity 30 50 30 110 70 30 40 70 250 Given the following selected information on McMillen's Chocolate, Inc., calculate Cash Flow from Operating Activities for 2012. Show your work. 2011 EAT Depreciation Exp. Dividends Accounts Receivable Inventory Accts. Payable/Accr. Long-Term Debt Common Stock Retained Earnings $ 600,000 100,000 2012 $ 750,000 150,000 400,000 550,000 1,500,000 2,000,000 3,500,000 2,000,000 350,000 500,000 2,300,000 3,000,000 2,200,000 2,500,000 6,150,000 6,350,000