I have to provide a case summary on receivable managements and I need assistance with plugging in the correct amounts to the attached spreadsheet please. I have also attached the case which provides information that needs to be plugged into the spreadsheet.

Foster Pharmaceuticals receivables management Kathleen Grogan received her PhD in pharmacology ten years ago from Boston University. While there, she became very inter- ested in the business side of drug distribution and hence stayed on for an extra 18 months to earn an MBA. After graduation, she went to work for Capo Corporation, a major drug manufacturer, where she managed the development of a new nonprescription antiallergy drug. Although the drug passed all Food and Drug Administration (FDA) trials and was certified for general use, Capo simultaneously developed a similar drug that was cheaper to produce and equally effective in treating most, but not all, allergy symptoms. Thus, Capo decided not to proceed with production of the drug that Kathleen helped develop. However, Capo was willing to license production and distribution rights to another company. Kathleen thought that this might be a golden opportunity, so she quit her job with Capo to found her own company, Foster Pharma- ceuticals. The sole purpose of the new company is to obtain the license for, produce, and distribute the new drug, which Kathleen dubbed \"SneezeRelief.\" Kathleen is currently working on the business plan that she will present at a venture capital conference to be held in New York. The main purpose of the conference is to match entrepreneurs with venture capitalists who are interested in providing capital to fledgling firms. Kathleen has spent a lot of time thinking about how her proposed company's receivables should be managed; she is concerned about this issue because she knows of several small drug manufacturers that have gotten into serious financial difficulty because of poor receivables management. Initially, Foster Pharmaceuticals would sell directly and exclusively to four retail customers in the Northeast (Exhibit 28.1 provides the sales mix). If demand proved solid, the company would expand into other areas and wholesale channels. Sales are expected to be highly seasonal: Allergy drug sales are slow during the winter months, but they pick up dramatically in the spring when plant pollen levels reach a peak. Busi- ness falls off again in the summer, but it picks up in the fall when the ragweed season begins. Kathleen's sales forecasts for the first six months of operations are given in Exhibit 28.2. Assuming the fledgling company receives financing and begins operations, Kathleen's sales forecasts for the first six months of the second year are provided in Exhibit 28.3. Kathleen does not plan to give discounts for early payment; discounts are not widely used in the industry. Based on preliminary discussions with the retail outlets (her customers) Kathleen forecasts the payment schedule, shown in Exhibit 28.4. She does not foresee any problems with bad-debt losses; the retailers she plans to sell to have been in business for a long time. Furthermore, she plans to carefully screen her customers, and she believes that these two factors will eliminate such losses. On average, Kathleen believes that 20 percent of receivables will contribute to profits, so 80 percent of receivables represent cash costs. Furthermore, the First National Bank of New England has indicated that its receivables financing would cost 8 percent annually. In spite of her optimism regarding bad-debt losses, Kathleen is con- cerned about the company's potential level of receivables, and she wants to have a monitoring system in place that will allow her to quickly spot any adverse trends that develop. Kathleen's total sales forecast for the first full year of operations is 800,000 packages. Each package, which will contain 12 tablets, will be priced at $5. Kathleen has hired you as an outside consultant to advise her about receivables management. So far, you have developed a model that produces accounts receivable balances, average collection period (ACP), aging schedules, uncollected balances schedules, and quarterly carry- ing costs for the end of March and the end of June. The uncollected balances schedule permits managers to remove the effects of seasonal and/or cyclical sales variation and to construct an accurate measure of receivables payment patterns. Thus, it provides financial managers with better aggregate information than do such crude measures as the ACP or aging schedule. CASE27 CASE 28 12/12/2017 Student Version FOSTER PHARMACEUTICALS Receivables Management This case focuses on the usefulness of the average collection period (ACP), the aging schedule, and the uncollected balances schedule in monitoring a business's receivables. The model uses sales forecasts and collections expectations to calculate ACP and to generate both aging schedules and uncollected balances schedules. In addition, the model calculates the cost of carrying receivables. The model consists of a complete base case analysisno changes need to be made to the existing MODEL-GENERATED DATA section. However, all values in the student version INPUT DATA section have been replaced with zeros. Thus, students must determine the appropriate input values and enter them into the model. These cells are colored red. When this is done, any error cells will be corrected and the base case solution will appear. Note that the model does not contain any risk analyses, so students will have to create their own if required by the case. Furthermore, students must create their own graphics (charts) as needed to present their results. INPUT DATA: Sales Forecast: Month January February March April May June ales Mix Forecast: Payer Large Retail Chain 1 Large Retail Chain 2 Regional Drug Store Small Grocery Chain KEY OUTPUT: Accounts Receivables Balance: Gross Sales $0 0 0 0 0 0 Average Collection Period (Days): Aging Schedules: 0 - 30 days 31 - 60 days 61 - 90 days Uncollected Balances Schedule: January (April) remaining rec / sales February (May) remaining rec / sales March (June) remaining rec / sales Quarter remaining rec / sales % of Sales 0% 0% 0% 0% Quarterly Carrying Costs of Receivables: Assumed Collection Pattern: Payer 0-30 days 31-60 days 61-90 days Large Retail Chain 1 0% 0% 0% Large Retail Chain 2 0% 0% 0% Regional Drug Store 0% 0% 0% Page 1 CASE27 Small Grocery Chain 0% Other Inputs: Periodic (quarterly) interest rate Contribution margin 0% 0% 0.0% 0.0% MODEL-GENERATED DATA: Average Collection Period: Remaining Uncollected Balance at Period End Payer 30 days 60 days 90 days Large Retail Chain 1 100% 100% 100% Large Retail Chain 2 100% 100% 100% Regional Drug Store 100% 100% 100% Small Grocery Chain 100% 100% 100% End of March: Accounts receivables balance Average daily sales Average collection period (days) $0 $0 #DIV/0! Page 2 CASE27 Aging Schedules: End of March: Age of Accounts in Days 0-30 31-60 61-90 Payer Large Retail Chain 1 Accts Rec % Total $0 #DIV/0! $0 #DIV/0! $0 #DIV/0! $0 #DIV/0! Large Retail Chain 2 Accts Rec % $0 #DIV/0! $0 #DIV/0! $0 #DIV/0! $0 #DIV/0! Regional Drug Store Accts Rec % $0 #DIV/0! $0 #DIV/0! $0 #DIV/0! $0 #DIV/0! Small Grocery Chain Accts Rec % $0 #DIV/0! $0 #DIV/0! $0 #DIV/0! $0 #DIV/0! Total End of March Accts Rec % $0 #DIV/0! $0 #DIV/0! $0 #DIV/0! $0 #DIV/0! Sales Accts Rec for month Remaining Rec/Sales $0 $0 $0 Total $0 $0 $0 $0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! $0 $0 $0 Total $0 $0 $0 $0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! $0 $0 $0 $0 $0 $0 #DIV/0! #DIV/0! #DIV/0! Uncollected Balances Schedules: End of March: Payer Month Large Retail Chain 1 January February March Quarter Large Retail Chain 2 January February March Quarter Regional Drug Store January February March Page 3 CASE27 Quarter Total $0 #DIV/0! January February March Quarter $0 $0 $0 Total $0 $0 $0 $0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! January February March Quarter $0 $0 $0 Total $0 $0 $0 $0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! Small Grocery Chain Total End of March Quarterly Carrying Costs of Receivables: End of March: Large Retail Chain 1 Large Retail Chain 2 Regional Drug Store Small Grocery Chain Total $0 $0 $0 $0 $0 Page 4 CASE27 Copyright 2014 Health Administration Press ivables. udents bles Balance: n Period (Days): ces Schedule: emaining rec / sales remaining rec / sales maining rec / sales ng rec / sales g Costs of Receivables: End of Mar $0 End of Jun $0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! $0 $0 Page 5 CASE27 End of June: Accounts receivables balance Average daily sales Average collection period (days) $0 $0 #DIV/0! Page 6 CASE27 End of June: Age of Accounts in Days 0-30 31-60 61-90 Payer Large Retail Chain 1 Accts Rec % Total $0 #DIV/0! $0 #DIV/0! $0 #DIV/0! $0 #DIV/0! Large Retail Chain 2 Accts Rec % $0 #DIV/0! $0 #DIV/0! $0 #DIV/0! $0 #DIV/0! Regional Drug Store Accts Rec % $0 #DIV/0! $0 #DIV/0! $0 #DIV/0! $0 #DIV/0! Small Grocery Chain Accts Rec % $0 #DIV/0! $0 #DIV/0! $0 #DIV/0! $0 #DIV/0! Total End of June Accts Rec % $0 #DIV/0! $0 #DIV/0! $0 #DIV/0! $0 #DIV/0! Sales Accts Rec for month Remaining Rec/Sales $0 $0 $0 Total $0 $0 $0 $0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! $0 $0 $0 Total $0 $0 $0 $0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! $0 $0 $0 $0 $0 $0 #DIV/0! #DIV/0! #DIV/0! End of June: Payer Month Large Retail Chain 1 April May June Quarter Large Retail Chain 2 April May June Quarter Regional Drug Store April May June Page 7 CASE27 Quarter Total $0 #DIV/0! April May June Quarter $0 $0 $0 Total $0 $0 $0 $0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! April May June Quarter $0 $0 $0 Total $0 $0 $0 $0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! Small Grocery Chain Total End of June End of June: Large Retail Chain 1 Large Retail Chain 2 Regional Drug Store Small Grocery Chain Total $0 $0 $0 $0 $0 END Page 8