Please help me complete the attached assignment. Use the word document to help fill out the excel spreadsheet. I have already started on the first tab, please check to make sure my answers are correct

Chicken Sensations ***Hint: Yellow cells are designed to vary for sensitivity analysis. Blue cells are calculated. Blue cells are calucated and should be linked to other tabs in your spreadsheet. Data Requirement 1: Sales and Marketing Assumptions Numbers Sales Price to Retailers Coupon Costs Commission Explanations Sales Price/Coupon Costs/Commission: Percent/Per Bag Bags/Case Per Case $ 3.00 12 $ 36.00 $ 0.20 $ 2.40 $ 2.80 $ 33.60 Slotting/Package Design/Sales Salaries: Slotting Costs Package Design Costs Sales Salaries First Month Sales Volume Monthly Sales Growth Year 1 December Sales Volume Sales Forecast Error $ $ $ Total Amort/Life Annual 6,000,000 1 $6,000,000.00 2,000,000 1 $2,000,000.00 400,000 $ 400,000.00 Cases Sold 65,000 15,000 230,000 Sales Volume (Cases): Annual Sales $2,340,000.00 $8,280,000.00 25% Data Requirement 2: Production and Cost Assumptions Numbers Explanations Case Configuration: Total Pounds per Case 15 Expected pounds per case Raw Material Ratios/Costs (per lb): Ratios Cost/LB Per Case ($) 20.00% $ 2.0000 $ 0.40000 Expected cost of chicken per pound 65.00% $ 0.5000 $ 0.32500 Expected cost of vegetables per pound 15.00% $ 0.1500 $ 0.02250 Expected cost of spaetzels per pound Chicken Vegetables Spaetzels Weighted Average Raw Material Cost Pounds per Case Total Raw Material Cost per Case Per Unit Bags Box Total Packaging Cost per Case $ $ 0.20 0.30 Total Direct Material Costs per Case 0.74750 Weighted average cost per pound raw materials $ 11.2125 Raw material cost per case (15 pounds per case) Packaging Costs: Qty/Case Per Case ($) 12 $ 2.40 Expected price per bag with 12 bags per case 12 $ 3.60 Expected price per box with 1 box per case $ 6.00 Total costs of bags and boxes per case $ Per Lb Direct Labor Variable Manufacturing Overhead Total Coversion Cost per Case $ $ $ Total Product Cost per Case 0.30 0.40 17.2125 Total of raw material and packaging costs Conversion Costs: Lbs/Case Per Case ($) 15 $ 4.50 Expected costs per pound direct labor 15 $ 6.00 Expected costs per pound variable mfg overhead $ 10.50 Total of direct labor and variable mfg overhead per case $ 27.7125 Total direct material and conversion costs per case Retrofit Costs: Retrofit Costs $ Total Amort/Life Annual 2,000,000 5 $ 400,000.00 Expected retrofit costs to be depreciated over five years Case Question 2: Contribution Margin Format Income Statement Parson Vegetable Company Contribution Margin Income Statement Chicken Sensations Case Question 3: Breakeven a. Contribution Margin per Case b. Contribution Margin Ratio Case Question 4: Margin of Safety a. Margin of Safety in Cases b. Margin of Safety in Dollars c. Margin of Safety Ratio Case Question 5: Sales Price Sensitivity Analysis Sensitivity Analysis Case Question 8: Sales Case Forecast Sensitivity Analysis Sales Case Forecast Accuracy Sensitivity Analysis Margin of error Minimum Per Case Maximum Per Case The chicken or the egg: Hatching a new and innovative product's introduction Brief History of Parson Foods Parson Foods Company was established in 1925 when Samuel E. Parson, Sr. purchased the Pecayune Marketing Company which ran an evaporated milk processing company in Pecayune, Illinois. Parson Foods first entered into the fluid milk industry in the mid-1930's and continued to primarily operate in the fluid milk industry in northern Illinois until 1947, when it entered into the ice cream business. The company followed a strategy of growth through careful acquisition of well-established, family-run, Midwest-based fluid milk processors. In the 1960's, Parson Foods made its initial expansion beyond its dairy roots by purchasing the Sturgeon Bay Food Company, a pickle processor. Soon afterwards, Parson Foods established its specialty products division. Through the remainder of the decade, Parson continued to acquire wellknown regional dairies infusing them with capital for facility upgrades and providing purchasing, marketing, and management expertise while allowing the acquired companies to continue to operate semi-autonomously. Parson made its reputation in the low-margin dairy business preferring to retain well-known local brands rather than consolidating into a single nationwide brand. Parson's fastest growing segment, with respect to profitability, was its specialty products division which expanded to process dips, sauces, relishes and processed salad mixes. In 1986, Parson continued to expand outside of the fluid milk business by merging with Midwest-based Darien Company, a market leader in canned and frozen vegetables. Darien's successful brands included Veg-Ums and Fresh Always with annual sales of $170 million. The merger signaled a shift in Parson's corporate strategy that now began to target companies with larger sales volumes in diverse markets. Management contended that vegetables were counter-cyclical to dairy driven primarily by weather conditions. The intent of the acquisition was to improve 1 consistency of annual operating results of the company. By 1987, Parson ranked third in frozen and canned vegetable sales in the United States. Profits in the dairy industry were more predictable thanks to government initiatives to stabilize milk prices by buying surpluses. However, profits and prices in the processed vegetable industry were considerably more volatile due to yield fluctuations driven by weather conditions. In years of mild weather, vegetable yield surpluses increased supply and negatively impacted profitability. In years of severe weather, shortages of vegetables decreased supply and positively impacted profitability. Similar to its dairy strategy, Parson acquired several regional vegetable companies throughout the country from the late 1980's to the mid 1990's. Parson's largest acquisition was Eagle Eye frozen foods from Dinner Foods which increased Parson Foods' annual vegetable sales to nearly $600 million making it the largest vegetable processor in the United States. Consistent with the dairy strategy, the acquired vegetable companies operate autonomously which created operational challenges for the individual vegetable companies. Unlike dairies with limited shelf life and distribution, the extended shelf life of vegetable products allowed vegetable companies to distribute products nationwide irrespective of the location of the processing facility. The result, in some cases, was that different vegetable companies under the Parson Food umbrella competed for the same business. This cannibalistic competition adversely impacted vegetable prices and company profitability. To address the issue, Parson Company executives elected to depart from the long held autonomous dairy strategy, deciding to centralize and establish Parson Foods Vegetable Company (PFVC) in Sturgeon Bay, Wisconsin. Richard Larson was named Chief Executive with the express purpose of consolidating and coordinating vegetable sales, production, operations and logistics throughout the country. Vegetable product profitability was lagging significantly behind expectations, and the changes were expected to improve profitability. 2 The Opportunity Richard Larson was excited about the challenge of improving PFVC's profitability. He just completed the difficult task reorganizing and consolidating PFVC's office in Sturgeon Bay with several long-term, highly-valued employees from the individually acquired vegetable companies no longer being severed. His assembled team in Sturgeon Bay was ready to tackle the task at hand. Several months ago, Carlos Rico, a marketing manager approached him with an innovative new convenience food product idea combining frozen vegetables, spaetzels (a coated seasoned pasta), and chicken in the same bag. It was designed to compete against other convenience foods such a frozen pizza. Based on preliminary analysis, the new product represented a potential homerun for PFVC with company sales expected to increase by 20 percent and predicted gross margins nearly double the current vegetable offerings. While the potential for this new product was palpable, Richard was a realist given PFVC's history of new product introductions. The latest was Soup-in-a-Flasha microwaveable soup starter kit introduced by PFVC's predecessor Anchor Vegetables that failed miserably. It ended with the write-off of $10 million of unsold finished goods, packaging and manufacturing lines during the company reorganization. This represented half of the company's profits from the prior year and served as a painful reminder to Richard of the team's challenge. As a result, Parson Company executives were cautious and not enthusiastic about investing in another PFVC new product even though the management team associated with Soup-in-a-Flash was no longer with the company. Richard mused, \"If this new product fails, my tenure as the CEO of the largest vegetable company in the United States may be short-lived.\" Failure was not an option. The Team 3 Richard decided to assemble a cross functional team of PFVC's best sales, production and financial professionals to pursue this apparent golden opportunity of a new, healthy frozen convenience meal. Richard addressed the team, \"Congratulations and welcome. You have been selected to participate in potentially the most important new product initiative in PFVC's history. As you might recall, our last new product initiative Soup-in-a-Flash was not a rousing success. The new product you will be working on has the potential to create a whole new category of higher-margin convenience foods for PFVC. Initial projections look promising. This is Carlos' idea, so I will let him explain.\" As Richard gestured, Carlos stood and moved to the front of the conference room. \"Thanks, Richard. Good afternoon. As many of you aware, we currently produce Pasta Done, a microwaveable product consisting of vegetables and spaetzels. But this innovative new product idea is going to take that concept one step further and is unlike anything on the market. We are going to add proteinin this case cooked chickento the vegetables and spaetzels in the same bag. In the past, similar products separated chicken from vegetables using multiple pouches. Combining all ingredients in a single bag simplifies the process for consumers offering them improved, value-added convenience. It takes only six minutes to cook in the microwave, and is a nutritious alternative to traditional convenience foods such as frozen dinners or pizza. I have been calling it Chicken Sensations. Some of the big hurdles with this new innovation: USDA approval for both our processing facility and the insertion of chicken combined with other ingredients into one bag are significant challenges to getting Chicken Sensations to market. The last time we sought USDA approval was more than 30 years ago when we began processing canned meat for the government. Since then, everyone associated with that process retired or was not retained in the reorganization. Therefore, we have no in-house institutional knowledge 4 of the USDA approval process. But enough about the operational challenges of the product, that delicious aroma you smell from the test kitchen is a sample of the product that our research and development team prepared for us. Let's eat!\" With that, staff emerged from the test kitchen and served the team sample meals of Chicken Sensations. While the team was enjoying their meal, Richard continued, \"Thanks Carlos. I think this could be a winner, but we need to make certain everything's right since we will be creating a new category of convenience foods. Execution is key to turn this potential idea into a successful reality; everyone on this team must execute. If we elect to pursue Chicken Sensations, we need to insure we are price competitive with current convenience food offerings, have the ability to produce a high quality product at a reasonable cost, and most importantly make a profit. Before our next meeting, I would like to address the feasibility of us pursuing Chicken Sensations. Carlos, I would like for you to evaluate the price per ounce of alternative convenience foods as well as configurations varying the mix of chicken, vegetables, and spaetzels. In addition, provide a sales forecast with recommended pricing.\" Addressing Gary Smits, the production manager, Richard said, \"Gary would you please provide the expected packaging, labor, and variable overhead costs, any additional capital investments, anticipated costs to get USDA approval and inventory needed to produce and support Chicken Sensations.\" Finally, Richard directed his comments towards, Vicki Hoerning, the sole financial analyst on the team saying, \"Vicki would you please take the inputs from Carlos, and Gary to determine the financial feasibility of Chicken Sensations. Include sales forecasts, contribution margin income statements, breakeven analysis, and margin of safety. With our failure with Soup-in-a- Flash our goal is to breakeven in the first year.\" 5 Vicki said, \"Richard you can count on me. I think I speak for the entire team; we are excited to be included on this team and will do everything possible not to repeat the Soup-in-aFlash debacle. I will get everything put together as soon as possible. Carlos and Gary, let's get together when you have your information ready.\" Addressing the entire team, Richard reminded, \"I do not think I need to remind you the importance of this project. Because of our failure with Soup-in-a-Flash and limited financial resources, Parson corporate is increasingly reluctant to authorize the pursuit of any new products. We need to insure our analysis is rock solid. Our goal for Chicken Sensations is to breakeven in less than one year.\" The Meeting \"Good afternoon gentlemen,\" Vicki began addressing Carlos and Gary. \"Thanks for meeting with me so quickly I know you have very busy schedules. My goal for the meeting today is to gather information necessary to generate Richard's financial analysis. When we are finished, I anticipate providing an in-depth evaluation of the feasibility of Chicken Sensations. Carlos, what did you find out about anticipated pricing and size of Chicken Sensations relative to other convenience foods?\" \"Well Vicki, based on my market analysis the retail price per ounce of other convenience foods ranges from 16 to 20 cents with package sizes ranging from 20 to 30 ounces. As a result, individual item prices range from $3.20 to $6.00 at retail. Based on my analysis and the greater perceived nutritional value of the Chicken Sensations relative to other convenience products, I suggest we target a retail sales price $3.75 per 20-ounce bag. With retailers requiring a minimum 20 percent gross margin for new products, we need to sell Chicken Sensations for around $3.00 6 per bag or $36.00 per case of 12 20-ounce bags.\" Vicki recorded this information on sales prices and revenue projections. Carlos continued to explain some of the sales expenses and other costs, \"To gain consumer trial in the first year only, we will have to offer coupons of $0.20 per bag or $2.40 per case for all cases sold. To gain access to convenience food distribution channels, we will have to pay 6 percent commission on the sales price for the life of the Chicken Sensations. In addition, retailers (in total) require a one-time slotting allowance of $6,000,000 to purchase shelf space for Chicken Sensations.\" Vicki noted that the slotting allowance costs would be expensed in the first years. Carlos continued to explain estimates for other Chicken Sensations costs, \"Package design costs of $2,000,000 will be paid and expensed in the first year. To support Chicken Sensations, three additional sales people will need to be hired with annual salaries totaling $400,000.\" \"Carlos, that sounds terrific. The last thing I need from you is forecasted case sales.\" Carlos continued, \"Sure Vicki. I anticipate first month sales to be 65,000 cases in January and increase by 15,000 cases each month for the first year to a maximum of 230,000 cases per month by December with a potential forecast error of 25 percent. For the subsequent years 2 through 8, I forecast sales to be 2,760,000 cases per year.\" \"Wow those numbers are much higher than I would have expected,\" Vicki exclaimed. \"One more thing Carlos, how much do you think accounts receivables will change to support the new product?\" \"Well, Vicki, based on our credit terms of net 30 days, I expect the accounts receivables required to support Chicken Sensations to be 100% of the prior month's sales 7 dollars.\" \"Thanks Carlos. Please review my summary of our discussion to insure I accurately captured your sales and marketing assumptions. I do not want to misinterpret you.\" Data Requirement 1: Provide a summary of Carlos' sales and marketing assumptions. Bags per Case Sales Price/Coupon & Commission Costs: Sales Price to Retailers Coupon Costs Commission Costs Percent/Per Bag Bags per Case Slotting/Package Design/Sales Salaries: Slotting Costs Package Design Costs Sales Salaries Sales Volume (Cases): Total Cases Sold Amort/Life Per Case Annual Annual Sales First Month Sales Volume Monthly Sales Growth Year 1 Total Sales Year 1 December Sales Volume Months per Year Annual Sales (Years 2 to 8) Forecast Error Percent \"Everything looks to be in order Vicki,\" Carlos replied after reviewing Vicki's summary. \"Great. Before we get to the production and cost assumptions, Gary do you have any questions for Carlos?\" \"Thanks for asking, I do have a few questions. Based on your comments, it looks like our case configuration would be 12 bags of 20 ounces per bag or a 15 pound case. Is that correct?\" \"Yes, we feel a 20 ounce bag allows for us to be competitive with the price points per ounce of other convenience food products.\" \"That works great. We anticipate producing 8 Chicken Sensations at the Oakdale facility where we already make spaetzels and have the capability and capacity to package 15 pound cases. Are you comfortable with your sales forecast? As I recall, your sales forecast for Soup-in-a-Flash was a bit overly optimistic causing us to over produce a product that never sold, occupied valuable warehouse space, and ultimately was scrapped.\" \"Gary, I am not certain if you are aware that prior to coming to PFVC, I worked for a frozen pizza company where we introduced several new products. The forecast I provided is consistent with first year sales volumes of those new products.\" \"Awesome, one more question. What are consumers' expectations relative to the amount of chicken to be included?\" \"For frozen pizza, consumers expected a minimum of 10 percent and a maximum of 20 percent of protein relative to other ingredients. Anything less than 10 percent, consumers feel cheated. Anything more than 20 percent, consumers are unwilling to pay the increased price for the product. Since we want consumers to view Chicken Sensations favorably with other convenience food products, we should have a ratio of 20 percent chicken, 65 percent vegetables, and 15 percent spaetzels.\" \"That works great. If anything, later we might want to consider decreasing the ratio of chicken and increasing the ratio of vegetables or spaetzels. Since chicken costs based on current market conditions are estimated to be $2.00 per pound substituting vegetables at $0.50 per pound or spaetzels at $0.15 per pound would reduce cost and increase profits.\" \"Based on the case configuration of twelve 20 ounce bags, packaging cost will be $0.20 per bag for the Ziplock bags. Also, the case container or box will cost $0.30 per box. Direct labor and variable manufacturing overhead costs per pound are $0.30 and $0.40 respectively at the Oakdale facility. To retrofit the Oakdale facility for USDA compliance, we will have to spend an additional $2,000,000 that we will depreciate over 5 years with no salvage value.\" 9 \"OK Gary,\" Vicki asked, \"based on Carlos' sales forecast and our production and storage capabilities and capacities, how much finished goods inventory do you think we will need to support sales?\" \"Well, Vicki, one of the issues here is that Chicken Sensations is unlike any of our standard vegetable offerings. Remember one of the issues that caused Soup-in-a-Flash to fail was inaccurate forecasts. Until we have better information, we should use 50 percent of the next months' forecast sales cases to estimate needed finished goods to support future sales.\" Data Requirement 2: Provide a summary of Gary's production assumptions. Case Configuration: Pounds per Case Bags per Case Raw Materials Ratios/Costs (per lb): Chicken Vegetables Spaetzels Weighted Average Raw Material Cost Total Ratios Cost/Lb Per Case ($) Per Unit Qty/Case Per Case ($) Per Lb Lbs/Case Per Case ($) Total Amort/Lif e Annual Total Raw Material per Case Packaging Costs: Bags Box Total Packaging Cost per Case Total Direct Material Cost Per Case Conversion Costs: Direct Labor Variable Manufacturing Overhead Total Conversion Cost per Case Total Product Cost per Case Retrofit Costs: Retrofit Costs 10 \"Gary, please verify I captured everything correctly.\" \"It looks like you got it correct.\" \"Awesome, I think I have everything I need from you guys. Thank you. I should be able assemble the preliminary financial analysis in the next few days. When we meet with Richard, if I were you, I would be prepared to discuss the sensitivities to your numbers as well as best and worst case scenarios. That would help us get a handle on risks and expectations. When we are done, I suspect Richard is going to request funds from Parson Corporate to proceed with Chicken Sensations. Be ready, he does not want to let this one get away. I am excited about the potential for Chicken Sensations. It's nutritious and tastes good too. Thanks again. See you soon.\" **Responses to all case questions to be completed in Word. Excel should be used for all calculations. Calculations from Excel should be copied to the Word document. Please turn in a hardcopy of the Word document and an electronic copy of the Excel files. Discussion Questions: 1. Case Issue: What is the basic issue needing resolution in the case? (Create an Excel spreadsheet to answer questions 2 thru 9). 2. Forecasted Contribution Margin Income Statement: Based on the sales and marketing (data requirement 1) and production and cost (data requirement 2) assumptions, generate a projected contribution margin income statement for the first year of operation for Chicken Sensations. 3. Breakeven: Based on your contribution margin income statement, compute: a. breakeven in cases and dollars b. contribution margin per case c. contribution margin ratio 4. Margin of Safety: Calculate the margin of safety for Chicken Sensations in: a. cases b. sales dollars c. the margin-of-safety ratio 5. Sales Price Sensitivity Analysis: Assuming the sales forecast and all costs are correct, if retailers are willing to only pay $2.90 per bag (ten percent less) or $34.80 per case for Chicken Sensations with no other changes in fixed and variable cost, what is the impact on 11 breakeven sales cases and dollars? Based on your calculations, is it feasible for Chicken Sensations to breakeven in the first year. 6. Variable Cost Sensitivity Analysis: During the meeting with Richard, Gary indicates the market conditions for chicken have changed, and the cost of chicken has increased to $2.25 per pound. Assuming the selling price remains constant at $36.00 per case and no other changes in fixed and variable costs, what is the impact on Gary's revelation for Chicken Sensations on breakeven sales cases and dollars? Based on your calculations, is it feasible for Chicken Sensations to breakeven in the first year? 7. Worst Case Sensitivity Analysis: Richard asks you to prepare a worst case scenario analysis where retailers are willing to pay only $34.80 per case and chicken prices are at $2.25 per pound. What is the impact for Chicken Sensations on breakeven sales cases and dollar? Based on your calculations, is it feasible for Chicken Sensations to breakeven in the first year? 8. Sales Case Forecast Accuracy Sensitivity Analysis: Assuming a sales forecast error of 25 percent with a selling price of $36.00 per case and chicken costs of $2.00 per pound, what are the range of potential gross profit dollars and percent for Chicken Sensations in the first year? Based on this analysis is it feasible for Chicken Sensations to breakeven in the first year? 9. Recommendation and Analysis: Based on your analysis on company costs and retailers expected profitability for new products, do you think Chicken Sensations is a good investment for PFVC? Why or why not? What impact did your sensitivity analysis have on your decision? What qualitative factors did you consider when making your recommendation? 12