Below is an Income and cash flow statements that management has approved. (If th
ID: 2623668 • Letter: B
Question
Below is an Income and cash flow statements that management has approved. (If there are errors or oversights, that is their problem, not yours). Start each question from the original data. Cells F14:F22 contain the original values in case you need to get back to them. a Determine the unit price that would achieve a cash flow in week 6 of $500,000 b Determine the sensitivity of the internal rate of return to a 10%, 25% and 50% increase in investment? (start with original values) c Determine the expected present worth for the following data where Forecast 1 is the current values. (start with original values) Forecast 1 (current Values) Forecast 2 Forecast 3 cell Unit Price $35.99 $40.00 $35.00 C14 COGS each $12.50 $15.00 $11.50 C15 S.G. & A. $800,000 $1,000,000 $900,000 C16 Sales Quantity Forecast year 1 50,000 40,000 60,000 C23 Probability 50% 30% 20% Original Values Unit Price $35.99 $35.99 COGS each $12.50 $12.50 S.G.& A. $800,000 $800,000 salvage $100,000 in year 6 $100,000 Income tax rate 35% 35% Capital Gains Tax rate 15% 15% Working capital no change no change MARR 15% 15% Investment $2,000,000 $2,000,000 Sales Quantity Forecast 50,000 60,000 72,000 86,400 103,680 124,416 Depreciation MACRS 5 20.00% 32.00% 19.20% 11.52% 11.52% 5.76% Income Statement 0 1 2 3 4 5 6 Sales revenue $1,799,500 $2,159,400 $2,591,280 $3,109,536 $3,731,443 $4,477,732 Cost of goods sold ($625,000) ($750,000) ($900,000) ($1,080,000) ($1,296,000) ($1,555,200) Gross Margin $1,174,500 $1,409,400 $1,691,280 $2,029,536 $2,435,443 $2,922,532 General, Sales and Admin. ($800,000) ($800,000) ($800,000) ($800,000) ($800,000) ($800,000) Depreciation ($400,000) ($640,000) ($384,000) ($230,400) ($115,200) ($57,600) EBIT ($25,500) ($30,600) $507,280 $999,136 $1,520,243 $2,064,932 Income tax $8,925 $10,710 ($177,548) ($349,698) ($532,085) ($722,726) Net income ($16,575) ($19,890) $329,732 $649,438 $988,158 $1,342,206 Cash Flow Statement Net Income ($16,575) ($19,890) $329,732 $649,438 $988,158 $1,342,206 Add depreciation $400,000 $640,000 $384,000 $230,400 $115,200 $57,600 Investment (2,000,000) Change in Working Capital ($179,950) ($35,990) ($43,188) ($51,826) ($62,191) ($74,629) Salvage $100,000 Tax on gain $15,000 Cash flow ($2,000,000) $203,475 $584,120 $670,544 $828,013 $1,041,167 $1,440,177 Present Worth = IRR $673,198 23.95% Below is an Income and cash flow statements that management has approved. (If there are errors or oversights, that is their problem, not yours). Start each question from the original data. Cells F14:F22 contain the original values in case you need to get back to them. a Determine the unit price that would achieve a cash flow in week 6 of $500,000 b Determine the sensitivity of the internal rate of return to a 10%, 25% and 50% increase in investment? (start with original values) c Determine the expected present worth for the following data where Forecast 1 is the current values. (start with original values) Forecast 1 (current Values) Forecast 2 Forecast 3 cell Unit Price $35.99 $40.00 $35.00 C14 COGS each $12.50 $15.00 $11.50 C15 S.G. & A. $800,000 $1,000,000 $900,000 C16 Sales Quantity Forecast year 1 50,000 40,000 60,000 C23 Probability 50% 30% 20% Original Values Unit Price $35.99 $35.99 COGS each $12.50 $12.50 S.G.& A. $800,000 $800,000 salvage $100,000 in year 6 $100,000 Income tax rate 35% 35% Capital Gains Tax rate 15% 15% Working capital no change no change MARR 15% 15% Investment $2,000,000 $2,000,000 Sales Quantity Forecast 50,000 60,000 72,000 86,400 103,680 124,416 Depreciation MACRS 5 20.00% 32.00% 19.20% 11.52% 11.52% 5.76% Income Statement 0 1 2 3 4 5 6 Sales revenue $1,799,500 $2,159,400 $2,591,280 $3,109,536 $3,731,443 $4,477,732 Cost of goods sold ($625,000) ($750,000) ($900,000) ($1,080,000) ($1,296,000) ($1,555,200) Gross Margin $1,174,500 $1,409,400 $1,691,280 $2,029,536 $2,435,443 $2,922,532 General, Sales and Admin. ($800,000) ($800,000) ($800,000) ($800,000) ($800,000) ($800,000) Depreciation ($400,000) ($640,000) ($384,000) ($230,400) ($115,200) ($57,600) EBIT ($25,500) ($30,600) $507,280 $999,136 $1,520,243 $2,064,932 Income tax $8,925 $10,710 ($177,548) ($349,698) ($532,085) ($722,726) Net income ($16,575) ($19,890) $329,732 $649,438 $988,158 $1,342,206 Cash Flow Statement Net Income ($16,575) ($19,890) $329,732 $649,438 $988,158 $1,342,206 Add depreciation $400,000 $640,000 $384,000 $230,400 $115,200 $57,600 Investment (2,000,000) Change in Working Capital ($179,950) ($35,990) ($43,188) ($51,826) ($62,191) ($74,629) Salvage $100,000 Tax on gain $15,000 Cash flow ($2,000,000) $203,475 $584,120 $670,544 $828,013 $1,041,167 $1,440,177 Present Worth = IRR $673,198 23.95%Explanation / Answer
original Values Unit Price $35.99 $35.99 COGS each $17.00 $14.75 S.G.& A. $800,000 $800,000 salvage $100,000 in year 6 $100,000 Income tax rate 35% 35% Capital Gains Tax rate 15% 15% Working capital no change no change MARR 4% 15% Investment $1,800,000 $1,800,000 Quantity Sold forecast 50,000 75,000 100,000 90,000 60,000 50,000 Depreciation MACRS 5 20.00% 32.00% 19.20% 11.52% 11.52% 5.76% Income Statement 0 1 2 3 4 5 6 Sales revenue $1,799,500 $2,699,250 $3,599,000 $3,239,100 $2,159,400 $1,799,500 Cost of goods sold ($850,000) ($1,275,000) ($1,700,000) ($1,530,000) ($1,020,000) ($850,000) Gross Margin $949,500 $1,424,250 $1,899,000 $1,709,100 $1,139,400 $949,500 General, Sales and Admin. ($800,000) ($800,000) ($800,000) ($800,000) ($800,000) ($800,000) Depreciation ($360,000) ($576,000) ($345,600) ($207,360) ($103,680) ($51,840) EBIT ($210,500) $48,250 $753,400 $701,740 $235,720 $97,660 Income tax $73,675 ($16,888) ($263,690) ($245,609) ($82,502) ($34,181) Net income ($136,825) $31,363 $489,710 $456,131 $153,218 $63,479 Cash Flow Statement Net Income ($136,825) $31,363 $489,710 $456,131 $153,218 $63,479 Add depreciation $360,000 $576,000 $345,600 $207,360 $103,680 $51,840 Investment (1,800,000) Change in Working Capital ($179,950) ($89,975) ($89,975) $35,990 $107,970 $35,990 Salvage $100,000 Tax on gain $15,000 Cash flow ($1,800,000) $43,225 $517,388 $745,335 $699,481 $364,868 $266,309 Present Worth = IRR $481,946.83 11.42% Solution a) Refer Scenario manager sheet b) original Values Unit Price $33.95 $35.99 COGS each $12.50 $14.75 S.G.& A. $800,000 $800,000 salvage $100,000 in year 6 $100,000 Income tax rate 35% 35% Capital Gains Tax rate 15% 15% Working capital no change no change MARR 4% 15% Investment $1,800,000 $1,800,000 Quantity Sold forecast 50,000 75,000 100,000 90,000 60,000 50,000 Depreciation MACRS 5 20.00% 32.00% 19.20% 11.52% 11.52% 5.76% Income Statement 0 1 2 3 4 5 6 Sales revenue $1,697,279 $2,545,919 $3,394,558 $3,055,102 $2,036,735 $1,697,279 Cost of goods sold ($625,000) ($937,500) ($1,250,000) ($1,125,000) ($750,000) ($625,000) Gross Margin $1,072,279 $1,608,419 $2,144,558 $1,930,102 $1,286,735 $1,072,279 General, Sales and Admin. ($800,000) ($800,000) ($800,000) ($800,000) ($800,000) ($800,000) Depreciation ($360,000) ($576,000) ($345,600) ($207,360) ($103,680) ($51,840) EBIT ($87,721) $232,419 $998,958 $922,742 $383,055 $220,439 Income tax $30,702 ($81,347) ($349,635) ($322,960) ($134,069) ($77,154) Net income ($57,019) $151,072 $649,323 $599,783 $248,986 $143,285 Cash Flow Statement Net Income ($57,019) $151,072 $649,323 $599,783 $248,986 $143,285 Add depreciation $360,000 $576,000 $345,600 $207,360 $103,680 $51,840 Investment (1,800,000) Change in Working Capital ($169,728) ($84,864) ($84,864) $33,946 $101,837 $33,946 Salvage $100,000 Tax on gain $15,000 Cash flow ($1,800,000) $133,254 $642,208 $910,059 $841,088 $454,502 $344,071 Present Worth IRR $1,084,380.37 19.74% Price at which project will achieve MARR would be $33.95 c) original Values Unit Price $52.09 $35.99 COGS each $17.00 $14.75 S.G.& A. $800,000 $800,000 salvage $100,000 in year 6 $100,000 Income tax rate 35% 35% Capital Gains Tax rate 15% 15% Working capital no change no change MARR 4% 15% Investment $2,000,000 $1,800,000 Quantity Sold forecast 50,000 75,000 100,000 90,000 60,000 50,000 Depreciation MACRS 5 20.00% 32.00% 19.20% 11.52% 11.52% 5.76% Income Statement 0 1 2 3 4 5 6 Sales revenue $2,604,545 $3,906,818 $5,209,091 $4,688,182 $3,125,455 $2,604,545 Cost of goods sold ($850,000) ($1,275,000) ($1,700,000) ($1,530,000) ($1,020,000) ($850,000) Gross Margin $1,754,545 $2,631,818 $3,509,091 $3,158,182 $2,105,455 $1,754,545 General, Sales and Admin. ($800,000) ($800,000) ($800,000) ($800,000) ($800,000) ($800,000) Depreciation ($400,000) ($640,000) ($384,000) ($230,400) ($115,200) ($57,600) EBIT $554,545 $1,191,818 $2,325,091 $2,127,782 $1,190,255 $896,945 Income tax ($194,090.91) ($417,136) ($813,782) ($744,724) ($416,589) ($313,931) Net income $360,455 $774,682 $1,511,309 $1,383,058 $773,665 $583,015 Cash Flow Statement Net Income $360,455 $774,682 $1,511,309 $1,383,058 $773,665 $583,015 Add depreciation $400,000 $640,000 $384,000 $230,400 $115,200 $57,600 Investment (2,000,000) Change in Working Capital ($260,455) ($130,227) ($130,227) $52,091 $156,273 $52,091 Salvage $100,000 Tax on gain $15,000 Cash flow ($2,000,000) $500,000 $1,284,455 $1,765,082 $1,665,549 $1,045,138 $807,705 Present Worth IRR $4,135,305.91 49.05%
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.