50.1K

Verified Solution

Link Copied!

given the following case information and financial statements complete the proforma assumptions for 2007 and proforma income statement. Make sure to show equations used and not just numbers

It was late October 2006 when Brad MacDougall, account manager at the Canadian Commercial Bank (CCB) in Barron, Ontario, reviewed a loan request for $200,000 from Liz Bodie. As owner of Bodie Industrial Supply Inc. (BIS), a distributor of commercial-grade tools, parts and equipment, Bodie requested the long-term loan to cover construction costs associated with a major expansion to BIS's warehouse. BIS was currently operating without a line of credit, and MacDougall wondered whether the business could generate enough cash to cover its expenses, including the new loan payments. MacDougall had to be thorough in his analysis because he knew Bodie was relying on financing from the bank for the expansion COMPANY BACKGROUND In 2003, Liz Bodie purchased the business from its previous owner and changed the company's name to reflect its new ownership. Prior to acquiring the business, Bodie had worked for a variety of different companies, including one of BIS's current metropolitan-based competitors. During those years, it was Bodie's long-term ambition to operate her own business. Her first entrepreneurial venture was a franchised retail hardware store that she operated for several years before selling it to acquire BIS. Drawing on her work experience, Bodie expanded BIS into a full-service distributor of top-line, brand name, new and used certified machine tools, maintenance parts and related equipment for the construction, utility and farming markets. The company strove to uphold its reputation as the single source for all industrial equipment needs. Although BIS conducted business country-wide, BIS's hometown of Barron, approximately 100 kilometres northeast of a large metropolitan city, was currently the largest and fastest growing industrial centre in its region with a population of 135,000. BIS's customers were primarily the industrial maintenance departments of medium- to large-sized corporations in the manufacturing, commercial construction and engineering industries. BIS also attracted some high- margin retail business from farming communities and summer cottage trade in the surrounding area, Bodie was pleased with BIS's progress since she had purchased it and had experienced considerable success in growing sales. The business's success was such that in August 2005, BIS purchased its rented Page 2 9B06N021 facilities when the landlord offered the property at what Bodie regarded as "a very attractive price." MacDougall had arranged the loan for the land and building purchase. The loan was currently in good standing with all payments up to date.' BIS's success continued, and by June 2006, monthly sales volumes averaged more than $200,000. (See Exhibits 1 to 5 for historical financial data, cash flow statements, and selected company and industry financial ratios.) Bodie was also proud of the company's reputation for dependability and integrity. She believed her success was due largely to the personalized service and engineering advice she offered BIS's customers. Bodie also noted that an important factor in attracting new customers and building lasting relationships was BIS's success in obtaining exclusive rights to offer the products of some top-line brand-name manufacturers. She also believed that maintaining good supplier relations with those manufacturers who granted BIS exclusivity was a key element to future success. THE COMPETITION Until late 2005, BIS had been the only distributor of machine tools, parts and equipment in Barron. Minimal competition had come from salespeople operating from out-of-town warehouses, but in the fall of 2005, a new distributor opened in downtown Barron. Bodie believed that the new competitor would compete directly with only a small portion of her business because of BIS's exclusive brand distribution rights and complete line of specialized products. Additionally, this new distributor had not yet earned a reputation comparable to that of BIS for dependable service. Although not necessarily considered a direct threat, the entrance of big-box retailers did increase competition among Canadian wholesalers and retailers alike. Big-box retailers competing within the home improvement, construction and building maintenance industries, such as Home Depot, RONA and Lowes, opened stores from 3,500 square metres to 15,000 square metres in size. Not only did these retailers offer lower prices, but they also provided a wide variety of products for retail and commercial consumers. At present, there were three major retailers in Barron with a fourth scheduled to open in the coming months. Although these retailers did not carry specialized, large and commercial-grade industrial equipment, they did offer a wide selection of construction supplies typically used by many of BIS's smaller, higher margin accounts. Bodie was still uncertain how and to what extent mass retailers and wholesalers could influence the business Bodie believed a more eminent threat was the rapid growth of Internet-based selling. Many of BIS's competitors now had websites with online ordering and e-commerce capabilities that provided added convenience and quicker service to customers looking to order general use supplies. Online selling also reduced the need for customers to do business with a local supplier, potentially vastly increasing BIS's pool of competition. Although BIS did currently have a company website, it was purely informational FINANCIAL PROJECTIONS FOR EXPANSION Although market information was limited, Bodie thought that BIS had about 35 per cent of the machine tool and equipment market in Barron and the surrounding region. Given the existing market potential, she believed sales could not increase beyond S4 million without expanding BIS's geographical market. For the next two years, she projected sales at $2.8 million for the year ending January 31, 2007, and $3.2 million for the year ending January 31, 2008 Appears as CCB mortgage payable on the balance sheet secured by land and building Bodie's major concern was the cramped space in BIS's warehouse. She believed the business could not handle any significant increases in inventory, given its present facilities. In order to maintain BIS's standard of service and delivery, Bodie wanted to add a warehouse extension, estimated at a cost of $200,000, by the beginning of November If the loan was approved, principal payments would be $40,000 each year, beginning February 1, 2007, and annual interest payments would be approximately $13,000 for the first two years. In addition to these new loan payments, the combined principal payment on the total amount of all existing long-term debt for fiscal 2007 and 2008 would be $36,528 and $71,924 respectively. The associated annual interest payments on these outstanding liabilities would be $20,914 for 2007 and $35,528 for 2008. Other items on the statement of earnings would remain roughly the same percentage of sales as was experienced in 2006. Bodie believed the days of inventory would not change after the warehouse expansion, even though sales were expected to increase. She did consider, however, that increasing inventory levels could present a risk to cashflows if sales did not increase proportionately. One of Bodie's top priorities was to reduce the age of BIS's accounts payables to 60 days before the end of the next fiscal year. If this was not accomplished, some of BIS's exclusive distribution agreements could be jeopardized. Although Bodie thought BIS could reduce its days of accounts payables as sales and profits grew, she wondered whether this objective was attainable within the next year. For this reason, Bodie anticipated that BIS could be in need of a line of credit to support the increased working capital requirements in addition to the requested new long-term loan. THE DECISION BIS had shown strong growth results within the past three years, but MacDougall was concerned about providing more financing in addition to the loan the Canadian Commercial Bank had already extended in August 2005. Furthermore, he was aware of the pressure BIS's preferred suppliers were putting on Bodie to reduce days of accounts payables. Could BIS support such an aggressive reduction at this time? Without a short-term source of financing, MacDougall wondered whether BIS would even be able to generate the cash required to cover its debt payments over the next few years -18460 -76350 -56600 76 Ending retained Eamings 77 78 79 80 100% 71.45% 28.55% 100% 73.24% 26.76% 100% 69.26% 30.74% 14.81% 0.54% 15.87% 1.39% 81 Net Sales 82 COGS 83 Gross Profit 84 85 Opt Exp 86 Wages and Commissions 87 Rent 88 Provision for doubtful accounts 89 General Selling Exp 90 General Admin Exp 91 Amortization 92 Total Opt Exp 93 94 Interest Exp 95 96 Net Inc before tax 97 Income Tax 98 Net Inc (loss) 99 19.74% 2.09% 0.72% 3.13% 8.22% 1.15% 6.43% 1.18% 24.11% 1.72% 6.55% 0.64% 26.17% 33.90% 1.63% 1.83% 2.93% 2.80% -1.23% -6.08% 2.80% -1.23% -6.08% se X Arial 10 ' ' == @ Merge Wrap Paste BIU - - - - Clipboard 12 Font Alignment J140 C 0.14% 32.25% 45.19% 0.17% 77.74% 2.81% 32.41% 54.98% 0.49% 90.69% 14.40% 21.51% 51.71% 0.51% 88.13% A 100 101 Assets 102 103 Current Assets 104 Cash 105 Acc. Rec (net) 106 Inv. 107 Prepaid Exp 108 Total CL 109 110 Fixed Assets 111 Land 112 Autom les 113 Building 114 Equipment 115 Subtotal 116 Less: Accumulated amortization 117 Net Fixed Assets 118 119 Other Assets 120 Goodwill 121 Deferred Charges 122 123 Total Assets 124 125 Liabilitites 126 Current li 5.52% 5.75% 5.80% 4.56% 11.71% 0.38% 22.24% 3.30% 19.14% 5.52% 1.66% 3.87% 5.75% 0.00% 5.75% 2.86% 0.26% 4.82% 0.62% 5.02% 1.10% 100.00% 100.00% 100.00% X Arial v 10 ' ' > as Wrap Text Paste B 1 0 - 1 - 2 - A = = = += = Merge &c Clipboard Font Alignment J140 B C D E 47.85% 3.41% 51.26% 46.73% 1.90% 48.63% 30.90% 2.64% 33.55% A 126 Current Lia 127 Acc Payable 128 Other CL 129 Total CL 130 131 Long Term Lia 132 Bank Loan 133 Transport Loan 134 Mortgage Payable 135 CCB mortgage payable 136 Total LTL 137 138 Equity 139 Common Stock 140 Retained Earnings 141 Total Equity 142 7.28% 0.50% 16.81% 16.39% 40.98% 16.35% 2.85% 28.37% 21.24% 5.06% 32.89% 47.57% 59.19% 9.52% -1.76% 7.76% 16.07% - 12.27% 3.80% 16.74% -9.47% 7.26% board Font Alignment 4 fo . A B C E D 2004 Ratios 2006 2005 LO 1.517 0.630 124 1.865 0.724 91 2.627 1.070 105 -7 A. Liquidity Ratio 78 49 Current Ratio 50 Quick Ratio 151 Acc Payable Days 152 153 154 B. Profitability Ratio 155 156 Return on Equity 157 Gross Profit Mg 158 Operating Margin 159 Net Profit Margin 160 161 162 C. Efficiency 163 164 TAT 165 FAT 166 Avg. Collection Period 167 Inventory Days 168 57.89% 28.55% 4.43% 2.80% -19.75% -56.60% 26.76% 30.74% 0.59% -3.16% -1.23% 6.08% 1.97 18.37 48 2.57 54.79 38 101 1.56 27.2 50 175 101 Arial Paste - OAE Merge & Center $ - % Conditional Formatas Cell Formatting Table Styles Styles Clipboard Font Alignment Number K27 ASSUMPTIONS for Proforma Year 2007 D G H 1 N IN THIS PAGE AT THE DESIGNTAED PART HIGHLIGHTED: SCROLL DOWN BELOW: 2 3 it to right 4 5 Sales Growth Sales $ 6 COGS as (%) of Sales 69.26% 73 24% 71.45% COGS as () of Sales 7 8 Wages and Commissions as () of Sales 19.74% 15.87% 14 81% Wages and Commissions as () of Sales 9 Rent) 2.09% 1.39% 0.54% Rent (5) 10 Provision for doubtful accounts (N) 0.72% Provision for doubtful accounts (N) 11 General Selling Exp($) 3.13% 1.72% 1.159 General Selling Exp(S) 12 General Admin Exp 8 22% 6.55% 6.43% General Admin Exp (%) 13 Amortization (%) 0.64% 1.18% Amortization (5) 14 15 Interest Exp 2.93% 1.839 1.63% Interest Exp 16 Income tax Income tax 17 18 cash as (S) of Sales 14.40% 2.81% 0.14% cash as (%) of Sales 19 ACP days 105 91 124 ACP days 20 Inventory Days 175 101 101 Inventory Days 21 GFA GFA (only Buliding, as other remain same) 22 Accumulated amortization Accumulated amortization 23 Acc Payable Acc Payable 24 D 24 Proforma come statements of Jan 2007 28 29 30 Net Sales COXGs Gross Profit 930110 1509920 2006020 544190 1171700 1478840 285920 428160 58180 32 306170 11100 1900 183620253910 22200 6710 29100 27500 7640 104720 10300 31.090 41.00 Wapes and Commissions Rent Provision for doubtful accounts General Selling Exp General in Exp Amortization Total Opt Exp SO 2310 132050 24 40 27230 2220 Interest Exp 33750 56600 -19750 57890 44 45 Netic before tax Income Tax Net Inc 0750 57890 It was late October 2006 when Brad MacDougall, account manager at the Canadian Commercial Bank (CCB) in Barron, Ontario, reviewed a loan request for $200,000 from Liz Bodie. As owner of Bodie Industrial Supply Inc. (BIS), a distributor of commercial-grade tools, parts and equipment, Bodie requested the long-term loan to cover construction costs associated with a major expansion to BIS's warehouse. BIS was currently operating without a line of credit, and MacDougall wondered whether the business could generate enough cash to cover its expenses, including the new loan payments. MacDougall had to be thorough in his analysis because he knew Bodie was relying on financing from the bank for the expansion COMPANY BACKGROUND In 2003, Liz Bodie purchased the business from its previous owner and changed the company's name to reflect its new ownership. Prior to acquiring the business, Bodie had worked for a variety of different companies, including one of BIS's current metropolitan-based competitors. During those years, it was Bodie's long-term ambition to operate her own business. Her first entrepreneurial venture was a franchised retail hardware store that she operated for several years before selling it to acquire BIS. Drawing on her work experience, Bodie expanded BIS into a full-service distributor of top-line, brand name, new and used certified machine tools, maintenance parts and related equipment for the construction, utility and farming markets. The company strove to uphold its reputation as the single source for all industrial equipment needs. Although BIS conducted business country-wide, BIS's hometown of Barron, approximately 100 kilometres northeast of a large metropolitan city, was currently the largest and fastest growing industrial centre in its region with a population of 135,000. BIS's customers were primarily the industrial maintenance departments of medium- to large-sized corporations in the manufacturing, commercial construction and engineering industries. BIS also attracted some high- margin retail business from farming communities and summer cottage trade in the surrounding area, Bodie was pleased with BIS's progress since she had purchased it and had experienced considerable success in growing sales. The business's success was such that in August 2005, BIS purchased its rented Page 2 9B06N021 facilities when the landlord offered the property at what Bodie regarded as "a very attractive price." MacDougall had arranged the loan for the land and building purchase. The loan was currently in good standing with all payments up to date.' BIS's success continued, and by June 2006, monthly sales volumes averaged more than $200,000. (See Exhibits 1 to 5 for historical financial data, cash flow statements, and selected company and industry financial ratios.) Bodie was also proud of the company's reputation for dependability and integrity. She believed her success was due largely to the personalized service and engineering advice she offered BIS's customers. Bodie also noted that an important factor in attracting new customers and building lasting relationships was BIS's success in obtaining exclusive rights to offer the products of some top-line brand-name manufacturers. She also believed that maintaining good supplier relations with those manufacturers who granted BIS exclusivity was a key element to future success. THE COMPETITION Until late 2005, BIS had been the only distributor of machine tools, parts and equipment in Barron. Minimal competition had come from salespeople operating from out-of-town warehouses, but in the fall of 2005, a new distributor opened in downtown Barron. Bodie believed that the new competitor would compete directly with only a small portion of her business because of BIS's exclusive brand distribution rights and complete line of specialized products. Additionally, this new distributor had not yet earned a reputation comparable to that of BIS for dependable service. Although not necessarily considered a direct threat, the entrance of big-box retailers did increase competition among Canadian wholesalers and retailers alike. Big-box retailers competing within the home improvement, construction and building maintenance industries, such as Home Depot, RONA and Lowes, opened stores from 3,500 square metres to 15,000 square metres in size. Not only did these retailers offer lower prices, but they also provided a wide variety of products for retail and commercial consumers. At present, there were three major retailers in Barron with a fourth scheduled to open in the coming months. Although these retailers did not carry specialized, large and commercial-grade industrial equipment, they did offer a wide selection of construction supplies typically used by many of BIS's smaller, higher margin accounts. Bodie was still uncertain how and to what extent mass retailers and wholesalers could influence the business Bodie believed a more eminent threat was the rapid growth of Internet-based selling. Many of BIS's competitors now had websites with online ordering and e-commerce capabilities that provided added convenience and quicker service to customers looking to order general use supplies. Online selling also reduced the need for customers to do business with a local supplier, potentially vastly increasing BIS's pool of competition. Although BIS did currently have a company website, it was purely informational FINANCIAL PROJECTIONS FOR EXPANSION Although market information was limited, Bodie thought that BIS had about 35 per cent of the machine tool and equipment market in Barron and the surrounding region. Given the existing market potential, she believed sales could not increase beyond S4 million without expanding BIS's geographical market. For the next two years, she projected sales at $2.8 million for the year ending January 31, 2007, and $3.2 million for the year ending January 31, 2008 Appears as CCB mortgage payable on the balance sheet secured by land and building Bodie's major concern was the cramped space in BIS's warehouse. She believed the business could not handle any significant increases in inventory, given its present facilities. In order to maintain BIS's standard of service and delivery, Bodie wanted to add a warehouse extension, estimated at a cost of $200,000, by the beginning of November If the loan was approved, principal payments would be $40,000 each year, beginning February 1, 2007, and annual interest payments would be approximately $13,000 for the first two years. In addition to these new loan payments, the combined principal payment on the total amount of all existing long-term debt for fiscal 2007 and 2008 would be $36,528 and $71,924 respectively. The associated annual interest payments on these outstanding liabilities would be $20,914 for 2007 and $35,528 for 2008. Other items on the statement of earnings would remain roughly the same percentage of sales as was experienced in 2006. Bodie believed the days of inventory would not change after the warehouse expansion, even though sales were expected to increase. She did consider, however, that increasing inventory levels could present a risk to cashflows if sales did not increase proportionately. One of Bodie's top priorities was to reduce the age of BIS's accounts payables to 60 days before the end of the next fiscal year. If this was not accomplished, some of BIS's exclusive distribution agreements could be jeopardized. Although Bodie thought BIS could reduce its days of accounts payables as sales and profits grew, she wondered whether this objective was attainable within the next year. For this reason, Bodie anticipated that BIS could be in need of a line of credit to support the increased working capital requirements in addition to the requested new long-term loan. THE DECISION BIS had shown strong growth results within the past three years, but MacDougall was concerned about providing more financing in addition to the loan the Canadian Commercial Bank had already extended in August 2005. Furthermore, he was aware of the pressure BIS's preferred suppliers were putting on Bodie to reduce days of accounts payables. Could BIS support such an aggressive reduction at this time? Without a short-term source of financing, MacDougall wondered whether BIS would even be able to generate the cash required to cover its debt payments over the next few years -18460 -76350 -56600 76 Ending retained Eamings 77 78 79 80 100% 71.45% 28.55% 100% 73.24% 26.76% 100% 69.26% 30.74% 14.81% 0.54% 15.87% 1.39% 81 Net Sales 82 COGS 83 Gross Profit 84 85 Opt Exp 86 Wages and Commissions 87 Rent 88 Provision for doubtful accounts 89 General Selling Exp 90 General Admin Exp 91 Amortization 92 Total Opt Exp 93 94 Interest Exp 95 96 Net Inc before tax 97 Income Tax 98 Net Inc (loss) 99 19.74% 2.09% 0.72% 3.13% 8.22% 1.15% 6.43% 1.18% 24.11% 1.72% 6.55% 0.64% 26.17% 33.90% 1.63% 1.83% 2.93% 2.80% -1.23% -6.08% 2.80% -1.23% -6.08% se X Arial 10 ' ' == @ Merge Wrap Paste BIU - - - - Clipboard 12 Font Alignment J140 C 0.14% 32.25% 45.19% 0.17% 77.74% 2.81% 32.41% 54.98% 0.49% 90.69% 14.40% 21.51% 51.71% 0.51% 88.13% A 100 101 Assets 102 103 Current Assets 104 Cash 105 Acc. Rec (net) 106 Inv. 107 Prepaid Exp 108 Total CL 109 110 Fixed Assets 111 Land 112 Autom les 113 Building 114 Equipment 115 Subtotal 116 Less: Accumulated amortization 117 Net Fixed Assets 118 119 Other Assets 120 Goodwill 121 Deferred Charges 122 123 Total Assets 124 125 Liabilitites 126 Current li 5.52% 5.75% 5.80% 4.56% 11.71% 0.38% 22.24% 3.30% 19.14% 5.52% 1.66% 3.87% 5.75% 0.00% 5.75% 2.86% 0.26% 4.82% 0.62% 5.02% 1.10% 100.00% 100.00% 100.00% X Arial v 10 ' ' > as Wrap Text Paste B 1 0 - 1 - 2 - A = = = += = Merge &c Clipboard Font Alignment J140 B C D E 47.85% 3.41% 51.26% 46.73% 1.90% 48.63% 30.90% 2.64% 33.55% A 126 Current Lia 127 Acc Payable 128 Other CL 129 Total CL 130 131 Long Term Lia 132 Bank Loan 133 Transport Loan 134 Mortgage Payable 135 CCB mortgage payable 136 Total LTL 137 138 Equity 139 Common Stock 140 Retained Earnings 141 Total Equity 142 7.28% 0.50% 16.81% 16.39% 40.98% 16.35% 2.85% 28.37% 21.24% 5.06% 32.89% 47.57% 59.19% 9.52% -1.76% 7.76% 16.07% - 12.27% 3.80% 16.74% -9.47% 7.26% board Font Alignment 4 fo . A B C E D 2004 Ratios 2006 2005 LO 1.517 0.630 124 1.865 0.724 91 2.627 1.070 105 -7 A. Liquidity Ratio 78 49 Current Ratio 50 Quick Ratio 151 Acc Payable Days 152 153 154 B. Profitability Ratio 155 156 Return on Equity 157 Gross Profit Mg 158 Operating Margin 159 Net Profit Margin 160 161 162 C. Efficiency 163 164 TAT 165 FAT 166 Avg. Collection Period 167 Inventory Days 168 57.89% 28.55% 4.43% 2.80% -19.75% -56.60% 26.76% 30.74% 0.59% -3.16% -1.23% 6.08% 1.97 18.37 48 2.57 54.79 38 101 1.56 27.2 50 175 101 Arial Paste - OAE Merge & Center $ - % Conditional Formatas Cell Formatting Table Styles Styles Clipboard Font Alignment Number K27 ASSUMPTIONS for Proforma Year 2007 D G H 1 N IN THIS PAGE AT THE DESIGNTAED PART HIGHLIGHTED: SCROLL DOWN BELOW: 2 3 it to right 4 5 Sales Growth Sales $ 6 COGS as (%) of Sales 69.26% 73 24% 71.45% COGS as () of Sales 7 8 Wages and Commissions as () of Sales 19.74% 15.87% 14 81% Wages and Commissions as () of Sales 9 Rent) 2.09% 1.39% 0.54% Rent (5) 10 Provision for doubtful accounts (N) 0.72% Provision for doubtful accounts (N) 11 General Selling Exp($) 3.13% 1.72% 1.159 General Selling Exp(S) 12 General Admin Exp 8 22% 6.55% 6.43% General Admin Exp (%) 13 Amortization (%) 0.64% 1.18% Amortization (5) 14 15 Interest Exp 2.93% 1.839 1.63% Interest Exp 16 Income tax Income tax 17 18 cash as (S) of Sales 14.40% 2.81% 0.14% cash as (%) of Sales 19 ACP days 105 91 124 ACP days 20 Inventory Days 175 101 101 Inventory Days 21 GFA GFA (only Buliding, as other remain same) 22 Accumulated amortization Accumulated amortization 23 Acc Payable Acc Payable 24 D 24 Proforma come statements of Jan 2007 28 29 30 Net Sales COXGs Gross Profit 930110 1509920 2006020 544190 1171700 1478840 285920 428160 58180 32 306170 11100 1900 183620253910 22200 6710 29100 27500 7640 104720 10300 31.090 41.00 Wapes and Commissions Rent Provision for doubtful accounts General Selling Exp General in Exp Amortization Total Opt Exp SO 2310 132050 24 40 27230 2220 Interest Exp 33750 56600 -19750 57890 44 45 Netic before tax Income Tax Net Inc 0750 57890

Answer & Explanation Solved by verified expert

See Answer