6273 - Receivables Case Study UNIT 2 ASSGN 1
mschach
copy_of_stucase28_5th_edition.xlsxCASE27
| CASE 28 | Student Version | Copyright 2014 Health Administration Press | ||||||||
| 7/28/15 | ||||||||||
| 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: | $0 | $0 | |||||||
| Month | Gross Sales | |||||||||
| January | $0 | Average Collection Period (Days): | ERROR:#DIV/0! | ERROR:#DIV/0! | ||||||
| February | 0 | |||||||||
| March | 0 | Aging Schedules: | ||||||||
| April | 0 | 0 - 30 days | ERROR:#DIV/0! | ERROR:#DIV/0! | ||||||
| May | 0 | 31 - 60 days | ERROR:#DIV/0! | ERROR:#DIV/0! | ||||||
| June | 0 | 61 - 90 days | ERROR:#DIV/0! | ERROR:#DIV/0! | ||||||
| Sales Mix Forecast: | Uncollected Balances Schedule: | |||||||||
| Payer | % of Sales | January (April) remaining rec / sales | ERROR:#DIV/0! | ERROR:#DIV/0! | ||||||
| Large Retail Chain 1 | 0% | February (May) remaining rec / sales | ERROR:#DIV/0! | ERROR:#DIV/0! | ||||||
| Large Retail Chain 2 | 0% | March (June) remaining rec / sales | ERROR:#DIV/0! | ERROR:#DIV/0! | ||||||
| Regional Drug Store | 0% Caroline Crews: Caroline Crews: Change from 17% to 15% | Quarter remaining rec / sales | ERROR:#DIV/0! | ERROR:#DIV/0! | ||||||
| Small Grocery Chain | 0% Caroline Crews: Caroline Crews: Change from 8% to 10% |
|||||||||
| Quarterly Carrying Costs of Receivables: | $0 | $0 | ||||||||
| 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% | |||||||
| Small Grocery Chain | 0% | 0% | 0% | |||||||
| Other Inputs: | ||||||||||
| Periodic (quarterly) interest rate | 0.0% Caroline Crews: Caroline Crews: Change to new financing cost: 8% annually |
|||||||||
|
Caroline Crews: Caroline Crews: Change from Commonwealth to Foster |
Caroline Crews: Caroline Crews: Change from 17% to 15% |
Caroline Crews: Caroline Crews: Change from 8% to 10% | Contribution margin | 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: | End of June: | |||||||||
| Accounts receivables balance | $0 | Accounts receivables balance | $0 | |||||||
| Average daily sales | $0 | Average daily sales | $0 | |||||||
| Average collection period (days) | ERROR:#DIV/0! | Average collection period (days) | ERROR:#DIV/0! | |||||||
| 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 | $0 | $0 | $0 | $0 | Accts Rec | $0 | $0 | $0 | $0 | |
| % | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | % | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | |
| Large Retail Chain 2 | Large Retail Chain 2 | |||||||||
| Accts Rec | $0 | $0 | $0 | $0 | Accts Rec | $0 | $0 | $0 | $0 | |
| % | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | % | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | |
| Regional Drug Store | Regional Drug Store | |||||||||
| Accts Rec | $0 | $0 | $0 | $0 | Accts Rec | $0 | $0 | $0 | $0 | |
| % | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | % | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | |
| Small Grocery Chain | Small Grocery Chain | |||||||||
| Accts Rec | $0 | $0 | $0 | $0 | Accts Rec | $0 | $0 | $0 | $0 | |
| % | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | % | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | |
| Total End of March | Total End of June | |||||||||
| Accts Rec | $0 | $0 | $0 | $0 | Accts Rec | $0 | $0 | $0 | $0 | |
| % | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | % | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/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 | $0 | $0 | ERROR:#DIV/0! | April | $0 | $0 | ERROR:#DIV/0! | |||
| February | $0 | $0 | ERROR:#DIV/0! | May | $0 | $0 | ERROR:#DIV/0! | |||
| March | $0 | $0 | ERROR:#DIV/0! | June | $0 | $0 | ERROR:#DIV/0! | |||
| Quarter | Total | $0 | ERROR:#DIV/0! | Quarter | Total | $0 | ERROR:#DIV/0! | |||
| Large Retail Chain 2 | Large Retail Chain 2 | |||||||||
| January | $0 | $0 | ERROR:#DIV/0! | April | $0 | $0 | ERROR:#DIV/0! | |||
| February | $0 | $0 | ERROR:#DIV/0! | May | $0 | $0 | ERROR:#DIV/0! | |||
| March | $0 | $0 | ERROR:#DIV/0! | June | $0 | $0 | ERROR:#DIV/0! | |||
| Quarter | Total | $0 | ERROR:#DIV/0! | Quarter | Total | $0 | ERROR:#DIV/0! | |||
| Regional Drug Store | Regional Drug Store | |||||||||
| January | $0 | $0 | ERROR:#DIV/0! | April | $0 | $0 | ERROR:#DIV/0! | |||
| February | $0 | $0 | ERROR:#DIV/0! | May | $0 | $0 | ERROR:#DIV/0! | |||
| March | $0 | $0 | ERROR:#DIV/0! | June | $0 | $0 | ERROR:#DIV/0! | |||
| Quarter | Total | $0 | ERROR:#DIV/0! | Quarter | Total | $0 | ERROR:#DIV/0! | |||
| Small Grocery Chain | Small Grocery Chain | |||||||||
| January | $0 | $0 | ERROR:#DIV/0! | April | $0 | $0 | ERROR:#DIV/0! | |||
| February | $0 | $0 | ERROR:#DIV/0! | May | $0 | $0 | ERROR:#DIV/0! | |||
| March | $0 | $0 | ERROR:#DIV/0! | June | $0 | $0 | ERROR:#DIV/0! | |||
| Quarter | Total | $0 | ERROR:#DIV/0! | Quarter | Total | $0 | ERROR:#DIV/0! | |||
| Total End of March | Total End of June | |||||||||
| January | $0 | $0 | ERROR:#DIV/0! | April | $0 | $0 | ERROR:#DIV/0! | |||
| February | $0 | $0 | ERROR:#DIV/0! | May | $0 | $0 | ERROR:#DIV/0! | |||
| March | $0 | $0 | ERROR:#DIV/0! | June | $0 | $0 | ERROR:#DIV/0! | |||
| Quarter | Total | $0 | ERROR:#DIV/0! | Quarter | Total | $0 | ERROR:#DIV/0! | |||
| Quarterly Carrying Costs of Receivables: | ||||||||||
| End of March: | End of June: | |||||||||
| Large Retail Chain 1 | $0 | Large Retail Chain 1 | $0 | |||||||
| Large Retail Chain 2 | $0 | Large Retail Chain 2 | $0 | |||||||
| Regional Drug Store | $0 | Regional Drug Store | $0 | |||||||
| Small Grocery Chain | $0 | Small Grocery Chain | $0 | |||||||
| Total | $0 | Total | $0 | |||||||
| END |
