Please generate Cash flow and statement and projected income statement in excel.

thought of bringing together the growing acceptance of infant milk formula with increasing number of malnourished children in India. The proposed product is intended to meet the nutrition requirements of infants ( 012 months) in addition te the mother's milk. Dr. Krishnan discussed the idea of this infant milk formulo with Mirthy and his team. Mur thy liked the idea and wanted that a market research should be conducted before the coempny invests in this new product especially when the compuny hor never move? beyond tim twsting product line of processed baby food. Company Background Started as a small business in 2004 by Dt. Gi VKrishnas. (De. Krishnan), as nenatiative to serve the feeding needs of a baby with maximum nstura! benefis, Neeptytctndia Pvt. Ltd. is a leading FBO (Food \& Beverage O-gacization) in Bengaluru, Intio Sy August 2018. It has secured a considerable share in the bah, food market by offerin glyackaged organic baby food for toddlers (1-3 years). The organizatios was registered under Cimplanies Act, 1956 as a Private L.imited company from 2000 on wards and insocporates bh manufacturing and retailing of the said product. Dr. Krisinas is a voluitarily retired pediswisan from a well-known private hospital in Bengaluru. During the jear of his practicet.s. Krishnan learnt that irrespective of their economic staru peopte in ltidia embraced t'e fivby food. Thus the level of adoption of commercial baby fuod has considersbly increareretweindia and would continue. One of the major reasons behind is quick adoption is the powenience offered by the product to the busy parents who have time consctuints. India being one of tiae highest ranking hnies in the world for children suffering from malnutriticn, the growth ot the Indten Wob food market has hargcly been supplemented by the incteasing awartness for nutrition. Thene is a significant portion of the population categorized as Lover Inconit Gooup (LIG) red Medium Income Group (MIG) who have now started adopting the processed bab, jedefser meeting the nutrition requirements of their children. The other facton adding to the iriegfsing demand for the processed baby food are - rise in organized retail marketing, urbanizesons and a sizeable increase in the count of working women popularion. The major market legdersin India for offering processed baby (infant and toddler) food are Nestle (Cerelac). Aebspri (Uurrition (Go \& Grow by Similac). Danone (Nurricia Farex), Pristine Organics (1st Etisa, British Life Sciences (Baby Food LF 100), Mead Johnson (Enfagrow Premium) and Ntwrimed (Medolac). Together, these companies account for 50.52% of the total retzil ste of baby food and formula market, Nestle India Pvt. Led. being the major player with 63 ) wetue share. According to a study, the Indian buby food is expected to grow at a constuns 2017 value CAGR of 8% during the forecast period to reach INR69.8 billion in 2022 Tinis growth of baby food marker in India will be supported by the increasing number of 2.1 Caselet working women, growing nutrition concerns for the development of babies among young mothers and the convenience of baby food available throughout the country. 2 Dr. Krishnan believed that the product offered by these majcr companies commanded higher prices and would be beyond the economic capacity of the LIG fimilies. These families are also termed as the urban poor and children belonging to these familics are at a risk of under-ruftstion. Thus Dr. Krishnan thought of developing and offering a bahy i3od for toudlers at a prece tower than the prices charged by the market leaders, with the aim to serve the underscenstrieeds of families from LIG to MIG and in turn capture this target matket segmentel Nepphyte is a privately labeled company offering prepared and packaged otanic baby nufiestn food that is available in local flavors such as apple, honcy, barana and orange. One of ine rajor benefits of starting a private label is the fact of being positioned as a lower cost alteveatyy to major national and international brand. Neophyte's products were sold ordy via small to medium scale cal-mist stores, private nursing homes and local provisional siores in Bengaluru. This was reariy due to the fact that the company could not bear the preferted margin by large scale pharmacists, super markets and large private hospitals. The owners of these independen feetale stores and provisional stores were given incentives to ereare avyareness and promoteshefroduct to the local residents personally. Thus Ncophyte is now a medium scale private labed company with annual sales turnover of INR 3450 million fon the financia! year 2017-18 :6 cottpared to INR43 million for the financial year 200405. Four years post the establishment of Neophyte, the Indian economy experienced the effects of the global ccononiy ciisis. The after-effected increased inflation rate and reduced purchasing power parity of the Indian consumei.jeey de beneficial for the low-priced baby food market as consumers became budget conscioussed started moving to private labels or local brands from the incernational ones. Hence siseales increased rapidly by 42.9% from the financial year 2008-09 to 2009-2010. The vittaises of Nestle's infant formula being contaminated and infested with worms cied in April tetio', had further contributed towards switching consumers' preferences from internasoul brands to local products made up of cow's milk and whey proteins. This had further sho- tip the sales by 87.4% increase from 2009-10 to 2017-18 (Exhibit I). Seprintert 2017 (Accessed Date: Aughast 25^ 2018) 12.ten National Ruper TCrecern over Fake Foods crop up", http:l/www newindianexpess.con/citic/dhennilartide193s 48 ece. April 13* b)10 (Accesed Date: February 202017 ) The Proposal De. Krishnat strongiy believed thai ase equate demand for any new product and the availability of ampic amount offinancial restiree are the keys behind the success of any proposed investment plan. Readuig the executive anemery of the proposal submitted by Murthy, Dr. Krishnan was reminded oi his conviction Weth regard to the financial resources, Dr. Krishnan felt that there could not be a better titiking to consider such an investment. On account of increasing sales trend and high profisvily levels over the past few years, Neophyte had saved sizable amount of funds. In thepsagsal, Murthy estimated that 80% of such a required capital could be funded by equity and the remaining 20% could be debt-financed, for which the company was required to afpproach the banker. Convinced by the stable cash flows and strong financial performen fect Neophyte, company's banker had extended to offer a five-year loan at a subsidized interss ratsof 16% for the launch of the low-priced infant formula. With the 20:80debt to equishtianacing structure, Murthy escimated the weighred average cost of capital for this project ancured 21%. adoption of processed baby food for both infants and toddlers, however these were lazecly consumed by the Upper Income Group (UIG) of the populstion. Majority of the LGend MIG families still belicved in working out some honic-made solutions for meeting the nutritional requirements for their children. It was not ciear wi-ther stch s trend was becene of the higher prices commanded by the major players or se to lack of awareness abour ve benefits of processed food. If the reason was former, it might prove ro be the best timine teg Neophyte to launch the low-priced infant formula. A consultant was hired by Murthy to conduct a narket survey for estimasirg the demand for the product. Based on the survey conducted, the consutrant presenteliguigcilowing estimares: - Neophyte could sell a total of 20,000 unurs of this low-inced infant milk formula during a month. - Projected selling pricc of INR:s? per unit. The survey also concluded that sudi a sale waz expected se sur Mivue for a span of 5 years at the same sales price. The market sarvey conciurted for the peogect resulted in a cost of INR2 million to the company which was agred to be padd at the suhtssion of the survey report (Consultant took 2 months to complete the surtey). The company was ruiaing at irs full capacio. lroducing the new range of product required Neophyte tc add new semi-auzoinated machinery to its production unit. The estimated cost of buying such machincry including the irafelituon charges was expected to be INR25 million. The plant cou'd le fully depreciatedone s pan of five years using straight line method. Murthy believed that the decisien to buy huk bsachinery will help the company to reduce its overall Libor cost, thersby chabling Neopiyt? to be in a more competitive position by adopting a lowprice stratezy. The machinceg had s capacity to produce 20,000 units every month. In case the plant is to shut down at the cto of five years or the existing machinery is to be replaced, this machinery could be sold at a value of INR2 million with an imposition of 30% tax on the resale value. The new machinery Preulise stationed in an empty unutilized annex next to the main production unit of Neophyte Ihe annex was not in use ever since the construction of the new warehouse for storing the finished products, even though, Dr. Krishnan recently received a request form one of tis aid itiends to lease out the annex for INR3.5 Lakh. Muriiz detemined that in order to maintain enough liquidity and to ensure smooth functioning of the est unit, Neophyte would require additional working capital. In his proposal, he suggested to koy the inventory level equal to a minimum of one month production. He proposed to adwt a liberal collection policy of 30 days as against the normal collection policy of 24 days. 6 wwicetcases.com Caselet 15 He believed that having a relaxed credit policy will not only attract new wholesale clients but will also encourage the existing Grocery stores to offer the product in their stores. As far as the payables were concerned, he would like to stay with the existiaz payment policy of 20 das The estimation of various production and other overhead cosis were slso outlined in the propusai. Unit Raw material cost required to produce the infant fermuia was ocimated to be INR\$6ger unit. The cost of labor and energy consumption would be sound INK2 5 . akh and Diz?8,,000 per month respectively. Murthy suggested utilizing conpany's existing sales forceas-ichannel of distribution and thus it was expected that there would onty be a moderate iecrement in the level of administrative and selling expenses estinvated aroum! IIvR4.5 Lakladyer. However, labelling and packaging would cost around INRI million per annum. A,0.\% licy, 8% of the sales were generally charged as overhead expense by the ancountant, fotwry(giw project in hand. After reading the entire proposal, Dr Krishnat was cor:templatine wbut the new proposal. Erosion of the sales of the existing prodict by. Wec infant milk forstila was a major apprehension for him. The market survey sugressici that Wee infant milk form tie could cannibalize Neophyte's after-tax cash flows (tax @30\%) from the existing product lene by INR3 million per year. Basis the information provided by Murthy in the proposal, cos? Dr. Krishnan add value to his company by undertaking tia given project