• There are three typos in reference to timing:

• Page 2, paragraph 2, sentence 2 should read: The businesss success was such that in August 2006, BIS purchased..

• Page 3, paragraph 4, sentence 1 should read: If the loan was approved, principle payments would be $40,000 each year, beginning February 1, 2008, and.

• Page 6, Exhibit 3, footnote 2 should read: Balance owing on a truck purchased in 2001. Loan was repaid in April 2007.

I am looking for an excel spreadsheet that shows all cash flow tables with the equation along with it.

BODIE INDUSTRIAL SUPPLY INC. Alle Harvey revised this case foriginally prepared by Richardson D G. Burgoyne and I.A. Humphrey under the supervision of Elizabeth MA Grasby solely to provide material for class discussion The authors do not intend to frustrate the effective ineffective handling of a managerial situation. The authors may have disguised certain names and other devtifying information to protect confidentiality Ivey Management Services prohibits any form of reproduction, storage or bransmit without its written permission. This materialis of covered under authoritation from CanCopy or any reproduction nights organization. To order copies or request permission to reproduce material contact Ivy Publishing. ley Management Services, Richard Ivey School of Business The University of Westem Ontario, London, Ontario Canada, NGAK.phone (519) 661-3208 fax (519) 661-3882 e-malcaseiveyo.ca Version: (A1 2007-01-12 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 Bodic purchased the business from its previous owner and changed the company's name to reflect its new ownership, Prior to acquiring the business, Bodic 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 facilities when the landlord offered the property at what Bodic 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.) Bodic 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 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 camed 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 Bodic 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 c-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 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 s20,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 concemed 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 Bodic 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. Exhibit 1 STATEMENT OF EARNINGS (for years ending January 31) 2006 2005 2004 Net sales Cost of goods sold Gross profit $2,066,820 1.476,840 $589,980 $1,599,920 1.171.760 S428,160 $930,110 644.190 $285,920 $306,170 11.100 $253.910 22.200 Operating expenses Wages and commissions Rent' Provision for doubtful accounts General selling expenses General administrative expenses Amortization Total operating expenses $183,620 19,430 6,710 29,100 76,430 23,860 132.850 24.160 498,340 27.560 104,720 10.000 418,690 315,290 Interest expense 33.750 29.220 27,230 Net income before tax Income tax $57,890 $(19,750) S(56,600) Net income (loss) $57.890 $119.750) S(36.600) 2004 Exhibit 2 STATEMENTS OF RETAINED EARNINGS (for years ending January 31) 2006 2005 Beginning retained carings S(76,350) $(56.600) Add: net income 57.890 (19,750) Less: dividends Ending retained carnings $(18,460) S(76.350) s (56,600) S(56.600) With the purchase of the land and building in August 2005, rent expense had been eliminated Previous losses were carried forward, offsetting allowing for 2006. BISs estimated tax rate was 23 percent 2004 $ 86,000 128.500 308,980 3.020 S526,560 Exhibit BALANCE SHEETS (as at January 31) 2006 2005 $ 1,420 $ 17.510 338.690 201.670 474.630 342,080 1.740 3.020 $ 816,480 $ 564.280 $ 60,880 47.850 34,350 122.940 3.990 $ 235,660 $ 34,350 140 $ 201,000 $ 24,050 30,000 2.750 S 1.050.230 34,350 ASSETS Current assets: Cash Accounts receivable.net Inventory Prepaid expenses Total current assets Fixed assets: Land Automobiles Building Equipment Subtotal Lessaccumulated amortization Net fixed assets Other assets: Goodwill Deferred charges TOTAL ASSETS LIABILITIES Current liabilities: Accounts payable Other current liabilities Total current liabilities Long-term liabilities: Bank loan Transport loan Mortgage payable CCB mortgage payable Total long-term liabilities Equity Common stock Retained earnings Total equity TOTAL LIABILITIES & EQUITY $34,350 $ 34,350 30,000 6.570 $597.480 $290,740 $300.590 $101.700 17.780 176,510 $ 502.300 $ 538,350 $ 76,500 5.210 176,510 172.120 $ 480,340 $ 100.000 S 81.540 S1.090.230 $184,640 15.790 5200,430 $126.900 30,240 196,510 $353,650 $100,000 (56.100) $ 43,400 S597.450 $295,940 $100,000 $ 23,650 5622.180 Unsecured loaned to the Provincial Bank of Commerce Blonde ingen and purchased in 2001 Lowas reading 2006 Loan from previous One. Secured by Inventory, mounted in February 2000 as part of the purchase aparent Exhibit 4 FINANCIAL RATIOS 2006 Selected Industry Ratios Where available) 2006 2005 2004 100% 100% 71.5% 28.5% 100% 73.2% 26.8% 100% 67.9% 32.1% 30.7% PROFITABILITY Net sales Cost of goods sold Gross profit Operating expenses Wages and commissions Rent Provision, doubtful accounts General selling expenses General & admin, expenses Amortization Total operating expenses 14.8% 0.5% 15.9% 1.4% 19.7% 2.1% 0.7% 3.1% 8.2% 1.2% 1.7% 6.5% 0.6% 26.2% 1.2% 24.1% 33.9% Interest expense 1.6% 1.8% 2.9% 2.8% n/a 2.8% 110.1% n/a 12.3% Net income (los) Retum on average equity LIQUIDITY Current ratio Acid test Working capital 1.52:1 0.63 : 1 $278,130 1.86:1 0.72:1 $261,690 2.63 : 1 1.07:1 $326,130 18:1 0.6:1 EFFICIENCY Age of receivables Age of inventory Age of payables 52.4 days 60 days 117 days 114 days 46 days 107 days 88 days 50 days 175 days 93 days STABILITY Net worth to total assets Interest coverage 7.8% 2.7 X 3.8% 0.3 X 7.3% n/a 48.07% 3.6 X 2004-05 72% GROWTH Sales Net income Total assets Equity 2005-06 29% n/a 69% 245% (46%) Exhibit 5 STATEMENT OF CASH FLOWS (for years ending January 31) 2006 2005 OPERATIONS Net income $ 57,890 S(19,750) Adjustments to cash basis: Amortization Accounts receivable, net Inventory Prepaid expenses Accounts payable Other current liabilities 24,360 (137,020) (132,550) 1,280 211,760 24,000 10,300 (73,170) (33,100) 106,100 (3,940) Net cash flow from operations $ 49,720 S (13,560) S(25,200) (12,520) S(25,200) (12,510) (20,000) 172,120 FINANCING ACTIVITIES Bank loan Transport loan Mortgage payable CCB mortgage payable Net cash flow from financing INVESTING ACTIVITIES Fixed assets Deferred charges Net cash flow from investing $134,400 (57,710) $(201,310) 1.100 2.720 $(200,210) $ 2,720 Net cash flow Beginning cash (16,090) 17,510 (68,550) 86,060 Ending cash $ 1,420 $17.510 BODIE INDUSTRIAL SUPPLY INC. Alle Harvey revised this case foriginally prepared by Richardson D G. Burgoyne and I.A. Humphrey under the supervision of Elizabeth MA Grasby solely to provide material for class discussion The authors do not intend to frustrate the effective ineffective handling of a managerial situation. The authors may have disguised certain names and other devtifying information to protect confidentiality Ivey Management Services prohibits any form of reproduction, storage or bransmit without its written permission. This materialis of covered under authoritation from CanCopy or any reproduction nights organization. To order copies or request permission to reproduce material contact Ivy Publishing. ley Management Services, Richard Ivey School of Business The University of Westem Ontario, London, Ontario Canada, NGAK.phone (519) 661-3208 fax (519) 661-3882 e-malcaseiveyo.ca Version: (A1 2007-01-12 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 Bodic purchased the business from its previous owner and changed the company's name to reflect its new ownership, Prior to acquiring the business, Bodic 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 facilities when the landlord offered the property at what Bodic 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.) Bodic 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 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 camed 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 Bodic 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 c-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 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 s20,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 concemed 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 Bodic 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. Exhibit 1 STATEMENT OF EARNINGS (for years ending January 31) 2006 2005 2004 Net sales Cost of goods sold Gross profit $2,066,820 1.476,840 $589,980 $1,599,920 1.171.760 S428,160 $930,110 644.190 $285,920 $306,170 11.100 $253.910 22.200 Operating expenses Wages and commissions Rent' Provision for doubtful accounts General selling expenses General administrative expenses Amortization Total operating expenses $183,620 19,430 6,710 29,100 76,430 23,860 132.850 24.160 498,340 27.560 104,720 10.000 418,690 315,290 Interest expense 33.750 29.220 27,230 Net income before tax Income tax $57,890 $(19,750) S(56,600) Net income (loss) $57.890 $119.750) S(36.600) 2004 Exhibit 2 STATEMENTS OF RETAINED EARNINGS (for years ending January 31) 2006 2005 Beginning retained carings S(76,350) $(56.600) Add: net income 57.890 (19,750) Less: dividends Ending retained carnings $(18,460) S(76.350) s (56,600) S(56.600) With the purchase of the land and building in August 2005, rent expense had been eliminated Previous losses were carried forward, offsetting allowing for 2006. BISs estimated tax rate was 23 percent 2004 $ 86,000 128.500 308,980 3.020 S526,560 Exhibit BALANCE SHEETS (as at January 31) 2006 2005 $ 1,420 $ 17.510 338.690 201.670 474.630 342,080 1.740 3.020 $ 816,480 $ 564.280 $ 60,880 47.850 34,350 122.940 3.990 $ 235,660 $ 34,350 140 $ 201,000 $ 24,050 30,000 2.750 S 1.050.230 34,350 ASSETS Current assets: Cash Accounts receivable.net Inventory Prepaid expenses Total current assets Fixed assets: Land Automobiles Building Equipment Subtotal Lessaccumulated amortization Net fixed assets Other assets: Goodwill Deferred charges TOTAL ASSETS LIABILITIES Current liabilities: Accounts payable Other current liabilities Total current liabilities Long-term liabilities: Bank loan Transport loan Mortgage payable CCB mortgage payable Total long-term liabilities Equity Common stock Retained earnings Total equity TOTAL LIABILITIES & EQUITY $34,350 $ 34,350 30,000 6.570 $597.480 $290,740 $300.590 $101.700 17.780 176,510 $ 502.300 $ 538,350 $ 76,500 5.210 176,510 172.120 $ 480,340 $ 100.000 S 81.540 S1.090.230 $184,640 15.790 5200,430 $126.900 30,240 196,510 $353,650 $100,000 (56.100) $ 43,400 S597.450 $295,940 $100,000 $ 23,650 5622.180 Unsecured loaned to the Provincial Bank of Commerce Blonde ingen and purchased in 2001 Lowas reading 2006 Loan from previous One. Secured by Inventory, mounted in February 2000 as part of the purchase aparent Exhibit 4 FINANCIAL RATIOS 2006 Selected Industry Ratios Where available) 2006 2005 2004 100% 100% 71.5% 28.5% 100% 73.2% 26.8% 100% 67.9% 32.1% 30.7% PROFITABILITY Net sales Cost of goods sold Gross profit Operating expenses Wages and commissions Rent Provision, doubtful accounts General selling expenses General & admin, expenses Amortization Total operating expenses 14.8% 0.5% 15.9% 1.4% 19.7% 2.1% 0.7% 3.1% 8.2% 1.2% 1.7% 6.5% 0.6% 26.2% 1.2% 24.1% 33.9% Interest expense 1.6% 1.8% 2.9% 2.8% n/a 2.8% 110.1% n/a 12.3% Net income (los) Retum on average equity LIQUIDITY Current ratio Acid test Working capital 1.52:1 0.63 : 1 $278,130 1.86:1 0.72:1 $261,690 2.63 : 1 1.07:1 $326,130 18:1 0.6:1 EFFICIENCY Age of receivables Age of inventory Age of payables 52.4 days 60 days 117 days 114 days 46 days 107 days 88 days 50 days 175 days 93 days STABILITY Net worth to total assets Interest coverage 7.8% 2.7 X 3.8% 0.3 X 7.3% n/a 48.07% 3.6 X 2004-05 72% GROWTH Sales Net income Total assets Equity 2005-06 29% n/a 69% 245% (46%) Exhibit 5 STATEMENT OF CASH FLOWS (for years ending January 31) 2006 2005 OPERATIONS Net income $ 57,890 S(19,750) Adjustments to cash basis: Amortization Accounts receivable, net Inventory Prepaid expenses Accounts payable Other current liabilities 24,360 (137,020) (132,550) 1,280 211,760 24,000 10,300 (73,170) (33,100) 106,100 (3,940) Net cash flow from operations $ 49,720 S (13,560) S(25,200) (12,520) S(25,200) (12,510) (20,000) 172,120 FINANCING ACTIVITIES Bank loan Transport loan Mortgage payable CCB mortgage payable Net cash flow from financing INVESTING ACTIVITIES Fixed assets Deferred charges Net cash flow from investing $134,400 (57,710) $(201,310) 1.100 2.720 $(200,210) $ 2,720 Net cash flow Beginning cash (16,090) 17,510 (68,550) 86,060 Ending cash $ 1,420 $17.510