Question

Please Somebody can help me specially on the what if analysis.

6-9. Compsys is a start-up computer company. This quarter they expect Sales of $550,000. They expect their Sales to grow at a rate of 9% per quarter. Their Cost of Goods Sold is running at 34% of Sales and is expected to stay at that rate. Their Selling Costs are 12% of Sales and are expected to stay at that rate. Their General and Administrative Costs (including Research and Development) are $420,000 this quarter and are scheduled to rise at 2% per quarter. (a) Design and implement a worksheet to make a 20 quarter income statement projection for Compsys. You should have a separate input area with well-labeled separate cells for each of the numbers above. You should have a separate row for each item in the income statement and a separate column for each quarter. The final row should be for Earnings Before Taxes. Use the Fill operation wherever possible. In the initial quarters, Compsys should show operating losses (negative Earnings Before Taxes). Under the assumptions stated above, what is the first quarter in which Compsys shows an operating profit (positive Earnings Before Taxes)? uppose Sales grow at 12% per quarter. Now in what quarter would Compsys first show an operating profit? es grow at 9% per quarter and General and Administrative Costs grow at 6% (c) Suppose Sales grow at 9% per quarter and General and A Costs are 15% of sales. Now in what quarter will Compsys first show per quarter and Selling Costs are 15% of salee an operating profit? (d) Create a one-dimensional data table that shows the Far quarter for different constant quarterly Sales growth rates of ansional data table that shows the Earnings Before Taxes in the final Sales growth rates of 0%, 1%, 2%, through 20%. (e) Create a two-dimensional data table that shows the quarter for different constant quarterly Sales growth rates of left column and constant quarterly General and Administra through 10% in the top row. ata table that shows the Earnings Before Taxes in the final Sales growth rates of 0%, 1%, 2%, through 20% in the al and Administrative growth rates of 0%, 1%,2

Answer 1

Answer to (a) = Starting from Quarter 7, company earns operating profit.

