IM IN EXAM! KINDLY SOLVE THIS LAB FOR ME ON MS EXCEL ASAP PLEASE

Case: Pops and Snacks Pops and Snacks is a local snack manufacturing company in Pakistan, having some of the most famous brands for snacks and drinks. With a production plant based in Sindh, the company not only produces locally, but sometimes also imports from their international plant. The company has a wide portfolio of brands and products, including the products "Bubble Cola", "Crispy Crips" and "Jelly Beans". A list of products is given in Exhibit A. Pops and Snacks has the third highest market share of 19%, while other smaller competitors fall behind. Fizzy, the market leader for snack is Pakistan with the highest market share of 49% has a wider product portfolio and brand awareness than any other competitor in the market. The market shares for the industry are given in Exhibit B. Fizzy's best selling product, Fizzy Cola, is the most popular with customers. Fizzy has the advantage of being the price setter in this industry, and due to close competition and product comparison, other companies have to match their product prices to Fizzy's, to be able to compete. This includes Pops and Snacks Bubble Cola, which is priced at the same rate as Fizzy Cola. Prices for the colas are given in Exhibit C. The market for snacks is growing in Pakistan at a rate of 3% on average annually. This is driven by the population growth and the changing taste preferences of the customers. Growth in the target market of Pops and Snacks (Ages 5-15) can be seen in Exhibit D, with the expected growth for 2020 to be 3%. The demand for these snacks also plays an important part in impacting the prices of these snacks; customers are sensitive to price changes and respond quickly by changing their demand pattern. The production manager for Bubble Cola is now planning the production for the upcoming year. The total demand for Bubble cola is met by either producing everything locally, importing everything or a combination of both. The costs related to all these options vary, and the manager wants to minimize this in order to maintain Bubble Cola as a high profit generating product. The local manufacturing plant for Bule Cola has a production capacity of 2,000,000 bottles a month. The plant is fully automated, so the only variable cost is that incurred for the raw materials. A breakdown for the cost of raw materials is given in Exhibit E. The supervisory staff is paid the same amount each month, regardless of how much is produced at the plant. The salaries cost PKR 20,000,000 and plant maintenance costs are PKR 25,000,000 annually. The second option of importing the product is dependent on the exchange rate between PKR and USD on average for that month as well as the import duties applied on the shipment. The exchange rates for the entire year can be seen in Exhibit F. The percentage of duties applied on the shipment depend on the weight of each shipment and can be seen in Exhibit G. Additionally, each bottle weighs 1kg and a carton of bottles contains 20 bottles. The major demand comes from Lahore, Karachi, Peshawar, Quetta and Multan. Pops and Snacks" exclusive distributors in these cities further take the responsibility of delivering the products to retailers in their own areas. Each distributor has submitted an estimated demand quote to the production manager, asking for a specific number of units as given in Exhibit H. The production manager needs to keep all this information in mind for the decision which need to be made. He thinks making an influence diagram will help map out the problem, which can later be translated into a model. Task 1: (5 Marks) Enter the relevant information on a worksheet using best practices for data entry learnt so far. Label everything properly, keeping in mind that another manager from your team may have to use this information. Exhibit B: MARKET SHARE Fizzy Fuzzy Snacks Pops and Snacks Others 4696 2596 Exhibit C: Cola Prices for Each Brand: Fizzy Fuzzy Snacks Pops and Snacks Other Rs. 35 Rs. 35 Rs. 37 Rs. 30 Exhibit D: Change in Population (Ages 5 to 15) 5.00% 4.50% 4.00% 3.50% 3.00% 2.50% ul 2.00% 1.50% 1.00% 0.50% 0.00% 2016 2017 2018 2019 Change in Population (Ages 5 to 45) Exhibit E: Cost per bottle - Bubble Cola: 10 Sugar Packaging Water Others Exhibit : USD to PKR Exchange Rates 1D 5D 1 M 1 Y 5 Y 5 Max 161 160 159 160,65 Mon, 18 Jan 158 28 Jan Heb Exhibit G: Percentage of Tax/Duties applicable on imported beverages 10,000 KG or less 10,000 - 25,000 KG 25,000 KG or more 5% 10% 13% Exhibit H: Demand Submitted by Major Distributors (units): Lahore Peshawar Karachi Quetta Multan 500,000 450,000 550,000 250,000 300,000 Case: Pops and Snacks Pops and Snacks is a local snack manufacturing company in Pakistan, having some of the most famous brands for snacks and drinks. With a production plant based in Sindh, the company not only produces locally, but sometimes also imports from their international plant. The company has a wide portfolio of brands and products, including the products "Bubble Cola", "Crispy Crips" and "Jelly Beans". A list of products is given in Exhibit A. Pops and Snacks has the third highest market share of 19%, while other smaller competitors fall behind. Fizzy, the market leader for snack is Pakistan with the highest market share of 49% has a wider product portfolio and brand awareness than any other competitor in the market. The market shares for the industry are given in Exhibit B. Fizzy's best selling product, Fizzy Cola, is the most popular with customers. Fizzy has the advantage of being the price setter in this industry, and due to close competition and product comparison, other companies have to match their product prices to Fizzy's, to be able to compete. This includes Pops and Snacks Bubble Cola, which is priced at the same rate as Fizzy Cola. Prices for the colas are given in Exhibit C. The market for snacks is growing in Pakistan at a rate of 3% on average annually. This is driven by the population growth and the changing taste preferences of the customers. Growth in the target market of Pops and Snacks (Ages 5-15) can be seen in Exhibit D, with the expected growth for 2020 to be 3%. The demand for these snacks also plays an important part in impacting the prices of these snacks; customers are sensitive to price changes and respond quickly by changing their demand pattern. The production manager for Bubble Cola is now planning the production for the upcoming year. The total demand for Bubble cola is met by either producing everything locally, importing everything or a combination of both. The costs related to all these options vary, and the manager wants to minimize this in order to maintain Bubble Cola as a high profit generating product. The local manufacturing plant for Bule Cola has a production capacity of 2,000,000 bottles a month. The plant is fully automated, so the only variable cost is that incurred for the raw materials. A breakdown for the cost of raw materials is given in Exhibit E. The supervisory staff is paid the same amount each month, regardless of how much is produced at the plant. The salaries cost PKR 20,000,000 and plant maintenance costs are PKR 25,000,000 annually. The second option of importing the product is dependent on the exchange rate between PKR and USD on average for that month as well as the import duties applied on the shipment. The exchange rates for the entire year can be seen in Exhibit F. The percentage of duties applied on the shipment depend on the weight of each shipment and can be seen in Exhibit G. Additionally, each bottle weighs 1kg and a carton of bottles contains 20 bottles. The major demand comes from Lahore, Karachi, Peshawar, Quetta and Multan. Pops and Snacks" exclusive distributors in these cities further take the responsibility of delivering the products to retailers in their own areas. Each distributor has submitted an estimated demand quote to the production manager, asking for a specific number of units as given in Exhibit H. The production manager needs to keep all this information in mind for the decision which need to be made. He thinks making an influence diagram will help map out the problem, which can later be translated into a model. Task 1: (5 Marks) Enter the relevant information on a worksheet using best practices for data entry learnt so far. Label everything properly, keeping in mind that another manager from your team may have to use this information. Exhibit B: MARKET SHARE Fizzy Fuzzy Snacks Pops and Snacks Others 4696 2596 Exhibit C: Cola Prices for Each Brand: Fizzy Fuzzy Snacks Pops and Snacks Other Rs. 35 Rs. 35 Rs. 37 Rs. 30 Exhibit D: Change in Population (Ages 5 to 15) 5.00% 4.50% 4.00% 3.50% 3.00% 2.50% ul 2.00% 1.50% 1.00% 0.50% 0.00% 2016 2017 2018 2019 Change in Population (Ages 5 to 45) Exhibit E: Cost per bottle - Bubble Cola: 10 Sugar Packaging Water Others Exhibit : USD to PKR Exchange Rates 1D 5D 1 M 1 Y 5 Y 5 Max 161 160 159 160,65 Mon, 18 Jan 158 28 Jan Heb Exhibit G: Percentage of Tax/Duties applicable on imported beverages 10,000 KG or less 10,000 - 25,000 KG 25,000 KG or more 5% 10% 13% Exhibit H: Demand Submitted by Major Distributors (units): Lahore Peshawar Karachi Quetta Multan 500,000 450,000 550,000 250,000 300,000