N 319 Required Excel Assignment Due: Aug 13, 2019 @ start of class Instructions: Carefully prepare your analysis in the associated Excel template. Turn in Hard & Soft copy. Full instructions are noted on the template. Bioject: Part 1 - Financing Growth William Bones, CFO of BioJect , was preparing for a meeting with his companys bank, set for the following week. At that meeting, Mr. Bones intended to request a five year loan to finance the growth in the business and the expansion of the companys warehouse facilities. The companys recent financial performance is noted in Worksheets 1 & 2. BioJect was a rapidly growing producer and distributer of insulin injection devices. The units were sold throughout the US, but sales were concentrated in the eastern US. Since production was located in Boston, there was usually sufficient inventory on hand to accommodate the large inventory purchases of the local hospitals and medical offices. These customers usually saw their orders filled within 24 hours. For the year ended in December, 2018, BioJect had sales of of about $23.5 million. Net income for that period was $1.19 million. During the previous three years, sales had grown at a compound annual rate of over 20%. This record was a reflection of BioJects excellent service and competitive technological advantage, which resulted in high levels of customer satisfaction. In 2014, BJ has borrowed funds from Central Bank to build a warehouse. The loan was being repaid in equal annual installments of $125,000. At the end of 2018, the balance due on the loan was $875,000. At the same time as the borrowings, a line of credit was established. BJ had yet to borrow any money under this arrangement. National Growth BJ was forecasting expansion of sales in the MidWest and Western parts of the country. To accommodate the forecast growth, it was planning on investing $2.4m over the next 18 months, of which $2m will be spent in 2019 and the remaining amount in 2020. It was anticipated this investment would fulfill the companys need for facilities for several years. Construction of a new warehouse in the MidWest was expected to be completed in early 2020. BJs accountant forecast total depreciation expense of $213,000 and $333,000 for 2019 and 2020, respectively. Management expected that by the end of 2019 that BJ would have to temporarily decrease its inventories to a level of $1.625m until warehouse construction was complete in early 2020. Bioject anticipates that in 2020 inventories will be back to the same proportional relationship to sales that it had in 2018. Operating margins were expected to be consistent with past experience (and the temporary drop in inventory would not affect the cost of goods as a percentage of sales). The company expects a future effective tax rate of 25% due to recent changes in the tax law. Mr. Bones expected BJs dividend payout to remain unchanged at 20% of net income in the foreseeable future. Mr. Bones was forecasting sales growth at 30% in the next year, but falling to 20% the following year. He was concerned what that might mean for the level of bank financing needed and set about to forecast the income statement and balance sheet for the firm over the next two years. Unless otherwise noted, he was going to assume the most recent years operating ratios would prevail in the future. He had a few questions he wanted to answer before going to the bank: How had BJ performed over the last few years? Was the Bank going to be happy with his performance? How much external financing does Bioject need to grow at the forecast rate? Bioject: Part 2 - Valuation Suppose BJ believes it will reach maturity at the end of 2020. Thereafter, growth is expected to average 4%; COGS and SG&A are expected to fall to 55% and 22% respectively, by 2020. With mature growth, CapX and depreciation of $350 is forecast in all years 2021 and beyond. Since growth will be in steady state, no additions to working capital are forecast beyond 2018. How much is BJ worth as a firm given this forecast? They estimate their WACC at 9%. If BioJect has a 2,250 shares of equity outstanding and plans to maintain a future debt level of $850m, what is the value of their equity on a per share basis?

