6273 - Receivables Case Study UNIT 2 ASSGN 1
mschachcopy_of_stucase28_5th_edition.xlsx
CASE27
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 |