The submission is to be an Excel file.
All calculations are to be shown, including the use of Excelformulas where applicable
All requirements should be on one excel worksheet
All schedules should be properly formatted as demonstrated inyour text (including applicable dollar signs and subtotal/totalunderlines).
Company is trying to determine their cash needs for the firstquarter of 2015. The following information is available:
All sales are on account. 40% of customer accounts are collectedin the months of sale; 55% are collected in the month following thesale. Uncollectibles amounting to 5% of sales are anticipated, andmanagement believes that only 14% of the accounts outstanding onDecember 31, 2014, will be recovered and that the recovery will bein January 2015.
60% of the merchandise purchases are paid for in the month ofpurchase; the remaining 40% are paid for in the month afteracquisition.
The December 31, 2014 balance sheet disclosed the followingselected figures: cash $30,000, accounts receivable $50,000, andaccounts payable $23,000.
Regal Company maintains a $20,000 minimum cash balance at alltimes. Financing is available (and retired) in $1,000 multiples ata 6% per year interest rate, with borrowings taking place at thebeginning of the month and repayments occurring at the end of themonth. Interest is paid at the time of repaying principal andcomputed on the portion of principal repaid at that time.
Additional data:
January:
Sales Revenue: 120,000
Merchandise Purchases: 85,000
Cash Operating Costs: 24,000
Proceeds from Sale of Equipment: n/a
February:
Sales Revenue: 135,000
Merchandise Purchases: 93,000
Cash Operating Costs: 20,000
Proceeds from Sale of Equipment: n/a
March:
Sales Revenue: 160,000
Merchandise Purchases: 102,000
Cash Operating Costs: 35,000
Proceeds from Sale of Equipment: 6,500
Required:
1. Prepare a schedule of total cash collections for Januarythrough March.
2. Prepare a schedule of total cash disbursements for Januarythrough March.
3. Prepare a cash budget for January through March.
The submission is to be an Excel file.
All calculations are to be shown, including the use of Excelformulas where applicable
All requirements should be on one excel worksheet
All schedules should be properly formatted as demonstrated inyour text (including applicable dollar signs and subtotal/totalunderlines).
Company is trying to determine their cash needs for the firstquarter of 2015. The following information is available:
All sales are on account. 40% of customer accounts are collectedin the months of sale; 55% are collected in the month following thesale. Uncollectibles amounting to 5% of sales are anticipated, andmanagement believes that only 14% of the accounts outstanding onDecember 31, 2014, will be recovered and that the recovery will bein January 2015.
60% of the merchandise purchases are paid for in the month ofpurchase; the remaining 40% are paid for in the month afteracquisition.
The December 31, 2014 balance sheet disclosed the followingselected figures: cash $30,000, accounts receivable $50,000, andaccounts payable $23,000.
Regal Company maintains a $20,000 minimum cash balance at alltimes. Financing is available (and retired) in $1,000 multiples ata 6% per year interest rate, with borrowings taking place at thebeginning of the month and repayments occurring at the end of themonth. Interest is paid at the time of repaying principal andcomputed on the portion of principal repaid at that time.
Additional data:
January:
Sales Revenue: 120,000
Merchandise Purchases: 85,000
Cash Operating Costs: 24,000
Proceeds from Sale of Equipment: n/a
February:
Sales Revenue: 135,000
Merchandise Purchases: 93,000
Cash Operating Costs: 20,000
Proceeds from Sale of Equipment: n/a
March:
Sales Revenue: 160,000
Merchandise Purchases: 102,000
Cash Operating Costs: 35,000
Proceeds from Sale of Equipment: 6,500
Required:
1. Prepare a schedule of total cash collections for Januarythrough March.
2. Prepare a schedule of total cash disbursements for Januarythrough March.
3. Prepare a cash budget for January through March.
Related questions
Mary and Kay, Inc., a distributor of cosmetics throughoutFlorida, is in the process of assembling a cash budget for thefirst quarter of 20x1. The following information has been extractedfrom the companyâs accounting records:
All sales are on account. Sixty percent of customer accounts arecollected in the month of sale; 30 percent are collected in thefollowing month. Uncollectibles amounting to 10 percent of salesare anticipated, and management believes that only 20 percent ofthe accounts outstanding on December 31, 20x0, will be recoveredand that the recovery will be in January 20x1.
Sixty percent of the merchandise purchases are paid for in themonth of purchase; the remaining 40 percent are paid for in themonth after acquisition.
The December 31, 20x0, balance sheet disclosed the followingselected figures: cash, $65,000; accounts receivable, $230,000; andaccounts payable, $79,000.
Mary and Kay, Inc. maintains a $65,000 minimum cash balance atall times. Financing is available (and retired) in $1,000 multiplesat an 9 percent interest rate, with borrowings taking place at thebeginning of the month and repayments occurring at the end of themonth. Interest is paid at the time of repaying principal andcomputed on the portion of principal repaid at that time.
Additional data:
| jan. | Feb. | march | |
| sales revenue | 580,000 | 670,000 | 685,000 |
| Merchandise purchases | 400,000 | 430,000 | 550,000 |
| Cash operating costs | 106,000 | 85,000 | 148,000 |
| Proceeds from sale of equipment | 28,000 |
QUESTION 1 : Prepare a schedule that discloses thefirmâs total cash collections for January through March using thechart below.
| january | february | march | |
| collection of accounts receivable | |||
| collection of january sales | |||
| collection of february sales | |||
| collection of march sales | |||
| sale of equipment | |||
| total cash collections |
QUESTION 2: Prepare a schedule that discloses the firmâstotal cash disbursements for January through March.
| January | February | March | |
| Payment of accounts payable | |||
| payment of january purchases | |||
| payment of february purchases | |||
| payment of march purchases | |||
| cash operating costs | |||
| total cash disbursements |
QUESTION 3: Prepare a schedule that summarizes thefirmâs financing cash flows for January through March.
| january | February | March | |
| beginning cash balance | |||
| total receipts | |||
| subtotal | |||
| LESS: total disbursements | |||
| Cash Excess (deficiency) before financing | |||
| Financing: | |||
| Borrowing to maintain 65,000 balance | |||
| loan principal repaid | |||
| loan interest paid | |||
| Ending Cash Balance |
| PROBLEM 22-5B | ||||||||||||
| Near the end of 2015, the management of IsleCorp., a merchandising company, prepared the | ||||||||||||
| following estimated balance sheet for December 31,2015. | ||||||||||||
| ISLE CORPORATION | ||||||||||||
| Estimated Balance Sheet | ||||||||||||
| 31-Dec-15 | ||||||||||||
| Assets | Liabilities and Equity | |||||||||||
| Cash | 36,000 | Accounts payable | 360,000 | |||||||||
| Accounts Receivable | 525,000 | Bank loan Payable | 15,000 | |||||||||
| Inventory | 150,000 | Taxes payable (due 3/15/2016) | 90,000 | |||||||||
| Total Current Assets | 711,000 | Total Liabilities | 465,000 | |||||||||
| Equipment | 540,000 | Common Stock | 472,500 | |||||||||
| Less Accumulated Depreciation | 67,500 | Retained Earnings | 246,000 | |||||||||
| Equipment, net | 472,500 | Total Stockholders' equity | 718,500 | |||||||||
| Total Assets | 1,183,500 | Total liabilities and equity | 1,183,500 | |||||||||
| To prepare the master budget for January, Februaryand March of 2016, management gathers the following | ||||||||||||
| information | ||||||||||||
| a. | Isle Corp.'s single product is purchased for $30per unit and resold for $45 per unit. The | |||||||||||
| expected inventory level of 5,000 units onDecember 31, 2015, is more than management's desired | ||||||||||||
| level for 2015, which is 25% of the next month'sexpected sales (in units). Expected sales are: | ||||||||||||
| January, 6,000 units; February, 8,000 units; March,10,000 units; and April, 9,000 units. | ||||||||||||
| b. | Cash sales and credit sales represent 25% and 75%,respectively, of total sales. Of the credit sales, | |||||||||||
| 60% is collected in the first month after themonth of sale and 40% in the second month after | ||||||||||||
| the month of sale. For the $525,000 accountsreceivable balance at December 31, 2015, | ||||||||||||
| $315,000 is collected in January 2016 and theremaining $210,000 is collected in February 2016. | ||||||||||||
| c. | Merchandise purchases are paid for as follows: 20%in the first month after the month of purchase | |||||||||||
| and 80% in the second month after the month ofpurchase. For the $360,000 accounts payable | ||||||||||||
| balance at December 31, 2015, $72,000 is paid inJanuary 2016 and the remaining $288,000 | ||||||||||||
| is paid in February 2016. | ||||||||||||
| d. | Sales commissions equal to 20% of sales are paideach month. Sales salaries (excluding commissions) | |||||||||||
| are $90,000 per year. | ||||||||||||
| e. | General and administrative salaries are $144,000per year. Maintenance expense equals $3,000 per month | |||||||||||
| and is paid in cash. | ||||||||||||
| f. | Equipment reported in the December 31, 2015balance sheet was purchased in January 2015. It is being | |||||||||||
| depreciated over 8 years under the straight-linemethod with no salvage value. The following amounts for | ||||||||||||
| new equipment purchases are planned in the comingquarter: January, $72,000; February, $96,000; and | ||||||||||||
| March, $28,800. This equipment will be depreciatedusing the straight-line method over 8 years with no | ||||||||||||
| salvage value. A full month's depreciation istaken for the month in which equipment is purchased. | ||||||||||||
| g. | The company plans to acquire land at the end ofMarch at a cost of $150,000, which will be paid with cash | |||||||||||
| on the last day of the month. | ||||||||||||
| h. | Isle Corp. has a working arrangement with its bankto obtain additional loans as needed. The interest rate | |||||||||||
| is 12% per year, and interest is paid at eachmonth-end based on the beginning balance. Partial or full | ||||||||||||
| payments on these loans can be made o the last dayof the month. Isle has agreed to maintain a minimum | ||||||||||||
| ending cash balance of $36,000 in each month. | ||||||||||||
| i. | The income ta rate for the company is 40%. Incometaxes on the first quarter's income will not be paid until | |||||||||||
| April 15. | ||||||||||||
| Prepare a master budget for the first three monthsof 2016. Round to the nearest dollar. | ||||||||||||