FIN 319 -- Su 2019 Required Excel Project (25 points) Instructions: Prepare an analysis of Bioject to answer the questions posed below. Provide your answers on the relevant worksheets, as noted below. Due Date: August 13, 2019 Beginning of Class; No late assignments accepted. TURN IN: 1. Excel submission to D2L Activity Folder 2. Soft copy in class: Stapled 1 page per question; print each worksheet Name on each page 3. Name your Excel file to include your name. ie 319_Excel_Your NameHere Prepare: Individual or groups of 2. Except those in groups of 2, discussion, texting, sharing, or otherwise communicating with others about the assignment is not permitted. Violations of this requirement will result in a zero grade for the assignment. Other Requirements: 1 Enter formulas and cell references where relevant in the worksheets. Hardcoding answers will result in a reduction of 40% of the assignment grade. 2 Use an input box on each worksheet. See slide details and worked in-class problems for examples. Lack of input area will result in a 20% grade reduction. 3 Stumped on something? Make a reasonable assumption so you can move forward and write a note on the worksheet noting why you were stumped and what you did to move forward. tien Woulcobeat 1 000 Dorformance Wksht 2: Financial Performance Assessment -- BioJet Inc. Question: Assess Bioject's recent financial performance using the information below. Include an assessment of past vs forecast performance. What do you conclude? As a banker, do you have any concerns? Answer completely. Answer in the space below. Make sure to provide a complete depiction of your thinking. Bioject Historical Performance Using data from Worksheet 2, place your estimates here Forecast Forecast 2019 2020 2016 2017 2018 2016 2017 2018 Using data from Worksheet 2, place your estimates here Forecast Forecast 2019 2020 Leverage Assets/equity Debt/Total Capital Interest Coverage B Profitability Return on sales Return on capital Return on equity 4.8% 21.1% 23.9% 4.9% 22.3% 24.5% 5.1% 22.3% 23.7% 2.0 25.6% 12.1 1.9 19.7% 18.2 1.8 14.9% 23.5 Liquidity Current ratio Quick ratio 2.5 2.0 1.3 1.9 1.3 2.0 1.4 Activity Ratios Sales/assets Days recievable Days inventory Days Payable 2.6 56.7 57.2 63.1 40.3 2.6 55.5 56.4 40.6 58.7 38.6 *Bioject's accountants have yet to update the forecasts for 2019 and 2020. This can be done by completing the pro-forma forecasts in Worksheet 2 and updating the forecast ratios for 2019 and 2020. 2021 2019 2020 2021 Input / Assumption Area: Sales growth COGS 2012 20% 2020 20% External Funds Needed: Solve for EFN below and use cell references to place the data at the top of the page. etc. Do you think they can raise the funds to grow at the forecast rate? Do you think they will be able to pay it back? Explain. Place your answer in the blue box to the left. Actual 2018 Forecast: 2019 For years ending 12/31 2020 2021 Notes INCOME STATEMENT Net sales Cost of sales Gross profit SG&A Depreciation Net interest expense Pre-tax operating income Income taxes Net income Dividends Additions to Retained Eamings $23,505 13,612 9,893 7,471 213 94 2,115 925 $1,190 $240 $950 BALANCE SHEET Assets Cash Accounts receivable Inventories Total current assets $ 706 3,652 2,190 6,548 Gross plant & equipment Accumulated depreciation Net plant & equipment Total assets 4,163 1,728 2,435 $8,983 LIABILITIES Current maturities of long-term debt Accounts payable Accrued expenses Total current liabilities Long-term debt $125 1,440 1,653 3,218 750 Common stock Retained eamings Total shareholders' equity Total liabilities 1,135 3,880 5,015 $8,983 Common stock Retained earings Total shareholders' equity Total liabilities 1,135 3,880 5,015 $8,983 External Funds Needed: Wk3: Question 3. How much is BioJect Worth? Note - use the data from your Pro Forma forecast to populate the majority of the inputs needed below. Inputs: Actual 2018 Forecast 2019 2020 2021 Notes For years ending 12/31 Net sales Cost of sales Gross profit SG&A Depreciation Operating Income Income taxes Net operating income Atx plus Depreciation EBIT(1-1) + Depreciation minus Capital Expenditures: minus Working Capital Additions (below) Net Operating Cash Flow to Firm: Actual 2018 Forecast 2019 2020 2021 Notes Forecast Cash Flows: Operating Cash Flows Horizon Value Total Cash Flows: Value of Firm: Debt Outstanding Equity Value # Shares Equity Value per Share Calculation of Additions to Working Capital (CA p - CLop) Actual 2018 2019 2020 2021 Current Assets, Operating: Current Liabilities Operating: Working Capital, net (CAP - CLP) Note - use the data from your Pro Forma forecast to estimate these amounts. FIN 319 -- Su 2019 Required Excel Project (25 points) Instructions: Prepare an analysis of Bioject to answer the questions posed below. Provide your answers on the relevant worksheets, as noted below. Due Date: August 13, 2019 Beginning of Class; No late assignments accepted. TURN IN: 1. Excel submission to D2L Activity Folder 2. Soft copy in class: Stapled 1 page per question; print each worksheet Name on each page 3. Name your Excel file to include your name. ie 319_Excel_Your NameHere Prepare: Individual or groups of 2. Except those in groups of 2, discussion, texting, sharing, or otherwise communicating with others about the assignment is not permitted. Violations of this requirement will result in a zero grade for the assignment. Other Requirements: 1 Enter formulas and cell references where relevant in the worksheets. Hardcoding answers will result in a reduction of 40% of the assignment grade. 2 Use an input box on each worksheet. See slide details and worked in-class problems for examples. Lack of input area will result in a 20% grade reduction. 3 Stumped on something? Make a reasonable assumption so you can move forward and write a note on the worksheet noting why you were stumped and what you did to move forward. tien Woulcobeat 1 000 Dorformance Wksht 2: Financial Performance Assessment -- BioJet Inc. Question: Assess Bioject's recent financial performance using the information below. Include an assessment of past vs forecast performance. What do you conclude? As a banker, do you have any concerns? Answer completely. Answer in the space below. Make sure to provide a complete depiction of your thinking. Bioject Historical Performance Using data from Worksheet 2, place your estimates here Forecast Forecast 2019 2020 2016 2017 2018 2016 2017 2018 Using data from Worksheet 2, place your estimates here Forecast Forecast 2019 2020 Leverage Assets/equity Debt/Total Capital Interest Coverage B Profitability Return on sales Return on capital Return on equity 4.8% 21.1% 23.9% 4.9% 22.3% 24.5% 5.1% 22.3% 23.7% 2.0 25.6% 12.1 1.9 19.7% 18.2 1.8 14.9% 23.5 Liquidity Current ratio Quick ratio 2.5 2.0 1.3 1.9 1.3 2.0 1.4 Activity Ratios Sales/assets Days recievable Days inventory Days Payable 2.6 56.7 57.2 63.1 40.3 2.6 55.5 56.4 40.6 58.7 38.6 *Bioject's accountants have yet to update the forecasts for 2019 and 2020. This can be done by completing the pro-forma forecasts in Worksheet 2 and updating the forecast ratios for 2019 and 2020. 2021 2019 2020 2021 Input / Assumption Area: Sales growth COGS 2012 20% 2020 20% External Funds Needed: Solve for EFN below and use cell references to place the data at the top of the page. etc. Do you think they can raise the funds to grow at the forecast rate? Do you think they will be able to pay it back? Explain. Place your answer in the blue box to the left. Actual 2018 Forecast: 2019 For years ending 12/31 2020 2021 Notes INCOME STATEMENT Net sales Cost of sales Gross profit SG&A Depreciation Net interest expense Pre-tax operating income Income taxes Net income Dividends Additions to Retained Eamings $23,505 13,612 9,893 7,471 213 94 2,115 925 $1,190 $240 $950 BALANCE SHEET Assets Cash Accounts receivable Inventories Total current assets $ 706 3,652 2,190 6,548 Gross plant & equipment Accumulated depreciation Net plant & equipment Total assets 4,163 1,728 2,435 $8,983 LIABILITIES Current maturities of long-term debt Accounts payable Accrued expenses Total current liabilities Long-term debt $125 1,440 1,653 3,218 750 Common stock Retained eamings Total shareholders' equity Total liabilities 1,135 3,880 5,015 $8,983 Common stock Retained earings Total shareholders' equity Total liabilities 1,135 3,880 5,015 $8,983 External Funds Needed: Wk3: Question 3. How much is BioJect Worth? Note - use the data from your Pro Forma forecast to populate the majority of the inputs needed below. Inputs: Actual 2018 Forecast 2019 2020 2021 Notes For years ending 12/31 Net sales Cost of sales Gross profit SG&A Depreciation Operating Income Income taxes Net operating income Atx plus Depreciation EBIT(1-1) + Depreciation minus Capital Expenditures: minus Working Capital Additions (below) Net Operating Cash Flow to Firm: Actual 2018 Forecast 2019 2020 2021 Notes Forecast Cash Flows: Operating Cash Flows Horizon Value Total Cash Flows: Value of Firm: Debt Outstanding Equity Value # Shares Equity Value per Share Calculation of Additions to Working Capital (CA p - CLop) Actual 2018 2019 2020 2021 Current Assets, Operating: Current Liabilities Operating: Working Capital, net (CAP - CLP) Note - use the data from your Pro Forma forecast to estimate these amounts