Find using excel npv and irr, set up a cash flow table for the first 5 years of the project
STORE FRONT CM has established a solid reputation on the California Coast. Lorenzo Merona created a following by participating in wine trade shows and symposiums. He also sent complimentary wine bottles to select restaurants on the California Coast. Alessandro Merona now believes that it is time for CM to claim its spot on the map and build a store front / show room. More and more people like visiting wineries for the experience and the NAPA Valley is known for its wine tours. Alessandro wants to take advantage of this trend. Having a store front and providing wine tasting experiences would attract customers and generate additional sales revenue for CM. CM would have to buy 5 acres of land close to the winery by the main road in order to provide customers with easy access to the store and enough parking to accommodate tour buses. The management team has located the land needed. It is priced at $55,300 an acre and would require clearing, leveling and grading which is estimated to cost $17,400 per acre. CM would build a central store surrounded by tasting rooms, and would create a large parking lot. The construction cost is estimated at $350,000. Interior design and finishes will add another $150,000. While the land cannot be depreciated, CM plans to depreciate the building using 20 year MACRS depreciation. $60,000 will be spent on furniture and fixtures, which will be depreciated using 7 year MACRS. CM will also need to invest in a computer system and winery specific software which will cost $15,800. HW/SW will be depreciated using 5 year MACRS. Initial estimates show that CM will have to spend $8,000 annually to run and maintain the store front/tasting rooms, and this maintenance cost will increase by 10% every year. CM will pay for a design firm to create signage for their store front. The cost of the new signage is estimated to be $18,000. This cost will be expensed upfront. CM would staff the new store front/tasting rooms with a receptionist/cashier ($40,000) and 2 wine specialists ( $55,000 each) who would create tour and wine tasting experiences for customers. Salaries are expected to increase by 3% every year. CM expects to have to hire an additional cashier and an additional wine specialist in year 3 , at the same salary level, but adjusted for annual raises. The plan is to give customers a tour of the vineyard, while providing them with a historical account of the wine production in NAPA valley, including the types of vines being used and the types of wines being produced. The vineyard tour will be followed by a tour of the winery, with an explanation of how wine is made and stored before being bottled. Finally, guests will be led to a tasting room, where they will have the opportunity to taste the wines. The first flight of wine samples will be included in the price of the tour. Additional flights will have to be purchased. CM will secure 2 small buses ( $50,000 each) that will be used to ferry the customers to and from the vineyard. Each bus will be equipped with a quality sound system, which is an add-on option ( $5,600 each). A third bus will be added when CM hires the additional wine specialist in year 3 . The buses will be depreciated according to 5 year MACRS. CM is committed to running this project for at least 5 years, at which point it will evaluate the viability of the store front/tasting room. At the end of 5 years, CM believes that furniture/fixtures and buses could be sold in the market for 20% of their orinal cost. The computer and SW will have no value and will have to be scrapped. Additional working capital of $75,000 is anticipated. CM expects the following sales in its first year of operations: In order to have enough wine to sell at the retail level, CM would forego the equivalent case sales. So in the first year, CM would hold 375 cases of wine or 4,500 bottles to be sold in the Store Front/Tasting room. Every year thereafter, CM expects tours to increase by 15\%, tasting flights by 20%, and wine sales by 15%. Review and analyze each proposed investment, using NPV and IRR. Set up a detailed cash flow table showing total cash flows for each year of the project. Use Excel to run your NPV/IRR and copy/insert your Excel Cash Flow table into your Word document. STORE FRONT CM has established a solid reputation on the California Coast. Lorenzo Merona created a following by participating in wine trade shows and symposiums. He also sent complimentary wine bottles to select restaurants on the California Coast. Alessandro Merona now believes that it is time for CM to claim its spot on the map and build a store front / show room. More and more people like visiting wineries for the experience and the NAPA Valley is known for its wine tours. Alessandro wants to take advantage of this trend. Having a store front and providing wine tasting experiences would attract customers and generate additional sales revenue for CM. CM would have to buy 5 acres of land close to the winery by the main road in order to provide customers with easy access to the store and enough parking to accommodate tour buses. The management team has located the land needed. It is priced at $55,300 an acre and would require clearing, leveling and grading which is estimated to cost $17,400 per acre. CM would build a central store surrounded by tasting rooms, and would create a large parking lot. The construction cost is estimated at $350,000. Interior design and finishes will add another $150,000. While the land cannot be depreciated, CM plans to depreciate the building using 20 year MACRS depreciation. $60,000 will be spent on furniture and fixtures, which will be depreciated using 7 year MACRS. CM will also need to invest in a computer system and winery specific software which will cost $15,800. HW/SW will be depreciated using 5 year MACRS. Initial estimates show that CM will have to spend $8,000 annually to run and maintain the store front/tasting rooms, and this maintenance cost will increase by 10% every year. CM will pay for a design firm to create signage for their store front. The cost of the new signage is estimated to be $18,000. This cost will be expensed upfront. CM would staff the new store front/tasting rooms with a receptionist/cashier ($40,000) and 2 wine specialists ( $55,000 each) who would create tour and wine tasting experiences for customers. Salaries are expected to increase by 3% every year. CM expects to have to hire an additional cashier and an additional wine specialist in year 3 , at the same salary level, but adjusted for annual raises. The plan is to give customers a tour of the vineyard, while providing them with a historical account of the wine production in NAPA valley, including the types of vines being used and the types of wines being produced. The vineyard tour will be followed by a tour of the winery, with an explanation of how wine is made and stored before being bottled. Finally, guests will be led to a tasting room, where they will have the opportunity to taste the wines. The first flight of wine samples will be included in the price of the tour. Additional flights will have to be purchased. CM will secure 2 small buses ( $50,000 each) that will be used to ferry the customers to and from the vineyard. Each bus will be equipped with a quality sound system, which is an add-on option ( $5,600 each). A third bus will be added when CM hires the additional wine specialist in year 3 . The buses will be depreciated according to 5 year MACRS. CM is committed to running this project for at least 5 years, at which point it will evaluate the viability of the store front/tasting room. At the end of 5 years, CM believes that furniture/fixtures and buses could be sold in the market for 20% of their orinal cost. The computer and SW will have no value and will have to be scrapped. Additional working capital of $75,000 is anticipated. CM expects the following sales in its first year of operations: In order to have enough wine to sell at the retail level, CM would forego the equivalent case sales. So in the first year, CM would hold 375 cases of wine or 4,500 bottles to be sold in the Store Front/Tasting room. Every year thereafter, CM expects tours to increase by 15\%, tasting flights by 20%, and wine sales by 15%. Review and analyze each proposed investment, using NPV and IRR. Set up a detailed cash flow table showing total cash flows for each year of the project. Use Excel to run your NPV/IRR and copy/insert your Excel Cash Flow table into your Word document
