calculate the NPV and IRR of the project with the given informstion on the excel chart. There are two follow up questions after the calculations please answer those.
Case Scenario-Berber Bread Yousef Tizir, Jr., CEO of Berber Breads Co. (BBC), Inc. offered his closing comments and then disconnected the conference call. The call had been with Costco Wholesalers' product development team. Costco wanted BBC to supply the chain with its wholegrain tanort or flatbread. Both parties had put their cards on the tableit was time to make a decision Today BBC distributes its products in Texas and its 5 neighboring states. The Costco deal was, however, the proverbial game changer. The firm would need to gear up production, and it did not have long. The unsigned contract dictated a staged rollout of BBC's product; the first shipment of flatbread is to arrive at Costco stores in southern states by January 1, 2023. If the products sell well, then other regions would be added. Unfortunately, if the flatbread sells as predicted the current production line would be unable to meet production needs. To thoroughly evaluate the opportunity, BBC has spent $15,000 researching a new production line and the implications of this deal. The finance department has learned that the physical equipment needed to transform the existing bakery line could be purchased for $1.6 million. It would cost another $250,000 to ship and install the equipment. In addition, BBC's staff will need to undergo an intensive training program. This training will cost $10,000 Thus far in year 2022 (Year 0), BBC has sold 12,000 cases of flatbread each month, at an average selling price of $6.50. If the status quo is maintained slow steady growth the firm expects to grow at an 8% annual rate over the next 4 years. However, sales are expected to change dramatically if BBC agrees to sell through Costco-projections indicate that next year (Year 1) the number of cases sold be double those sold in 2022. While unit sales are predicted to shoot upward, Costco has negotiated a lower price, and this means that the average case selling price (average of Costco and existing case price) would drop to $5.25. Then, as more Costco locations are added to the rollout, total annual revenues are projected to increase 30% (year-over-year) in Years 2 and 3, and 10% in Year 4. Today (year 0) total variable and fixed operating costs consume about 50% of revenues. It is anticipated that, as a percentage of sales, these costs will increase about one percentage point per year over the next four years. But, with the Costco deal, the firm anticipates economies of scale that will reduce total operating costs to about 31% of revenues in Year 1, 33% in Years 2 and 3, and 35% in the last year of the project. The anticipated increase in sales will lead to an increase in working capital requirements. Prior to beginning production of the Costco products, a one-time increase of $100,000 in its inventory (flour, shortening, etc.) will be necessary. This will be partially offset by an increase in accounts payable of S45,000 The existing bakery line was installed 6 years ago and had an estimated economic life of 10 years. At that time, BBC paid $750,000 to buy the machinery. The machine is depreciated using straight-line depreciation. Today, it is believed that the existing equipment can be sold for $400,000 BBC's chief financial officer has predicted that the new equipment has a four-year economic life. It will also be depreciated using straight-line depreciation. At the time of replacement/renovation it is estimated that the firm could sell the new equipment for about 20 percent of its original installed cost to another wholesale bakery. BBC is in the 21% marginal tax rate, and the riskiness of this project warrants that is be assigned a 22% cost of capital (AKA, the required rate of return on the project). The firm has unlimited funds to invest and faces no other constraints in its capital budgeting decisions. Search 10-54 AM Wed May 4 100% Michael's CBP partil POF 219 KB G H 1 Cash Flow Worksheet - Berber Bakery Project 2 Initial Cost of Proposed Project Label in Column A and calculate in Column B. the elements of the initial cost Parch 1.600.000 The initial cost is given below as a check figure. Show your work in columns D-H 4 InstaShoping 250.000 for the figures for any sale of equipment 5 Train 10.000 6 A/increase (45.000) 7 Inventory increase 100.000 8 Old Equipment Sold (400.000) Tax Cepen 21.000 10 11 12 13 14 15 16 17 18 Equals In Cost Check Figures $ 1.536.000 19 20 21 22 Annual Operating Cash Flow (OCFS) You will need to calculate the difference between the figures does not 23 do the project versus if it does the project. Those differences determine incremental cash flow 24 2022 2023 2024 2025 Year 2026 25 0 1 2 3 4 26 Revers 27 without project 938.000 1.010880 1,091,750 1,179.000 1.273,418 volven year multid 20th 1.512.000 1.965,600 2.555200 2.810.000 same done here as above just 29 Change 501,120 873 850 1.376.190 1537,300 ofference of with project fro 30 Opening Costs 31 without 468.000 515 549 567,710 624918 687,546 costs of operation are calcul 2 with pro 468720 64648 $43 242 903.789 gen year is multipled with 33 Change 48.829 (30.338 (216 524) (208, 137Gfference of with projects H 75 000 462.500 (387.00 160.449 33 604 307 500 75,000 462,500 (387,500) 405,412 85, 136 367,500 75.000 462.500 (387 500) 770.365 161.777 387 500 75.000 SLD is calculated here by S7 462.500 The New Equipment degree 387,500) 853,753 "Be sure to 179,288 como calculation to 387,500 matto 514.255 707.775 996066 1.061,965 Les Change in Depreciation 35 without projet 30th 37 Change angin 39 40 41 12 Annual Operating Canh Flow w/ project 83 Terminal Year (Noneperating Cash Flow 45 NWC Recovery 46 Pluri MV of New Art 47 BV of New Asset 48 Gaint Sale of New Asset 49 PlusMinus: Taxon Gainless SO Equals: 51 Terminal Nonoperating Carth How Calculate any adjustments to the OCF for Year as the terminal cash flow 55,000 370.000 66 370.000 177.700) 347,300 52 2022 2023 2024 2025 2026 Year 53 0 1 2 3 4 54 55 Net Cash Flow each year (1.536.000) 514.255 707.775 996.088 1,409.265 56 NPV for Project 57 58 IRR for Project 59 Questions 60 Based on the figures from your solution, would you recommend that BBC upgrade the production line as proposed? Why? 61 62 63 64 65 H Would your answer to Question 1 change if BBC did not expect any savings in operating expenses from the renovations? That is costs are the same percentage of revenues as those that would exist with the existing equipment. Explain, making reference to both NPV and IRR 66 and using specific figures 67 68 69 70 71 72 73 74 75 76 77 78