H I J K L M N O L P e R Qtr 5 Qtr 6 Qtr 9 Otr 11 Qtr 12 Otr 13 Tot Qtr 14 lo Otr 19 Qtr 20 9% 996 9% 9% 34% 12% 2% 9% 34% 12% 29 9% 34% 12% 2% Input Data Qtr 10 9% 9% 34% 34% 1291 12% 2% 2% 34% 12% 2% 3496 9% 34% 12% 2% 9% 34% 12% 34% 12% 2% Qtr 15 9% 3491 12% 9% 34% 12% 2% 9% 34% 12% 2% 9% 34% 12% 2% lotr 18 9% 34% 12% 9% 34% 12% 2% 9% 34% 2% 9% 34% 12% 2% 9% 34% 12% 2% 296 12% 2% 2%/ 2% 1 Answer to (a) 2 3 Qtr 1 4 Sales 550,000 5 Cost of Goods sold (COGS) 34% 6 Selling Cost 12% 7 General and Admin Cost 420,000 8 9 10 11 12 Sales 550,000 13 Cost of Goods sold (COGS) 187,000 14 Gross Margin 363,000 15 Selling Cost 66,000 16 General and Admin Cost 420,000 17 Earnings before Taxes (EBT -123,000 18 Otr 1 otr 3 Qtr 2 599,500 203,830 395,670 71,940 428,400 -104,670 653,455 222,175 431,280 78,415 436,968 -84,103 Qtr 4 712,266 242,170 470,096 85,472 445,707 -61,083 Qtr 5 Qtr 6 776,370 846,243 263,966 287,723 512,404 558,520 93,164 101,549 4 54,621 463,713 -35,381 -6,742 922,405 313,618 608,787 110,689 472.987 25,111 Income Statement (all numbers rounded off to 0 decimal places) Qtr 8 Qtr 9 Qtr 10 Qtr 11 Qtr 12 Qtr 13 Qtr 14 1,005,421 1,095,909 1,194,541 1,302,050 1,419,235 1,546,966 1,686,193 341,843 372,609 406,144 442,697 482,540 525,968 573,306 663,578723,300 788,397 859,353 936,695 1,020,998 1,112,887 120,651 131,509 143,345 156,246 | 170,308 185,636 2 02,343 4 82,447 492,096 501,938 511,977 522,217 532,661 543,314 60,480 99,695 143,114 | 191,130 244,170 | 302,701 367,230 Qtr 15 Qtr 16 Qtr 17 Qtr 18 Qtr 19 Qtr 20 1,837,950 2,003,366 2,183,669 2,380,1992,594,417 2,827,915 624,903 L 681,144 | 742,447 809,268 882.102 961.491 1,213,047 1.322.222 1.441.222 1.570.931 1.712.315 2 20,554 240,404 262,040 285,624 311,330 339,350 554,180 565,264 5 76,569 588,100 599,862 611,859 4 38,313 | 516,554 | 602,613 | 697,207 801,123 | 915,215 19 20 Formula to use in income statement 21 lotr 1 Jotr 2 lotr 3 lotr 4 lotr 5 lotr 6 lotr 7 Qtr 8 Qtr 9 Qtr 10 Qtr 11 Qtr 12 Qtr 13 Qtr 14 Qtr 15 Qtr 16 Qtr 17 lotr 18 Qtr 19 Qtr 20 22 Sales B12+(B12*04) C12+(C12*04) D12+D12*64) E12+(E12*F4) F12+F12*64) G12+(G12*H4) H12+H12*14) 112+(112*14) J12+J12*K4) K12+K12*14) L12+(L12*M4) M12+M12*N4) N12+N12*04) 012+(012*P4) P12+P12*04) 012+Q12*R4) R12+R12*54) S12+(S12*T4) T12+(T12*U4) 23 Cost of Goods sold (COGS) B12*85 C12*C5 D12*D5 E12*E5 F12*F5 G12*G5 H12*H5 112*15 1 12*15 K12*K5 L12*L5 M12*M5 N12*N5 0 12*05 P 12*P5 Q12*05 R12*R5 S12*55 T 12*T5 U12*U5 24 Gross Margin B12-B13 | C12-C13 012-D13 E12-E13 F12-F13 G12-G13 H12-H13 112-113 112-113 K12-K13 L12-L13 M12-M13 N12-N13 012-013 P12-P13 Q12-Q13 R12-R13 512-513 T 12-T13 U12-U13 25 Selling Cost B12*86 C12*06 012*D6 E12*E6 F12*F6 G 12*G6 H 12*H6 112*16 112*16 K12*K6 L12*L6 M12*M6 N12*N6 012*06 P12*P6 012*06 R12*R6 S 12*S6 T12*T6 112*U6 26 General and Admin Cost B7 B16+(B16*C7) C16+(C16*D7) D16+(D16*E7) E16+E16*F7) F16+F16*G7) G16+(G16*H7) H16+(H16*17) 116+(116*17) J16+(J16*K7) K16+K16*L7) L16+(L16*M7) M16+M16*N7) N16+(N16*07) 016+016*P7) P16+(P16*07) 016+(016*R7) R16+(R16*57) S16+(516*T7) T16+T16*U7) 27 Earnings before Taxes (EBT B14-B15-B16 C14-C15-C16 D14-D15-D16 E14-E15-E16 F14-F15-F16 G14-G15-G16 H14-H15-H16 114-115-116 114-115-J16 K14-K15-K16 L14-L15-L16 M14-M15-M16 N14-N15-N16 014-015-016 P14-P15-P16 014-015-016 R14-R15-R16 514-515-516 T14-T15-T16 U14-U15-U16 28

Answer to (b) - Starting from Quarter 5, company earns operating profit.

Formula Bar к 1 Answer to (b) 2 Qtr 2 Otr3 Qtr 4 Qtr 5 Qtr 6 Lotr 7 Otr 8 Qtr 9 lotr 12 lotr 13 Qtr 15 Qtr 16 Qtr 17 Qtr 18 Qtr 19 Otr 20 12% 12% 12% 12% 1296 12% 1296 12% 34% 12% 12% 34% 1296 12% 34% 12% 12% 34% 12% Input Data Tot Qtr 10 lotr 11 129 12% 34% 34% 12% 12% 2% Qtr 14 1296 34% 12% 349 12% 34% 34% 12% 12% 34% 12% 2% 34% 12% 2% 34% 12% 12% 34% 12% 2% 12% 34% 12% 12% 34% 12% 34% 12% 34% 12% 34% 12% 12% 12% 296 2% 2% 2% 2% 296 296 2% 296) 296 296 lotr 1 4 Sales 550,000 5 Cost of Goods sold (COGS) 34% 6 Selling Cost 12% 7 General and Admin Cost 420,000 8 9 10 11 Qtr 1 12 Sales 550,000 13 Cost of Goods sold (COGS) 187,000 14 Gross Margin 363,000 15 Selling Cost 66,000 16 General and Admin Cost 420,000 17 Earnings before Taxes (EBT -123,000 18 Qtr 2 616,000 209,440 406,560 73,920 428,400 -95,760 Qtr 3 Qtr 4 Qtr 5 689,920 772,710 865,435 234,573 262,721 294,248 455,347 509,989 571,187 82,790 92,725 103,852 436,968 445,707 454,621 -64,411 -28,443 12,714 Qtr 6 969,287 329,558 639,729 116,314 463,713 59,702 Income Statement (all numbers rounded off to 0 decimal places) Qtr 7 Qtr 8 Qtr 9 Qtr 10 Qtr 11 Qtr 12 Qtr 13 1,085,601 1,215,873 1,361,778 1,525,191 1,708,214 1,913,200 2,142,784 369,104 413,397 463,005 518,565 580,793 650,488 728,547 716,497 802,476 898,773 1,006,626 1,127,421 1,262,712 1,414,237 130.272 145,905 163,413 183,023 204.986 229,584 257,134 472,987 482,447 492,096 501,938 511,977 522,217 | 532,661 113,238 174,1241 243,264 321,665 410,458 510,911 624,442 Qtr 14 2,399,918 815,972 1,583,946 287,990 543,314 752,642 Qtr 15 2,687,908 913,889 1,774,019 322,549 554,180 897,290 Qtr 16 Qtr 17 3,010,457 L 3,371,712 1,023,555 1,146,382 1,986,902 2,225,330 361,255 404,605 565,264 576,569 1,060,383 1,244,156 Qtr 18 Qtr 19 3,776,317 4,229,475 1,283,948 1,438,022 2,492,369 2,791,453 453,158 507,537 588,100| 588,100 599,862 1,451,111 | 1,684,054 Qtr 20 4,737,012 1,610,584 3,126,428 568,441 611,859 1,946,128 19 21 20 Formula to use in income statement Qtr 1 Qtr 2 Otr3 Qtr 4 Qtr 5 Qtr 6 Qtr 7 Qtr 8 Qtr 9 Qtr 10 Qtr 11 Qtr 12 Qtr 13 Qtr 14 Qtr 15 Qtr 16 Qtr 17 Qtr 18 Qtr 19 Q tr 20 22 Sales B12+(B12*04) C12+(C12*D4) D12+D12*54) E12+(E12*F4) F12+(F12*64) G12+(G12*H4) H12+(H12*14) 112+112*J4) J12+(J12*K4) K12+K12*L4) L12+(L12*M4) M12+M12*N4) N12+(N12*04) 012+(012*P4) P12+(P12*04 Q12+(Q12*R4) R12+(R12*54) S12+(512*T4) T12+(T12*U4) 23 Cost of Goods sold (COGS) B12*85 C12*C5 D12*D5 E12*E5 F12*F5 G12*65 H12*H5 1 12*15 J12*15 K12*K5 L12*L5 M12*M5 N 12*N5 012*05 P12*P5 Q12*Q5 R12*R5 S12*55 T12*T5 U12*U5 24 Gross Margin B12-B13 C12-C13 D 12-D13 E12-E13 F12-F13 G12-G13 H12-H13 1 12-113 J12-113 K12-K13 L12-L13 M12-M13 N12-N13 0 12-013 P12-P13 Q12-Q13 R12-R13 S12-S13 T12-T13 U12-U13 25 Selling Cost B12*86 C12*06 D12*D6 E 12*E6 F12*F6 G12*G6 H12*H6 1 12*16 J12*16 K12*K6 L12*L6 M12*M6 N 12*N6 0 12*06 P12*P6 Q12*Q6 R12*R6 S12*56 T12*T6 U12*U6 26 General and Admin Cost B7 B16+(B16*07) C16+C16*D7) D16+(D16*E7) E16+(E16*F7) F16+(F16*G7) G16+G16*H7) H16+(H16*17) 116+(116*17) J16+(J16*K7) K16+K16*L7) L16+L16*M7) M16+M16*N7) N16+(N16*07) 016+(016*P7) P16+(P16*07) 016+Q16*R7) R16+(R16*57) 516+(516*17) T16+(T16*U7) 27 Earnings before Taxes (EBT B14-B15-B16 C14-C15-C16 D14-D15-D16 E14-E15-E16 F14-F15-F16 G14-G15-G16 H14-H15-H16 114-115-116 114-115-J16 K14-15-K16 L14-L15-L16 M14-M15-M16 N14-N15-N16 014-015-016 P14-P15-P16 014-015-016 R14-R15-R16 514-515-516 T14-T15-T16 U14-U15-U16 28

Answer to (c) - Starting from Quarter 16, company earns operating profit.

B J K N 1 Answer to (c) 2 Qtr 2 Qtr 3 Qtr 5 Otr 6 Otr 7 Lotr 8 Qtr 9 Qtr 11 Qtr 12 Qtr 13 Qtr 14 Qtr 16 Qtr 17 Qtr 18 Qtr 19 996 9% 99% 996 996 9% 996 Input Data Qtr 10 9% 34% 15% lotra Qtr 4 9% 34% 15% 3 Qtr 1 4 Sales 550,000 5 Cost of Goods sold (COGS) 34% 6 Selling Cost 7 General and Admin Cost 8 9% 34% 15% 9% 34% 15% 3494 34% Qtr 15 9% 34961 15% 34% 15% 9% 34% 15% Qtr 20 9% 34% 15%) 9% 34% 15% 34% 15% 34% 15% 34% 9% 34% 15% 6% 9% 34% 15% 34% 15% 9% 34% 15% 34% 15% 6% 1596 15% 695/ 6% 6% 696 6% 6% 6% 6% 696 69 6% 9 10 lotr 7 11 Qtr 1 12 Sales 550,000 13 Cost of Goods sold (COGS) 187,000 14 Gross Margin 363.000 15 Selling Cost 82,500 16 General and Admin Cost 420,000 17 Earnings before Taxes (EBT -139,500 Qtr 2 599,500 203,830 395,670 89,925 445,200 - 139,455 Otr 3 lotr 4 lotr 5 lotr 6 653,455 712,266 776,370 846,243 222,175 242,170 263,966 287,723 431,280 470,096 512 404 558,520 98,018 106,840 116,456 126,936 471,912 500,227 530,241 562,055 -138,650| -136,971| -134,293 -130,471 Income Statement (all numbers rounded off to 0 decimal places) Qtr 8 Qtr 9 Qtr 10 Qtr 11 Qtr 12 lotr 13 922,405 1,005,421 1,095,909 1,194,541 1,302,050 1,419,235 1,546,966 313,618 341,843 372,609 406,144 442,697 482,540 525,968 608,787 663,578 723,300 788,397 859,353 936,695 1,020,998 138,361 150,813 164,386 179,181 195,308 212,885 232,045 595,778 631,525 669,417 709,582 7 52,157 797,286 845,123 -125,352 -118,760 -110,503 -100,366 -88,112 -73,476 -56,170 Qtr 14 1,686,193 573,306 1,112,887 252,929 8 95,830 -35,872 Qtr 15 1,837,950 624,903 1,213,047 2 75,693 949,580 -12,226 Qtr 16 2,003,366 681,144 1,322,222 300,505 1,006,555 15,162 Qtr 17 2,183,669 742,447 1,441,222 3 27,550 1,066,948 46,724 Qtr 18 2,380,199 809,268 1,570,931 357,030 1,130,965 82,936 Qtr 19 2,594,417 882,102 1.712 315 1,712,315 389,163 1,198,823 124,329 Qtr 20 2,827,915 961,491 1,866,424 424,187 1,270,752 171,485 18 19 20 Formula to use in income statement Qtr 1 Qtr 2 Qtr 3 Qtr 4 Qtr 5 Qtr 6 Qtr 7 Qtr 8 Qtr 9 Qtr 10 Qtr 11 Qtr 12 Qtr 13 Qtr 14 Qtr 15 Qtr 16 Qtr 17 Qtr 18 Qtr 19 Qtr 20 22 Sales B4 B12+(B12*C4) C12+(C12*D4) D12+(D12*54) E12+(E12*F4) F12+(F12*64) G12+(G12*H4) H12+(H12*14) 112+(112*34) J12+(J12*K4) K12+K12*L4) L12+(L12*M4) M12+M12*N4) N12+(N12*04) 012+(012*P4) P12+(P12*04) Q12+(Q12*R4) R12+(R12*54) S12+(512*T4) T12+(T12*U4) 23 Cost of Goods sold (COGS) B12*85 C12*C5 D12*D5 E12*E5 F12*F5 G12*G5 H12*H5 1 12*15 1 12*15 K 12*K5 L12*L5 M12*M5 N12*N5 0 12*05 P12*P5 0 12*Q5 R12*R5 S12*55 T12*T5 U12*U5 24 Gross Margin B12-B13 C12-C13 D 12-D13 E12-E13 F12-F13 G12-G13 H12-H13 112-113 112-113 K12-K13 L12-L13 M12-M13 N12-N13 012-013 P12-P13 Q12-Q13 R12-R13 512-513 T12-T13 U12-U13 25 Selling Cost B12*86 C12*C6 D12*D6E12*E6 F12*F6 G12*G6 H12*H6 1 12*16 J12*16 K12*K6 L12*16 M12*M6 N12*N6 012*06 P12*P6 Q12*Q6 R12*R6 S12*S6 T12*T6 U12*U6 26 General and Admin Cost B7 B16+(B16*c7) C16+(C16*07) D16+(016*E7) E16+(E16*F7) F16+(F16*G7) G16+(G16*H7) H16+(H16*17) 116+(116*17) J16+(J16*K7) K16+(K16*L7) L16+(L16*M7) M16+(M16*N7) N16+(N16*07) 016+(016*27) P16+(P16*07, 016+(Q16*R7) R16+(R16*57) S16+(516*T7) T16+(T16*U7) 27 Earnings before Taxes (EBT B14-B15-B16 C14-C15-C16 D14-D15-D16 E14-E15-E16 F14-F15-F16 G14-G15-G16 H14-H15-H16 114-115-116 J14-115-J16 K14-15-K16 L14-L15-L16 M14-M15-M16 N14-N15-N16 014-015-016 P14-P15-P16 014-015-016 R14-R15-R16 514-515-516 T14-T15-T16 U14-U15-U16 28

Answer to (d)

KL RS TL 1 Answer to (d) Input Data T Qtr 11 Otr 1 Otr 2 Otr 4 Tatr 10 Otr 11 Tatr 12 Otr 17 Otr 15 Otr 16 Otr 17 Qtr 18 Qtr 19 Otr 20 Qtr 2 296 10% 12% 14% 15% 13% 34% 20% TETE 18% 34% 34% 19% 34% 34% 34% 34% 34% 34% 34% 34% 4 Sales 5 Cost of Goods sold (COGS 6 Selling Cost 7 General and Admin Cost 8 16% 34% 12% 550,000 34% 12% 420,000 17% 34% 12% 349 34% 12% 12% 12% 12% 12% 12% 12% 12% 12% 12 29 296 Otr 2 Qtr 4 Qtr 7 Qtr 8 Otr 17 12 Sales 13 Cost of Goods sold (COGS 14 Gross Margin 15 Selling Cost 16 General and Admin Cost! 17 Earnings before Taxes (EBT 550,000 187,000 363,000 66,000 420,000 -123,000 555,500 188,870 366,630 66,660 428,400 -128,430 566,610 192,647 373,963 67,993 436,968 -130,998 583,608 198,427 385,181 70,033 445,707 -130,559 Qtr 5 Qtr 6 606,952 206,364 400,588 72,834 454,621 -126,867 637,300 216,682 420,618 76,476 463,713 -119,571 675,538 229,683 445,855 81,065 472,987 -108,197 Income Statement (all numbers rounded off to 0 decimal places) Qtr 9 Qtr 10 Qtr 11 Qtr 12 722,826 780,652 850,911 936,002 1,038,962 245,761 265,422 289,310 318,241 353,247 477,065 515,230 561,601 617,761 685,715 86,739 93,678 102,109 112,320 124,675 482,447 492,096 501,938 511,977 522,217 -92,121 -70,544 -42,446 -6,536 38,823 Qtr 14 1,163,637 1,314,910 395,637 447,069 768,000 867,841 139,636 157,789 532,661 543,314 95,703 166,738 Qtr 15 1,498,997 509,659 989,338 179,880 554,180 255,278 Qtr 16 1,723,847 586,108 1,137,739 206,862 565,264 365,613 1,999,663 679,885 1,319,778 239,960 576,569 503,249 Qtr 18 Otr 19 2,339,606 2,760,735 795,466 938,650 1,544,140 1,822,085 280,753 331,288 588,100 599,862 675,287 890,935 Qtr 20 3,285,275 1,116,994 2,168,281 394,233 611,859 1,162,189 Otr 21 3,942,330 1,340,392 2,601,938 473,080 624,096 1,504,762 RA Formula to use in income statement Qtr 2 Qtr 3 Qtr 4 Qtr 6 Qtr 7 Qtr 8 Otr 9 Qtr 10 Qtr 11 Qtr 12 Qtr 14 Qtr 15 Qtr 16 Otr 19 Qtr 17 Qtr 18 Qtr 20 Qtr 21 Sales B12+(B12-C4) C12+(C12D4) D12+(D12*E4) E12+(E12*F4) F12+(F12*64) G12+(G12*H4) H12+(H12*14) 112+(11214) J12+(J12*K4) K12+1K12*L4) L12+(L12-M4) M12+(M12*N4) N12+(N12*04) 012+(012 P4) P12+(P12 Q4) Q12+(Q12*R4) R12+(R12*54) S12+(S12*T4) | T12+(T12*U4) 12+ 12v4) 23 Cost of Goods sold (COGS) B12B5 C12-C5 D12D5 E12 E5 F12 F5 G12-G5 H12*H5 112-15 J125 K12 K5 L12L5 M12*M5 N12 N5 012-05 P12*P5 012-05 R 12 R5 S12*55 T12-T5 U12-U5 V12*V5 24 Gross Margin B12-B13 C12-C13 D12-D13 E12-E13 F12-F13 G12-G13 H12-H13 112-113 112-113 K12-K13 L12-L13 M12-M13 N12-N13 012-013 P12-P13 012-913 R12-R13 S12-S13 T12-T13 U12-U13 V12-V13 25 Selling Cost B12.B6 C12.06 D12*D6 E12 E6 F12 F6 G12*G6 H12*H6 112.16 J12.16 K12*K6 L12*L6 M12*M6 N12 N6 012-06 P12.P6 Q12Q6 R12*R6 S12.56 T12 T6 U12-U6 V12 V6 26 General and Admin Cost B7 B16+(B16.07) C16+(C16*D7) D16+(D16E7) E16+(E16*F7) F16+(F16*G7) G16+(G16 H7) H16+(H16*17) 116+(116*17) 116+(J16*K7) K16+(K16*L7) L16+(L16*M7) M16+(M16*N7) N16+(N16*07) 016+016*P7) P16+(P16.07) 016+Q16*R7) R16+(R16*S7) S16+(S16*17) | T16+(T16*U7) U16+(U16*V7) 27 Earnings before Taxes (EB B14-B15-B16 C14-C15-C16 D14-D15-D16 E14-E15-E16 F14-F15-F16 G14-G15-G16 H14-H15-H16 114-115-116 J14-J15-J16 K14-K15-K16 L14-L15-L16 M14-M15-M16 N14-N15-N16 014-015-016 P14-P15-P16 014-015-016 R14-R15-R16 S14-S15-S16 T14-T15-T16 U14-U15-U16 V14-V15-V16 O O O RAN ONS &