i need to bulid a imcome statement in Excel.
Financial Strategy.
Boston Physicians Devices In the spring of 2011, John Gruber, Chief Financial Officer of Boston Physicians Devices, was preparing for a meeting with Rex Dodge, vice-president and loan officer of Great New England Bank. Gruber was looking for a significant increase in Boston Physicians Devices' line of credit above the current $11 million maximum. Gruber examined the past three years of performance at Boston Physicians Devices (see Exhibit 1) and thought about its presentation Boston Physicians Devices (BPD) developed, manufactured and sold scientific medical instruments, needles and catheters which allowed rapid and less invasive access to a number of different organs and vessels. These products represented an alternative to traditional surgical procedures and allowed analysis or corrective treatment with less risk and trauma and at lower cost. An example of the products was catheters that could be introduced into a blood vessel and then manipulated through partially closed arteries or into the heart itself. BPD experienced extraordinary growth, fueled by heavy spending on research and development, necessary to maintain BPD's competitive advantage and rapid expansion of its sales force. Its technical staff was highly regarded for its development of new products with a wide range applications. The combination of state-of-the-art products and a rapidly growing market resulted in plosive sales growth in the last two years, sales had grown at an average rate of 50% Gruber wa confident that BPD sales could reach $45,000,000 in 2011 and $61,000,000 in 2012, given the environment of this rapidly expanding high-tech industry Management had financed BPD's growth by heavily relying on short-term credit. The only outstanding long-term debt had been contracted to finance the manufacturing facility and was being repaid at a rate of $175,000 per year. At the end of 2010, BPD had tapped $10,769,000 of the line of credit with Great New England Bank. This left only $231, 000 available to be drawn. Gruber believed that a significantly higher line of bank credit would be needed to meet BPD's financing needs over the next two years to the investment in net working capital that BPD was expecting to continue, th company was planning the construction of a second production facility adjacent to the current plant. e $2,400,000 facility would be built over the next two years with $1,200,000 being spent in 2011 and In addition another $1,200,000 being spent in 2012. Depreciation charges on the new production facility Boston Physicians Devices 212-070 not be taken until 2013. As for the other additional capital expenditures (other than the S plant), they were anticipated to be equal to the depreciation charges. Before visiting Great New England Bank and meeting Rex Dodge, John Gruber needed to analyze the financial situation of his company in the last three years and to determine how large a line of credit would be required to meet BPD's financing needs during this critical two-year period. Given the risk of its operations, the rate of interest would be approximately 10%, that is, 4% above the lowest rate charged by the bank on short-term loans ibit 1 Boston Physicians Devices: Financial Statements 2008-10 INCOME STATEMENT ($000 Net sales Cost of goods sold 2008 2009 2010 $14,512 6,632 7,880 $22,163 10,232 11,931 $32,803 15,223 17,580 Gross profit Selling, general, and administrative Research and development Operating earnings Earnings before tax Net earnings 4,330 2,454 1,096 351 745 6,621 3,534 1,776 617 1,159 394 $765 10,060 4,997 2,523 988 1,535 522 $1,013 Interest expense Income tax $492 Purchases 3,287 $4,966 $7,436 BALANCE SHEET ($000) Assets Cash Accounts receivable Inventories $1,243 2,798 4,225 $8,266 $1,523 4,225 6,523 $12,271 $1,250 6,232 9,587 $17,069 Total current assets Net fixed assets 1,512 358 203 Total assets $9,778 $14,629 $20,272 Liabilities and Net Worth Notes payable to bank Accounts payable $2,987 $6,674 623 $10,769 898 BALANCE SHEET (S5000) Assets Cash Accounts receivable Inventories $1,243 2,798 4,225 $8,266 $1,523 4,225 6,523 $12,271 $1,250 6,232 9,587 $17,069 Total current assets Net fixed assets 1,512 3,203 Total assets $9,778 $14,629 $20,272 Liabilities and Net Worth Notes payable to bank Accounts payable Accrued expenses Current portion of long-term debt $6,674 623 1,086 175 $10,769 898 1,521 175 13,363 $2,987 395 740 175 Total current liabilities 900 Long-term debt 1250 1.074 5,547 4,231 $9,778 9,633 4,996 $14,629 14,263 6,009 $20,272 Total liabilities Net worth Total liabilities and net worth Source: Casewriter Boston Physicians Devices In the spring of 2011, John Gruber, Chief Financial Officer of Boston Physicians Devices, was preparing for a meeting with Rex Dodge, vice-president and loan officer of Great New England Bank. Gruber was looking for a significant increase in Boston Physicians Devices' line of credit above the current $11 million maximum. Gruber examined the past three years of performance at Boston Physicians Devices (see Exhibit 1) and thought about its presentation Boston Physicians Devices (BPD) developed, manufactured and sold scientific medical instruments, needles and catheters which allowed rapid and less invasive access to a number of different organs and vessels. These products represented an alternative to traditional surgical procedures and allowed analysis or corrective treatment with less risk and trauma and at lower cost. An example of the products was catheters that could be introduced into a blood vessel and then manipulated through partially closed arteries or into the heart itself. BPD experienced extraordinary growth, fueled by heavy spending on research and development, necessary to maintain BPD's competitive advantage and rapid expansion of its sales force. Its technical staff was highly regarded for its development of new products with a wide range applications. The combination of state-of-the-art products and a rapidly growing market resulted in plosive sales growth in the last two years, sales had grown at an average rate of 50% Gruber wa confident that BPD sales could reach $45,000,000 in 2011 and $61,000,000 in 2012, given the environment of this rapidly expanding high-tech industry Management had financed BPD's growth by heavily relying on short-term credit. The only outstanding long-term debt had been contracted to finance the manufacturing facility and was being repaid at a rate of $175,000 per year. At the end of 2010, BPD had tapped $10,769,000 of the line of credit with Great New England Bank. This left only $231, 000 available to be drawn. Gruber believed that a significantly higher line of bank credit would be needed to meet BPD's financing needs over the next two years to the investment in net working capital that BPD was expecting to continue, th company was planning the construction of a second production facility adjacent to the current plant. e $2,400,000 facility would be built over the next two years with $1,200,000 being spent in 2011 and In addition another $1,200,000 being spent in 2012. Depreciation charges on the new production facility Boston Physicians Devices 212-070 not be taken until 2013. As for the other additional capital expenditures (other than the S plant), they were anticipated to be equal to the depreciation charges. Before visiting Great New England Bank and meeting Rex Dodge, John Gruber needed to analyze the financial situation of his company in the last three years and to determine how large a line of credit would be required to meet BPD's financing needs during this critical two-year period. Given the risk of its operations, the rate of interest would be approximately 10%, that is, 4% above the lowest rate charged by the bank on short-term loans ibit 1 Boston Physicians Devices: Financial Statements 2008-10 INCOME STATEMENT ($000 Net sales Cost of goods sold 2008 2009 2010 $14,512 6,632 7,880 $22,163 10,232 11,931 $32,803 15,223 17,580 Gross profit Selling, general, and administrative Research and development Operating earnings Earnings before tax Net earnings 4,330 2,454 1,096 351 745 6,621 3,534 1,776 617 1,159 394 $765 10,060 4,997 2,523 988 1,535 522 $1,013 Interest expense Income tax $492 Purchases 3,287 $4,966 $7,436 BALANCE SHEET ($000) Assets Cash Accounts receivable Inventories $1,243 2,798 4,225 $8,266 $1,523 4,225 6,523 $12,271 $1,250 6,232 9,587 $17,069 Total current assets Net fixed assets 1,512 358 203 Total assets $9,778 $14,629 $20,272 Liabilities and Net Worth Notes payable to bank Accounts payable $2,987 $6,674 623 $10,769 898 BALANCE SHEET (S5000) Assets Cash Accounts receivable Inventories $1,243 2,798 4,225 $8,266 $1,523 4,225 6,523 $12,271 $1,250 6,232 9,587 $17,069 Total current assets Net fixed assets 1,512 3,203 Total assets $9,778 $14,629 $20,272 Liabilities and Net Worth Notes payable to bank Accounts payable Accrued expenses Current portion of long-term debt $6,674 623 1,086 175 $10,769 898 1,521 175 13,363 $2,987 395 740 175 Total current liabilities 900 Long-term debt 1250 1.074 5,547 4,231 $9,778 9,633 4,996 $14,629 14,263 6,009 $20,272 Total liabilities Net worth Total liabilities and net worth Source: Casewriter
