the first 3 photos are the problem, the last 5 photos are the template.

Excel Master It! Problem xlsx Using Excel to find the marginal tax rate can be accomplished using the VLOOKUP function. However, calculating the total tax bill is a little more difficult. Below we have shown a copy of the IRS tax table for an unmarried individual for 2018. Often, tax tables are presented in this format. If taxable income is But not over ... over ... The tax is: S 0 $ 9.525 10% of the amount over 50 9.526 38.700 38,701 93,700 $952.50 plus 15% of the amount over $9.525 $5.328.75 plus 25% of the amount over $38,700 $19,078.75 plus 28% of the amount over $93.700 $47,568.75 plus 33% of the amount over $195.450 93,701 195,450 195,451 424,950 424,951 426,700 $123,303.75 plus 35% of the amount over $424.950 426,701 $123,916.25 plus 39.6% of the amount over $426,700 In reading this table, the marginal tax rate for taxable income less than $9.525 is 10 percent. If the taxable income is between SO 505 and 7700150mm Undladh MHC Reader 426,701 $123,916.25 plus 39.6% of the amount over $426,700 In reading this table, the marginal tax rate for taxable income less than $9,525 is 10 percent. If the taxable income is between $9,525 and $38,700, the tax bill is $952.50 plus the marginal taxes. The marginal taxes are calculated as the taxable income minus $9,525 times the marginal tax rate of 15 percent. Page 47 Below, we have the corporate tax table that was applicable 2017 and as shown in Table 2.3 2. Taxable income is greater than or equal to... But less than... Tax rate 0 15% 50,001 25 75,001 34 100,001 $ 50,000 75.000 100,000 335,000 10,000,000 15,000,000 18,333,333 39 335.001 34 10,000,001 35 15.000,001 38 18,333,334 35 a. Create a tax table in Excel for corporate taxes similar to the individual tax table shown above. Your spreadsheet should then But equal to... less than... Tax rate $ 0 $ 50,000 15% 50,001 75,000 25 75,001 100,000 34 100,001 335,000 39 335,001 10,000,000 34 10,000,001 15,000,000 35 15,000,001 18,333,333 38 18,333,334 35 a. Create a tax table in Excel for corporate taxes similar to the individual tax table shown above. Your spreadsheet shou calculate the marginal tax rate, the average tax rate, and the tax bill for any level of taxable income input by a user. b. For a taxable income of $1,350,000, what is the marginal tax rate? c. For a taxable income of $1,350,000, what is the total tax bill? d. For a taxable income of $1,350,000, what is the average tax rate? Case 1 - Cash Flows and Financial State- ments at Sunset Boards, Inc. (Chapter 2) Submit Assignment Due Sunday by 11:59pm Points 100 Submitting a file upload Chapter Case on page 47, Cash Flows and Financial Statements at Sunset Boards, Inc. Instruction: Before you work on case 1, please read the case description at the end of Chapter 2 on page 47 in the text book and watch the Cash Flows and Financial Statement Analysis Demonstration Video. The demo video illustrates how to perform cash flow analysis on the company. The best way to study the case is to watch and follow the video using the Excel template below. Once you understand the solving process, erase all the numbers. And then to practice one more time, work on the case independent of the video using the blank template. Complete the following steps. 1. Prepare the financial statements for #1 to #6 in the end of the case in an Excel file. Inside the Excel file, also answer questions 1 and 2.( You can type the answers to the questions directly on the Excel file.) 2. Complete your submission by uploading your Excel file using this assignment link. Please note that all assignments must be submitted through Canvas. No submission through email will be accepted. Case 1 Template-2.xlsx a Case Title: Input area: Year: Year: Cost of goods sold Cash Depreciation Interest expense Selling & Administrative Accounts payable Net Fixed assets Sales Accounts receivable Notes payable Long-term debt Inventory New equity Tax rate Dividend percentage Output area: 1 Construct the income statements. Year Income Statement Sales Cost of goods sold Selling & Administrative Depreciation EBIT Interest 5 MacBook Air Output area: 1 Construct the income statements. Income Statement Year Sales Cost of goods sold Selling & Administrative Depreciation EBIT Interest EBT Taxes Net income Dividends Addition to retained earnings Income Statement Year Sales Cost of goods sold Selling & Administrative Depreciation EBIT Interest EBT Taxes Net income Dividends Addition to retained earnings 2 Construct the balance sheets. Balance sheet as of Dec. 31. Year Cash Accounts receivable Accounts payable Notas navable 2 1 5 MacBook Air 95% bers File Edit Insert Table Organize Format Arrange View Share Window Help Case Template-2-Edited D125% View Zoom Ads Category Insert Table Chart Text Shape Media Comment + Sheet1 Collaborate Style 2 Construct the balance sheets. Text Balance sheet as of Dec. 31. Year Cash Accounts payable Notes payable Current liabilities Accounts receivable Inventory Current assets Text Shah Net fixed assets Total assets Long-term debt Owners' equity Total liab. & equity FI Border Balance sheet as of Dec. 31. Year Shadow Cash Accounts receivable Inventory Current assets Accounts payable Notes payable Current liabilities Reflection Long-term debt Owners' equity Total liab. & equity Opacity Net fixed assets Total assets 3 Calculate the operating cash flows. Year Year Operating cash flow Capital Spending Ending net fixed assets - Beginning net foxed assets Depreciation Net capital spending 5 MacBook Air YO 000 * F F7 3 Calculate the operating cash flows. Text Year Year Operating cash flow Text Te Shape > F Capital Spending Ending net fixed assets - Beginning net fixed assets + Depreciation Net capital spending Border Shadow Reflection Change in Net Working Capital Ending NWC -Beginning NWC Change in NWC Opacity 4 Calculate the cash flow from Assets Cash Flow from Assets Operating cash flow - Net capital spending -Change in NWC Cash flow from assets 5 Calculate the cash flow to creditors Cash Flow fo Creditors Interest paid 5 MacBook Air 940 ODO 12 Zoom Add Category View + Insert Table CN Text Shape Media Comment Co Collaborate Sheet1 Style Text 5 Calculate the cash flow to creditors. Cash Flow to Creditors Interest paid -Net New Borrowing Cash flow to Creditors Text he 6 Calculate the cash flow to stockholders, F Cash Flow to Stockholders Dividends paid -Net new equity raised Cash flow to Stockholders Border Shadow Reflection Answer to Question 1: Opacity Answer to Question 2: 5 MacBook Air A SUSISI