How do I calculate percentage of receivables to profit vs. to cost? CEO in case would like 20% to profit and 80% to cash costs. Can I calculate with the data I have on the spreadsheet?

CASE27 CASE 28 7/23/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 $100,000 250,000 400,000 600,000 450,000 300,000 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 40% 35% 15% 10% Quarterly Carrying Costs of Receivables: Assumed Collection Pattern: Payer 0-30 days 31-60 days 61-90 days Large Retail Chain 1 35% 50% 15% Large Retail Chain 2 25% 40% 35% Regional Drug Store 20% 35% 45% Page 1 CASE27 Small Grocery Chain 30% Other Inputs: Periodic (quarterly) interest rate Contribution margin 55% 15% 8.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 65% 15% 0% Large Retail Chain 2 75% 35% 0% Regional Drug Store 80% 45% 0% Small Grocery Chain 70% 15% 0% End of March: Accounts receivables balance Average daily sales Average collection period (days) $351,250 $8,333 42.2 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 % $104,000 87.4% $15,000 12.6% $0 0.0% $119,000 100.0% Large Retail Chain 2 Accts Rec % $105,000 77.4% $30,625 22.6% $0 0.0% $135,625 100.0% Regional Drug Store Accts Rec % $48,000 74.0% $16,875 26.0% $0 0.0% $64,875 100.0% Small Grocery Chain Accts Rec % $28,000 88.2% $3,750 11.8% $0 0.0% $31,750 100.0% $285,000 81.1% $66,250 18.9% $0 0.0% $351,250 100.0% Sales Accts Rec for month Remaining Rec/Sales $40,000 $100,000 $160,000 Total $0 $15,000 $104,000 $119,000 0.0% 15.0% 65.0% 39.7% $35,000 $87,500 $140,000 Total $0 $30,625 $105,000 $135,625 0.0% 35.0% 75.0% 51.7% $15,000 $37,500 $60,000 $0 $16,875 $48,000 0.0% 45.0% 80.0% Total End of March Accts Rec % Total 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 $64,875 57.7% January February March Quarter $10,000 $25,000 $40,000 Total $0 $3,750 $28,000 $31,750 0.0% 15.0% 70.0% 42.3% January February March Quarter $100,000 $250,000 $400,000 Total $0 $66,250 $285,000 $351,250 0.0% 26.5% 71.3% 46.8% 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 $9,520 $10,850 $5,190 $2,540 $28,100 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 $351,250 End of Jun $333,000 42.2 28.5 81.1% 18.9% 0.0% 64.2% 35.8% 0.0% 0.0% 26.5% 71.3% 46.8% 0.0% 26.5% 71.3% 24.7% $28,100 $26,640 Page 5 CASE27 End of June: Accounts receivables balance Average daily sales Average collection period (days) $333,000 $11,667 28.5 Page 6 CASE27 End of June: Age of Accounts in Days 0-30 31-60 61-90 Payer Large Retail Chain 1 Accts Rec % $78,000 74.3% $27,000 25.7% $0 0.0% $105,000 100.0% Large Retail Chain 2 Accts Rec % $78,750 58.8% $55,125 41.2% $0 0.0% $133,875 100.0% Regional Drug Store Accts Rec % $36,000 54.2% $30,375 45.8% $0 0.0% $66,375 100.0% Small Grocery Chain Accts Rec % $21,000 75.7% $6,750 24.3% $0 0.0% $27,750 100.0% $213,750 64.2% $119,250 35.8% $0 0.0% $333,000 100.0% Sales Accts Rec for month Remaining Rec/Sales $240,000 $180,000 $120,000 Total $0 $27,000 $78,000 $105,000 0.0% 15.0% 65.0% 19.4% $210,000 $157,500 $105,000 Total $0 $55,125 $78,750 $133,875 0.0% 35.0% 75.0% 28.3% $90,000 $67,500 $45,000 $0 $30,375 $36,000 0.0% 45.0% 80.0% Total End of June Accts Rec % Total 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 $66,375 32.8% April May June Quarter $60,000 $45,000 $30,000 Total $0 $6,750 $21,000 $27,750 0.0% 15.0% 70.0% 20.6% April May June Quarter $600,000 $450,000 $300,000 Total $0 $119,250 $213,750 $333,000 0.0% 26.5% 71.3% 24.7% 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 $8,400 $10,710 $5,310 $2,220 $26,640 END Page 8