Income and Cash Flow Analysis

Excel Online Structured Activity: income and Cash Flow Analysis The Berndt Corporation expects to have saies of $11 million. Costs other than depreciation are expected to be 60% of sales, and depreciation is expected to be $2.2 million, All sales revenues will be collected in cash, and costs other than depreciation must be paid for during the year. Brendt's federal-plusstate tax rate is 40%. Berndt has no debt. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. Open spreadsheet a. Set up an income statement. What is Berndt's expected net cash flow? Enter your answer in dollars. For example, an answer of $1.2 million shoula be entered as 1,200,000. Round your answer to the nearest dollar. 5 b. Suppose Congress changed the tax laws so that Berndt's depreciation expenses doubled. No changes in operations occurred, What is Berndt's expected net cash flow? Round your answer to the nearest dollar: $ C. Now suppose that Congress changed the tax laws such that, instead of doubling Berndt's depreciation, it was reduced by 50%. What is Berndt's expected net cash flow? Round your answer to the nearest dollar. d. If this were your compary, would you prefer Congress to cause your depreciation expense to be doubled or halved? Dowblis \begin{tabular}{|c|c|c|} \hline 1 & Income and Cash Flow Aralysis & \\ \hline 2 & & \\ \hline 3 & Canch sales & 511,000,000 \\ \hline 4 & Conts other toun aepreciation as $ of sales & 0000 \\ \hline 5 & Depreciation & $2200,000 \\ \hline 6 & Federal plus stale tax tale & 4000s \\ \hline ? & therent & so \\ \hline 8 & & \\ \hline 9 & income Statement: & \\ \hline 10 & salies & $11,000,000 \\ \hline 11 & Cents excent depreciulion & \\ \hlinen & Deprecintion & 2200.000 \\ \hline 11 & Eurtings betor interst ans tases (EBT) & \\ \hline 24 & menred & 0 \\ \hline 15 & Pretar eaming & 50 \\ \hline 16 & Tant & \\ \hline 17. & Net incone & \\ \hline 3 & & \\ \hline 15 & Nut Cash Flow: & \\ \hline 20 & Net income & \\ \hline 21 & Depreciation & 2700000 \\ \hline 22 & Nent canh flow & \\ \hline 2 & & \\ \hline 24 & Original Drpreciation Doubled & 84,400,000 \\ \hline 25 & & \\ \hline 25 & moeme Statement: & \\ \hline n. & Soles & $11,000000 \\ \hline 26 & Cons except deponcietion. & \\ \hline 28 & Depritecinton & 4400000 \\ \hline 30. & Eamings telore inturet and tares (EBIT) & \\ \hline 31 & Viternet & 0. \\ \hlinen & Proter earnings & 10 \\ \hline 33 & Taus & \\ \hline 3 & Net income & \\ \hline is. & & \\ \hlinex & Nor Canh Flow: & \\ \hline 37 & Net income & \\ \hline 31 & Depreciation & 4.400000 \\ \hline 97 & Nint caph flow & \\ \hline 40 & & \\ \hline 41 & Orlginal Depreciotion Helved & $1,100,000 \\ \hlinea & & \\ \hline 43 & Ineame Statement: & \\ \hline 44 & Salet & I11 000000 \\ \hline is & Conts except depreciation & \\ \hline 46 & Depreciation & 1.100 .000 \\ \hline a) & Earrings belore inderest end taust (fram) & \\ \hline 46 & interest & 0 \\ \hline 49 & Procex teenings & 1010 \\ \hline & Taues & \\ \hline 51 & Net income & \\ \hline 52 & & \\ \hline s3 & Net Cosh Fow: & \\ \hline 54 & Net income & \\ \hline 55 & Depreciatan & 1,+09,000 \\ \hline 56 & Ner cash fow: & \\ \hline 37 & & \\ \hline 58 & \begin{tabular}{l} Would you prefer depreciation to be \\ doutied or thahed? \end{tabular} & \\ \hline \end{tabular} Excel Online Structured Activity: income and Cash Flow Analysis The Berndt Corporation expects to have saies of $11 million. Costs other than depreciation are expected to be 60% of sales, and depreciation is expected to be $2.2 million, All sales revenues will be collected in cash, and costs other than depreciation must be paid for during the year. Brendt's federal-plusstate tax rate is 40%. Berndt has no debt. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. Open spreadsheet a. Set up an income statement. What is Berndt's expected net cash flow? Enter your answer in dollars. For example, an answer of $1.2 million shoula be entered as 1,200,000. Round your answer to the nearest dollar. 5 b. Suppose Congress changed the tax laws so that Berndt's depreciation expenses doubled. No changes in operations occurred, What is Berndt's expected net cash flow? Round your answer to the nearest dollar: $ C. Now suppose that Congress changed the tax laws such that, instead of doubling Berndt's depreciation, it was reduced by 50%. What is Berndt's expected net cash flow? Round your answer to the nearest dollar. d. If this were your compary, would you prefer Congress to cause your depreciation expense to be doubled or halved? Dowblis \begin{tabular}{|c|c|c|} \hline 1 & Income and Cash Flow Aralysis & \\ \hline 2 & & \\ \hline 3 & Canch sales & 511,000,000 \\ \hline 4 & Conts other toun aepreciation as $ of sales & 0000 \\ \hline 5 & Depreciation & $2200,000 \\ \hline 6 & Federal plus stale tax tale & 4000s \\ \hline ? & therent & so \\ \hline 8 & & \\ \hline 9 & income Statement: & \\ \hline 10 & salies & $11,000,000 \\ \hline 11 & Cents excent depreciulion & \\ \hlinen & Deprecintion & 2200.000 \\ \hline 11 & Eurtings betor interst ans tases (EBT) & \\ \hline 24 & menred & 0 \\ \hline 15 & Pretar eaming & 50 \\ \hline 16 & Tant & \\ \hline 17. & Net incone & \\ \hline 3 & & \\ \hline 15 & Nut Cash Flow: & \\ \hline 20 & Net income & \\ \hline 21 & Depreciation & 2700000 \\ \hline 22 & Nent canh flow & \\ \hline 2 & & \\ \hline 24 & Original Drpreciation Doubled & 84,400,000 \\ \hline 25 & & \\ \hline 25 & moeme Statement: & \\ \hline n. & Soles & $11,000000 \\ \hline 26 & Cons except deponcietion. & \\ \hline 28 & Depritecinton & 4400000 \\ \hline 30. & Eamings telore inturet and tares (EBIT) & \\ \hline 31 & Viternet & 0. \\ \hlinen & Proter earnings & 10 \\ \hline 33 & Taus & \\ \hline 3 & Net income & \\ \hline is. & & \\ \hlinex & Nor Canh Flow: & \\ \hline 37 & Net income & \\ \hline 31 & Depreciation & 4.400000 \\ \hline 97 & Nint caph flow & \\ \hline 40 & & \\ \hline 41 & Orlginal Depreciotion Helved & $1,100,000 \\ \hlinea & & \\ \hline 43 & Ineame Statement: & \\ \hline 44 & Salet & I11 000000 \\ \hline is & Conts except depreciation & \\ \hline 46 & Depreciation & 1.100 .000 \\ \hline a) & Earrings belore inderest end taust (fram) & \\ \hline 46 & interest & 0 \\ \hline 49 & Procex teenings & 1010 \\ \hline & Taues & \\ \hline 51 & Net income & \\ \hline 52 & & \\ \hline s3 & Net Cosh Fow: & \\ \hline 54 & Net income & \\ \hline 55 & Depreciatan & 1,+09,000 \\ \hline 56 & Ner cash fow: & \\ \hline 37 & & \\ \hline 58 & \begin{tabular}{l} Would you prefer depreciation to be \\ doutied or thahed? \end{tabular} & \\ \hline \end{tabular}