Recreate the table in excel.

Table 7.9 Spreadsheet Kappa's Net Working Capital Forecast 2021 2022 2023 2024 2025 2026 Year Working Capital ($ 000) Assets 1 Accounts Receivable 2 Raw Materials 3 Finished Goods 4 Minimum Cash Balance 5 Total Current Assets Liabilities 6 Wages Payable 7 Other Accounts Payable 8 Total Current Liabilities Net Working Capital 9 Net Working Capital (5 - 8) 10 Increase in Net Working Capital 18,493 14,525 1,973 1,534 4,192 4,967 6,164 7,262 30,822 28,288 16,970 1,775 5,838 8,485 33,067 19,689 2,039 6,815 9,845 38,388 22,709 26,059 2,329 2,646 7,911 9,138 11,355 13,030 44,304 50,872 1,294 3,360 4,654 1,433 4,099 5,532 1,695 4,953 6,648 1,941 5,938 7,879 2,211 6,900 9,110 2,570 7,878 10,448 26,168 22,756 (3,412) 26,419 3,663 30,509 4,089 35,194 4,685 40,425 5,231 Table 7.7 Spreadsheet Pro Forma Income Statement for Kappa, 2021-2026 Year 2021 2022 2023 2024 2025 2026 75,000 88,358 103,234 119,777 138,149 158,526 Income Statement ($ 000) 1 Sales 2 Cost of Goods Sold 3 Raw Materials 4 Direct Labor Costs 5 Gross Profit 6 Sales and Marketing 7 Administrative 8 EBITDA 9 Depreciation 10 EBIT 11 Interest Expense (net) 12 Pretax Income 13 Income Tax 14 Net Income (16,000) (18,665) (21,593) (24,808) (28,333) (32,193) (18,000) (21,622) (25,757) (30,471) (35,834) (41,925) 41,000 48,071 55,883 64,498 73,982 84,407 (11,250) (14,579) (18,582) (23,356) (27,630) (31,705) (13,500) (13,254) (15,485) (16,769) (17,959) (20,608) 16,250 20,238 21,816 24,373 28,393 32,094 (5,500) (5,450) (5,405) (6,865) (7,678) (7710) 10,750 14,788 16,411 17,508 20,715 24,383 (75) (6,800) (6,800) (6,800) (7,820) (8,160) 10,675 7,988 9,611 10,708 12,895 16,223 (3,736) (2,796) (3,364) (3,748) (4,513) (5,678) 6,939 5,193 6,247 6,960 8,382 10,545 Question 14 Reproduce the following table in Excel. In yellow are the inputs, in blue are the formulas. Fund Size: Term (years): Management fee: Carry: Deals: 200 // 200/5= 40 = contribution every year for 5 years 10 0.02 0.015 2% on commitment (200M) during 4 years investment period, 1.5% on (invested capital+realized capital) thereafter 20% 2 per year a $20m with 4y until exit 4 6 7 8 9 10 1 -40 2 -40 3 -40 -40 Year Invested capital Realized capital Management Fee Carried Interest 5 -40 80 -2.4 80 -1.8 -4 -4 -4 -200 400 -20.8 -40 -4 6 80 -0.6 -8 No 80 0 -16 80 0 -16 SUM: SUM: SUM: 0 Gross return -40 -40 -40 -40 40 80 80 80 80 SUM(GAIN): MOM: IRR: 200 2 18.9% CF to Investor -44 -44 -44 -44 37.6 78.2 71.4 64 64 SUM(GAIN): MOM: IRR: 139.2 1.696 13.5% COMMENTS From the reference book Exhibit 19.3: IMPACT OF COST ON NET RETURNS Fund Size (m): 200 Term years) 10 Management fee: Mon commitment during ay investment period, 1.5% on invested capital thereafter. Carry 20% of European style "All Capital First structure Thurdie rates and catch up not relevant as fund in example comfortably exceeds hurdie) Deals 2 per year a $20m with dy until exit STRONGLY SIMPLIFIED EXAMPLE! 5 8 9 YEAR INVESTED REALIZED 1 -40 2 -40 3 -40 4 40 Equal time distribution of investment & distributions Early win would impact IRR and timing for carried interest Management fee drops substantially after years, building a successful GP franchise requires new fund raised by this time. 80 80 80 80 80 MANAGEMENT FEE CARRIED INTEREST 4 4 24.18 -1.2 0.6 -16 0 SUM -16 SUM GROSS RETURN -40 -40 40 80 80 80 SUM (GAIN MOM RR 200 2 18.9 First carry' flows in year 71 Cost to investor about 55% p.a. or 0.31% MOM NET CF TO INVESTOR 44 -44 44 -44 376 782 708 634 64 SUM (GAIN: MOM RR 138 1.69 13.45 Table 7.9 Spreadsheet Kappa's Net Working Capital Forecast 2021 2022 2023 2024 2025 2026 Year Working Capital ($ 000) Assets 1 Accounts Receivable 2 Raw Materials 3 Finished Goods 4 Minimum Cash Balance 5 Total Current Assets Liabilities 6 Wages Payable 7 Other Accounts Payable 8 Total Current Liabilities Net Working Capital 9 Net Working Capital (5 - 8) 10 Increase in Net Working Capital 18,493 14,525 1,973 1,534 4,192 4,967 6,164 7,262 30,822 28,288 16,970 1,775 5,838 8,485 33,067 19,689 2,039 6,815 9,845 38,388 22,709 26,059 2,329 2,646 7,911 9,138 11,355 13,030 44,304 50,872 1,294 3,360 4,654 1,433 4,099 5,532 1,695 4,953 6,648 1,941 5,938 7,879 2,211 6,900 9,110 2,570 7,878 10,448 26,168 22,756 (3,412) 26,419 3,663 30,509 4,089 35,194 4,685 40,425 5,231 Table 7.7 Spreadsheet Pro Forma Income Statement for Kappa, 2021-2026 Year 2021 2022 2023 2024 2025 2026 75,000 88,358 103,234 119,777 138,149 158,526 Income Statement ($ 000) 1 Sales 2 Cost of Goods Sold 3 Raw Materials 4 Direct Labor Costs 5 Gross Profit 6 Sales and Marketing 7 Administrative 8 EBITDA 9 Depreciation 10 EBIT 11 Interest Expense (net) 12 Pretax Income 13 Income Tax 14 Net Income (16,000) (18,665) (21,593) (24,808) (28,333) (32,193) (18,000) (21,622) (25,757) (30,471) (35,834) (41,925) 41,000 48,071 55,883 64,498 73,982 84,407 (11,250) (14,579) (18,582) (23,356) (27,630) (31,705) (13,500) (13,254) (15,485) (16,769) (17,959) (20,608) 16,250 20,238 21,816 24,373 28,393 32,094 (5,500) (5,450) (5,405) (6,865) (7,678) (7710) 10,750 14,788 16,411 17,508 20,715 24,383 (75) (6,800) (6,800) (6,800) (7,820) (8,160) 10,675 7,988 9,611 10,708 12,895 16,223 (3,736) (2,796) (3,364) (3,748) (4,513) (5,678) 6,939 5,193 6,247 6,960 8,382 10,545 Question 14 Reproduce the following table in Excel. In yellow are the inputs, in blue are the formulas. Fund Size: Term (years): Management fee: Carry: Deals: 200 // 200/5= 40 = contribution every year for 5 years 10 0.02 0.015 2% on commitment (200M) during 4 years investment period, 1.5% on (invested capital+realized capital) thereafter 20% 2 per year a $20m with 4y until exit 4 6 7 8 9 10 1 -40 2 -40 3 -40 -40 Year Invested capital Realized capital Management Fee Carried Interest 5 -40 80 -2.4 80 -1.8 -4 -4 -4 -200 400 -20.8 -40 -4 6 80 -0.6 -8 No 80 0 -16 80 0 -16 SUM: SUM: SUM: 0 Gross return -40 -40 -40 -40 40 80 80 80 80 SUM(GAIN): MOM: IRR: 200 2 18.9% CF to Investor -44 -44 -44 -44 37.6 78.2 71.4 64 64 SUM(GAIN): MOM: IRR: 139.2 1.696 13.5% COMMENTS From the reference book Exhibit 19.3: IMPACT OF COST ON NET RETURNS Fund Size (m): 200 Term years) 10 Management fee: Mon commitment during ay investment period, 1.5% on invested capital thereafter. Carry 20% of European style "All Capital First structure Thurdie rates and catch up not relevant as fund in example comfortably exceeds hurdie) Deals 2 per year a $20m with dy until exit STRONGLY SIMPLIFIED EXAMPLE! 5 8 9 YEAR INVESTED REALIZED 1 -40 2 -40 3 -40 4 40 Equal time distribution of investment & distributions Early win would impact IRR and timing for carried interest Management fee drops substantially after years, building a successful GP franchise requires new fund raised by this time. 80 80 80 80 80 MANAGEMENT FEE CARRIED INTEREST 4 4 24.18 -1.2 0.6 -16 0 SUM -16 SUM GROSS RETURN -40 -40 40 80 80 80 SUM (GAIN MOM RR 200 2 18.9 First carry' flows in year 71 Cost to investor about 55% p.a. or 0.31% MOM NET CF TO INVESTOR 44 -44 44 -44 376 782 708 634 64 SUM (GAIN: MOM RR 138 1.69 13.45