Ceramic Structures Engineering, Inc. Example Case Ceramic Structures has experienced rapid growth over the past several years. Sales are expected to grow at 15% per year for the next three years. Sales growth has been fueled by aggressive pricing as well as increased use of ceramics in high performance engines. Asset growth has been financed by internal funds as well as the increased use of debt. At the end of 2018, the debt was restructured with a new 10% seven-year loan with principal payments of $1 million per year. In addition a $1.5 million working capital line was negotiated in 2017. It was increased to $2.5 million in 2019 and $3.5 million in 2020. Interest is charged at prime + 1%. (For class, we will use 9%.) Cash balances will be kept around $250,000 and the credit line will average $2 million. 1. Prepare pro-forma statements for Ceramic and determine their need for funds for the years 2021-2023. xHSS ? 6 X Ceramic Spreadsheet (1) [Mode protg) - Excel (chec de l'activation du produit) DONNES RVISION AFFICHAGE FICHIER ACCUEIL INSERTION MISE EN PAGE FORMULES Connexion G75 f Current Portion LTD D E F G H I J K L M N 0 2019 2020 Forecast 2021 2022 2023 470 9 946 8 071 18 487 9 325 27 812 250 11 507 9 020 20 777 10 354 31 131 ASSETS Cash Accounts receivable Inventory Current assets Net fixed assets Total assets A B 1 CERAMIC STRUCTURES ENGINEERING, INC. 2 BALANCE SHEETS 3 AT DECEMBER 31, 4 5 2016 2017 2018 6 7 2 363 1 178 899 8 4 779 5 643 8 467 9 3 450 5213 6 985 10 10 592 12 033 16 351 11 5 193 6 551 8 235 12 15 785 18 584 24 586 13 14 15 1 505 1 669 2 139 16 1 000 17 1 000 18 243 299 425 19 1 748 2 968 2 564 20 2 358 2 358 7 358 21 4 106 5 326 22 3 000 3 000 3 000 23 8 679 10 258 11 775 24 11 679 13 258 14 775 25 15 785 18 584 24 696 26 4 004 1 000 1 000 566 5 570 6358 11 928 3 000 12 884 15 884 27 812 4 226 2 500 1 000 818 7 544 5358 12 902 3 000 15 229 18 229 31 131 LIABILITIES AND EQUITIES Accounts payable Notes payable Current Portion LTD Accruals Current liabilites Long-term debt Total Liabilities Common stock Retained earnings Equities Total capital 9921 27 Cumulative Need for Funds Annual Need for For Funds 28 29 1 Activer Windows Accdez aux paramtres pour activer Windows CeramicSpreadsheet PRT 90 % % O Taper ici pour rechercher + 21:33 05/04/2021 P P9 x] 640) x H 5 ? 6 X Ceramic Spreadsheet (1) [Mode protg) - Excel (chec de l'activation du produit) DONNES RVISION AFFICHAGE FICHIER ACCUEIL INSERTION MISE EN PAGE FORMULES Connexion G75 : f Current Portion LTD B C D E F H I J K L M N 0 27 G Cumulative Need for Funds Annual Need for For Funds 28 29 2016 2017 2018 2019 2020 2021 2022 2023 30 31 32 33 26 470 20 087 6 383 33 856 25 386 8 471 42 709 32 199 10 510 56 196 45 125 11 071 70 245 55 560 14 685 Sales Cost of Goods Gross Profit 34 35 36 37 38 39 40 41 42 1 324 1 642 1 064 289 4318 2065 330 1 735 798 937 1 693 1 726 1 342 342 5 103 3 368 444 2 924 1 345 1 579 2 563 1 815 1 666 487 6 531 3 979 1 170 2 809 1 292 1 517 3 372 1 910 1 910 703 7 895 3 176 1 123 2 053 944 1 109 4 215 2010 2 121 772 9 118 5 566 1 223 4 344 1 998 2 346 G and A expenses Fixed operating expenses Depreciation Miscellaneous Total operating expenses EBIT Interest EBT Taxes Net income 43 44 45 46 47 48 49 100,00% 75,89% 24,11% 100,00% 74.98% 25,02% 100,00% 75.39% 24,61% 100,00% 80,30% 19,70% 100,00% 79,09% 20,91% Sales Cost of Goods Gross Profit 50 51 52 53 54 5,00% 6,20% 4,02% 1,09% 5,00% 5,10% 3,96% 1,01% 6,00% 4,25% 3,90% 1,14% 6,00% 3,40% 3,40% 1,25% 6,00% 2,86% 3,02% 1,10% G and A expenses Fixed operating expenses Depreciation Miscellaneous 55 :1 Tab Activer Windows Accdez aux paramtres pour activer Windows. CeramicSpreadsheet PRT 90 % % O Taper ici pour rechercher + 21:34 05/04/2021 P P9 x] 640) xHSS ? 6 X Ceramic Spreadsheet (1) [Mode protg) - Excel (chec de l'activation du produit) DONNES RVISION AFFICHAGE FICHIER ACCUEIL INSERTION MISE EN PAGE FORMULES Connexion G75 f Current Portion LTD F H I J K L M N 0 55 56 57 58 59 60 61 62 63 64 65 66 67 1,09% 16,31% 7,80% 1,25% 6,55% 46,00% 3,54% B 1,01% 15,07% 9,95% 1,31% 8,64% 46,00% 4,66% 1,14% 15,29% 9,32% 2.74% 6,58% 46.00% 3,55% D 1.25% 14,05% 5,65% 2.00% 3,65% 46,00% 1,97% E 1,10% 12.98% 7,92% 1,74% 6,18% 46.00% 3,34% Miscellaneous Total operating expenses EBIT Interest EBT Taxes Net income 8.93% 18,06% 13,03% 40,01% 19,62% 59,63% 3,48% 16,67% 15,40% 35,54% 19,35% 54,89% 2,10% 19,83% 16,35% 38.28% 19,28% 57,57% 0,84% 17,70% 14,36% 32,90% 16,59% 49,49% 0,36% 16,38% 12,84% 29,58% 14,74% 44,32% ASSETS Cash Accounts receivable Inventory Current assets Net fixed assets Total assets 5,69% 5,01% 4,93% 2,95% 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 0,92% 6,60% 8,91% 15,51% 11,33% 32.79% 44,12% 59,63% 0,88% 8,77% 6,96% 15,73% 8,86% 30,30% 39,16% 54,89% 2,34% 1,00% 6,00% 17,23% 23,23% 7,02% 27,57% 34,60% 57,83% 7,13% 1,78% 1,78% 1,01% 9,91% 11,31% 21.23% 5,34% 22,93% 28,27% 49,49% 6,02% 3,56% 1,42% 1,16% 10,74% 7,63% 18,37% 4,27% 21,68% 25,95% 44,32% LIABILITIES AND EQUITIES Accounts payable Notes payable Current Portion LTD Accruals Current liabilites Long-term debt Total Liabilities Common stock Retained earnings Equities Total capital Activer Windows Accdez aux paramtres pour activer Windows CeramicSpreadsheet PRT 90% Taper ici pour rechercher x] 640) 21:34 05/04/2021 Ceramic Structures Engineering, Inc. Example Case Ceramic Structures has experienced rapid growth over the past several years. Sales are expected to grow at 15% per year for the next three years. Sales growth has been fueled by aggressive pricing as well as increased use of ceramics in high performance engines. Asset growth has been financed by internal funds as well as the increased use of debt. At the end of 2018, the debt was restructured with a new 10% seven-year loan with principal payments of $1 million per year. In addition a $1.5 million working capital line was negotiated in 2017. It was increased to $2.5 million in 2019 and $3.5 million in 2020. Interest is charged at prime + 1%. (For class, we will use 9%.) Cash balances will be kept around $250,000 and the credit line will average $2 million. 1. Prepare pro-forma statements for Ceramic and determine their need for funds for the years 2021-2023. xHSS ? 6 X Ceramic Spreadsheet (1) [Mode protg) - Excel (chec de l'activation du produit) DONNES RVISION AFFICHAGE FICHIER ACCUEIL INSERTION MISE EN PAGE FORMULES Connexion G75 f Current Portion LTD D E F G H I J K L M N 0 2019 2020 Forecast 2021 2022 2023 470 9 946 8 071 18 487 9 325 27 812 250 11 507 9 020 20 777 10 354 31 131 ASSETS Cash Accounts receivable Inventory Current assets Net fixed assets Total assets A B 1 CERAMIC STRUCTURES ENGINEERING, INC. 2 BALANCE SHEETS 3 AT DECEMBER 31, 4 5 2016 2017 2018 6 7 2 363 1 178 899 8 4 779 5 643 8 467 9 3 450 5213 6 985 10 10 592 12 033 16 351 11 5 193 6 551 8 235 12 15 785 18 584 24 586 13 14 15 1 505 1 669 2 139 16 1 000 17 1 000 18 243 299 425 19 1 748 2 968 2 564 20 2 358 2 358 7 358 21 4 106 5 326 22 3 000 3 000 3 000 23 8 679 10 258 11 775 24 11 679 13 258 14 775 25 15 785 18 584 24 696 26 4 004 1 000 1 000 566 5 570 6358 11 928 3 000 12 884 15 884 27 812 4 226 2 500 1 000 818 7 544 5358 12 902 3 000 15 229 18 229 31 131 LIABILITIES AND EQUITIES Accounts payable Notes payable Current Portion LTD Accruals Current liabilites Long-term debt Total Liabilities Common stock Retained earnings Equities Total capital 9921 27 Cumulative Need for Funds Annual Need for For Funds 28 29 1 Activer Windows Accdez aux paramtres pour activer Windows CeramicSpreadsheet PRT 90 % % O Taper ici pour rechercher + 21:33 05/04/2021 P P9 x] 640) x H 5 ? 6 X Ceramic Spreadsheet (1) [Mode protg) - Excel (chec de l'activation du produit) DONNES RVISION AFFICHAGE FICHIER ACCUEIL INSERTION MISE EN PAGE FORMULES Connexion G75 : f Current Portion LTD B C D E F H I J K L M N 0 27 G Cumulative Need for Funds Annual Need for For Funds 28 29 2016 2017 2018 2019 2020 2021 2022 2023 30 31 32 33 26 470 20 087 6 383 33 856 25 386 8 471 42 709 32 199 10 510 56 196 45 125 11 071 70 245 55 560 14 685 Sales Cost of Goods Gross Profit 34 35 36 37 38 39 40 41 42 1 324 1 642 1 064 289 4318 2065 330 1 735 798 937 1 693 1 726 1 342 342 5 103 3 368 444 2 924 1 345 1 579 2 563 1 815 1 666 487 6 531 3 979 1 170 2 809 1 292 1 517 3 372 1 910 1 910 703 7 895 3 176 1 123 2 053 944 1 109 4 215 2010 2 121 772 9 118 5 566 1 223 4 344 1 998 2 346 G and A expenses Fixed operating expenses Depreciation Miscellaneous Total operating expenses EBIT Interest EBT Taxes Net income 43 44 45 46 47 48 49 100,00% 75,89% 24,11% 100,00% 74.98% 25,02% 100,00% 75.39% 24,61% 100,00% 80,30% 19,70% 100,00% 79,09% 20,91% Sales Cost of Goods Gross Profit 50 51 52 53 54 5,00% 6,20% 4,02% 1,09% 5,00% 5,10% 3,96% 1,01% 6,00% 4,25% 3,90% 1,14% 6,00% 3,40% 3,40% 1,25% 6,00% 2,86% 3,02% 1,10% G and A expenses Fixed operating expenses Depreciation Miscellaneous 55 :1 Tab Activer Windows Accdez aux paramtres pour activer Windows. CeramicSpreadsheet PRT 90 % % O Taper ici pour rechercher + 21:34 05/04/2021 P P9 x] 640) xHSS ? 6 X Ceramic Spreadsheet (1) [Mode protg) - Excel (chec de l'activation du produit) DONNES RVISION AFFICHAGE FICHIER ACCUEIL INSERTION MISE EN PAGE FORMULES Connexion G75 f Current Portion LTD F H I J K L M N 0 55 56 57 58 59 60 61 62 63 64 65 66 67 1,09% 16,31% 7,80% 1,25% 6,55% 46,00% 3,54% B 1,01% 15,07% 9,95% 1,31% 8,64% 46,00% 4,66% 1,14% 15,29% 9,32% 2.74% 6,58% 46.00% 3,55% D 1.25% 14,05% 5,65% 2.00% 3,65% 46,00% 1,97% E 1,10% 12.98% 7,92% 1,74% 6,18% 46.00% 3,34% Miscellaneous Total operating expenses EBIT Interest EBT Taxes Net income 8.93% 18,06% 13,03% 40,01% 19,62% 59,63% 3,48% 16,67% 15,40% 35,54% 19,35% 54,89% 2,10% 19,83% 16,35% 38.28% 19,28% 57,57% 0,84% 17,70% 14,36% 32,90% 16,59% 49,49% 0,36% 16,38% 12,84% 29,58% 14,74% 44,32% ASSETS Cash Accounts receivable Inventory Current assets Net fixed assets Total assets 5,69% 5,01% 4,93% 2,95% 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 0,92% 6,60% 8,91% 15,51% 11,33% 32.79% 44,12% 59,63% 0,88% 8,77% 6,96% 15,73% 8,86% 30,30% 39,16% 54,89% 2,34% 1,00% 6,00% 17,23% 23,23% 7,02% 27,57% 34,60% 57,83% 7,13% 1,78% 1,78% 1,01% 9,91% 11,31% 21.23% 5,34% 22,93% 28,27% 49,49% 6,02% 3,56% 1,42% 1,16% 10,74% 7,63% 18,37% 4,27% 21,68% 25,95% 44,32% LIABILITIES AND EQUITIES Accounts payable Notes payable Current Portion LTD Accruals Current liabilites Long-term debt Total Liabilities Common stock Retained earnings Equities Total capital Activer Windows Accdez aux paramtres pour activer Windows CeramicSpreadsheet PRT 90% Taper ici pour rechercher x] 640) 21:34 05/04/2021