Discussion posts
koehler1
copy_of_incase28_5th_edition.xlsxCASE27
| CASE 28 | Instructor Version | Copyright 2014 Health Administration Press | ||||||||
| 7/6/17 | ||||||||||
| FOSTER PHARMACEUTICALS Caroline Crews: Caroline Crews: Change from Commonwealth to Foster |
||||||||||
| 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 analysis--no 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: | KEY OUTPUT: | |||||||||
| End of Mar | End of Jun | |||||||||
| Sales Forecast: | Accounts Receivables Balance: | $351,250 | $333,000 | |||||||
| Month | Gross Sales | |||||||||
| January | $100,000 | Average Collection Period (Days): | 42.2 | 28.5 | ||||||
| February | 250,000 | |||||||||
| March | 400,000 | Aging Schedules: | ||||||||
| April | 600,000 | 0 - 30 days | 81.1% | 64.2% | ||||||
| May | 450,000 | 31 - 60 days | 18.9% | 35.8% | ||||||
| June | 300,000 | 61 - 90 days | 0.0% | 0.0% | ||||||
| Sales Mix Forecast: | Uncollected Balances Schedule: | |||||||||
| Payer | % of Sales | January (April) remaining rec / sales | 0.0% | 0.0% | ||||||
| Large Retail Chain 1 | 40% | February (May) remaining rec / sales | 26.5% | 26.5% | ||||||
| Large Retail Chain 2 | 35% | March (June) remaining rec / sales | 71.3% | 71.3% | ||||||
| Regional Drug Store | 15% Caroline Crews: Caroline Crews: Change from 17% to 10% | Quarter remaining rec / sales | 46.8% | 24.7% | ||||||
| Small Grocery Chain | 10% Caroline Crews: Caroline Crews: Change from 8% to 10% |
|||||||||
| Quarterly Carrying Costs of Receivables: | $5,620 | $5,328 | ||||||||
| 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% | |||||||
| Small Grocery Chain | 30% | 55% | 15% | |||||||
| Other Inputs: | ||||||||||
| Periodic (quarterly) interest rate | 2.0% Caroline Crews: Caroline Crews: Change formula from 10%/4 to 8%/4 |
|||||||||
|
Caroline Crews: Caroline Crews: Change from Commonwealth to Foster |
Caroline Crews: Caroline Crews: Change from 17% to 10% |
Caroline Crews: Caroline Crews: Change from 8% to 10% | Contribution margin | 20.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: | End of June: | |||||||||
| Accounts receivables balance | $351,250 | Accounts receivables balance | $333,000 | |||||||
| Average daily sales | $8,333 | Average daily sales | $11,667 | |||||||
| Average collection period (days) | 42.2 | Average collection period (days) | 28.5 | |||||||
| Aging Schedules: | ||||||||||
| End of March: | End of June: | |||||||||
| Age of Accounts in Days | Age of Accounts in Days | |||||||||
| Payer | 0-30 | 31-60 | 61-90 | Total | Payer | 0-30 | 31-60 | 61-90 | Total | |
| Large Retail Chain 1 | Large Retail Chain 1 | |||||||||
| Accts Rec | $104,000 | $15,000 | $0 | $119,000 | Accts Rec | $78,000 | $27,000 | $0 | $105,000 | |
| % | 87.4% | 12.6% | 0.0% | 100.0% | % | 74.3% | 25.7% | 0.0% | 100.0% | |
| Large Retail Chain 2 | Large Retail Chain 2 | |||||||||
| Accts Rec | $105,000 | $30,625 | $0 | $135,625 | Accts Rec | $78,750 | $55,125 | $0 | $133,875 | |
| % | 77.4% | 22.6% | 0.0% | 100.0% | % | 58.8% | 41.2% | 0.0% | 100.0% | |
| Regional Drug Store | Regional Drug Store | |||||||||
| Accts Rec | $48,000 | $16,875 | $0 | $64,875 | Accts Rec | $36,000 | $30,375 | $0 | $66,375 | |
| % | 74.0% | 26.0% | 0.0% | 100.0% | % | 54.2% | 45.8% | 0.0% | 100.0% | |
| Small Grocery Chain | Small Grocery Chain | |||||||||
| Accts Rec | $28,000 | $3,750 | $0 | $31,750 | Accts Rec | $21,000 | $6,750 | $0 | $27,750 | |
| % | 88.2% | 11.8% | 0.0% | 100.0% | % | 75.7% | 24.3% | 0.0% | 100.0% | |
| Total End of March | Total End of June | |||||||||
| Accts Rec | $285,000 | $66,250 | $0 | $351,250 | Accts Rec | $213,750 | $119,250 | $0 | $333,000 | |
| % | 81.1% | 18.9% | 0.0% | 100.0% | % | 64.2% | 35.8% | 0.0% | 100.0% | |
| Uncollected Balances Schedules: | ||||||||||
| End of March: | End of June: | |||||||||
| Accts Rec | Remaining | Accts Rec | Remaining | |||||||
| Payer | Month | Sales | for month | Rec/Sales | Payer | Month | Sales | for month | Rec/Sales | |
| Large Retail Chain 1 | Large Retail Chain 1 | |||||||||
| January | $40,000 | $0 | 0.0% | April | $240,000 | $0 | 0.0% | |||
| February | $100,000 | $15,000 | 15.0% | May | $180,000 | $27,000 | 15.0% | |||
| March | $160,000 | $104,000 | 65.0% | June | $120,000 | $78,000 | 65.0% | |||
| Quarter | Total | $119,000 | 39.7% | Quarter | Total | $105,000 | 19.4% | |||
| Large Retail Chain 2 | Large Retail Chain 2 | |||||||||
| January | $35,000 | $0 | 0.0% | April | $210,000 | $0 | 0.0% | |||
| February | $87,500 | $30,625 | 35.0% | May | $157,500 | $55,125 | 35.0% | |||
| March | $140,000 | $105,000 | 75.0% | June | $105,000 | $78,750 | 75.0% | |||
| Quarter | Total | $135,625 | 51.7% | Quarter | Total | $133,875 | 28.3% | |||
| Regional Drug Store | Regional Drug Store | |||||||||
| January | $15,000 | $0 | 0.0% | April | $90,000 | $0 | 0.0% | |||
| February | $37,500 | $16,875 | 45.0% | May | $67,500 | $30,375 | 45.0% | |||
| March | $60,000 | $48,000 | 80.0% | June | $45,000 | $36,000 | 80.0% | |||
| Quarter | Total | $64,875 | 57.7% | Quarter | Total | $66,375 | 32.8% | |||
| Small Grocery Chain | Small Grocery Chain | |||||||||
| January | $10,000 | $0 | 0.0% | April | $60,000 | $0 | 0.0% | |||
| February | $25,000 | $3,750 | 15.0% | May | $45,000 | $6,750 | 15.0% | |||
| March | $40,000 | $28,000 | 70.0% | June | $30,000 | $21,000 | 70.0% | |||
| Quarter | Total | $31,750 | 42.3% | Quarter | Total | $27,750 | 20.6% | |||
| Total End of March | Total End of June | |||||||||
| January | $100,000 | $0 | 0.0% | April | $600,000 | $0 | 0.0% | |||
| February | $250,000 | $66,250 | 26.5% | May | $450,000 | $119,250 | 26.5% | |||
| March | $400,000 | $285,000 | 71.3% | June | $300,000 | $213,750 | 71.3% | |||
| Quarter | Total | $351,250 | 46.8% | Quarter | Total | $333,000 | 24.7% | |||
| Quarterly Carrying Costs of Receivables: | ||||||||||
| End of March: | End of June: | |||||||||
| Large Retail Chain 1 | $1,904 | Large Retail Chain 1 | $1,680 | |||||||
| Large Retail Chain 2 | $2,170 | Large Retail Chain 2 | $2,142 | |||||||
| Regional Drug Store | $1,038 | Regional Drug Store | $1,062 | |||||||
| Small Grocery Chain | $508 | Small Grocery Chain | $444 | |||||||
| Total | $5,620 | Total | $5,328 | |||||||
| END |
