Prepare a cash budget for Atlas Products, Inc. for the first year of 20X2, based on
the following information.
The budgeting section of the corporate finance department of Atlas Products
has received the following sales estimates from the marketing department:
Total Sales Credit Sales
December 20X1 $825,000 $770,000
January 20X2 730,000 690,000
February 20X2 840,000 780,000
March 20X2 920,000 855,000
The company has found that, on average, about 25 percent of its credit sales are
collected during the month when the sale is made, and the remaining 75 percent of credit
sales are collected during the month following the sale. As a result, the company uses these
figures for budgeting.
The company estimates its purchases at 60 percent of next month’s sales, and payment
for those purchases are budgeted to lag the purchases by 1 month.
Various disbursements have been estimated as follows:
January February March
Wages and salaries $250,000 $290,000 $290,000
Rent 27,000 27,000 27,000
Other expenses 10,000 12,000 14,000
In addition, a tax payment of $105,000 is due on January 15, and $40,000 in dividends will
be declared in January and paid in March. Also, the company has ordered a $75,000 piece
of equipment. Delivery is scheduled for early January; and payment will be due in February.
The company’s projected cash balance at the beginning of January is $100,000, and the
company desires to maintain a balance of $100,000 at the end of each month.
Prepare a cash budget for Atlas Products, Inc. for the first year of 20X2, based on
the following information.
The budgeting section of the corporate finance department of Atlas Products
has received the following sales estimates from the marketing department:
Total Sales Credit Sales
December 20X1 $825,000 $770,000
January 20X2 730,000 690,000
February 20X2 840,000 780,000
March 20X2 920,000 855,000
The company has found that, on average, about 25 percent of its credit sales are
collected during the month when the sale is made, and the remaining 75 percent of credit
sales are collected during the month following the sale. As a result, the company uses these
figures for budgeting.
The company estimates its purchases at 60 percent of next month’s sales, and payment
for those purchases are budgeted to lag the purchases by 1 month.
Various disbursements have been estimated as follows:
January February March
Wages and salaries $250,000 $290,000 $290,000
Rent 27,000 27,000 27,000
Other expenses 10,000 12,000 14,000
In addition, a tax payment of $105,000 is due on January 15, and $40,000 in dividends will
be declared in January and paid in March. Also, the company has ordered a $75,000 piece
of equipment. Delivery is scheduled for early January; and payment will be due in February.
The company’s projected cash balance at the beginning of January is $100,000, and the
company desires to maintain a balance of $100,000 at the end of each month.
Related questions
| ABC Company, an office supplies specialty store, prepares its master budget on a quarterly basis. | ||||||||||||
| The following data have been assembled to assist in preparing the master budget for the first quarter. | ||||||||||||
| a. | As of December 31 (the end of the prior quarter), the company's general ledger showed the following account balances: | |||||||||||
| Debits | Credits | |||||||||||
| Cash | $48,000 | |||||||||||
| Accounts receivable | 195,000 | |||||||||||
| Inventory | 45,600 | |||||||||||
| Buildings and equipment (net) | 350,000 | |||||||||||
| Acccounts payable | $79,800 | |||||||||||
| Common stock | 450,000 | |||||||||||
| Retained earnings | 108,800 | |||||||||||
| $638,600 | $638,600 | |||||||||||
| b. | Actual sales for December and budgeted sales for the next four months are as follows: | |||||||||||
| December (actual) | January | February | March | April | ||||||||
| $260,000 | $380,000 | $410,000 | $280,000 | $210,000 | ||||||||
| c. | Sales are collected as follows: | |||||||||||
| 25% | collected in cash at the time of the sale | |||||||||||
| 75% | on credit and collected in the month following sale | |||||||||||
| The accounts receivable at December 31 are a result of December credit sales. | ||||||||||||
| d. | The company's gross margin as a percent of sales is | 40% | ||||||||||
| In other words, cost of goods sold is 60% of sales. | ||||||||||||
| e. | Monthly expenses are budgeted as follows: | |||||||||||
| Salaries and wages | $35,000 | per month | ||||||||||
| Advertising | $50,000 | per month | ||||||||||
| Shipping | 4% | of sales | ||||||||||
| Other expenses | 3% | of sales | ||||||||||
| Depreciation, including depreciation on new assets acquired during the quarter, | ||||||||||||
| will be | $42,000 | for the quarter. | ||||||||||
| All selling and administrative expenses, except depreciation, are paid in cash in the month they are incurred. | ||||||||||||
| f. Each month's ending inventory should equal | ||||||||||||
| 20% | of the following month's cost of goods sold | |||||||||||
| g. Inventory purchases are paid for as follows: | ||||||||||||
| 50% | in the month of the purchase | |||||||||||
| with the remaining balance paid in the following month. | ||||||||||||
| h. During February, the company will purchase a new copy machine for | ||||||||||||
| $2,100 | cash. | |||||||||||
| During March, the company will purchase other equipment for | ||||||||||||
| $76,000 | cash. | |||||||||||
| i. | Cash dividends paid in January will be | $35,000 | ||||||||||
| j. | Management wants to maintain a current cash balance of | $20,000 | ||||||||||
| The company has an agreement with the local bank that allows the company to | ||||||||||||
| borrow in increments of $1,000 at the beginning of the month. | ||||||||||||
| The monthly interest rate on the loan is | 1% | |||||||||||
| For simplicity, assume that the interest is not compounded. | ||||||||||||
| The company would, as far as it is able, repay the loan plus any accumulated interest at the end of the quarter. | ||||||||||||
| Required: Using the data above, complete the following statements and schedules for the first quarter using the formats given below. | ||||||||||||
| To receive full credit, all amounts below must be entered as formulas or cell references, except for the financing section of the cash budget. | ||||||||||||
| Submissions using formulas or cell references for the financing section may receive up to 3 points extra credit, | ||||||||||||
| dependent upon the degree to which the formulas can be used for any and all possible scenarios. (Hint: IF statements.) | ||||||||||||
| 1. | Schedule of expected cash collections | January | February | March | Quarter | |||||||
| Cash sales | ||||||||||||
| Credit sales | check figure: | |||||||||||
| Total cash collections | Total cash collections = | |||||||||||
| $1,055,000 | ||||||||||||
| 2a. | Merchandise purchases budget | January | February | March | Quarter | |||||||
| Budgeted cost of goods sold | ||||||||||||
| Desired ending inventory | ||||||||||||
| Total needs | ||||||||||||
| Beginning inventory | ||||||||||||
| Required purchases | ||||||||||||
| 2b. | Schedule of expected cash disbursements for merchandise purchases | |||||||||||
| January | February | March | Quarter | |||||||||
| December purchases | ||||||||||||
| January purchases | check figure: | |||||||||||
| February purchases | Total cash disbursements | |||||||||||
| March purchases | for purchases = | |||||||||||
| Total cash disbursements for purchases | $621,600 | |||||||||||
| 3. | Cash budget | January | February | March | Quarter | |||||||
| Beginning cash balance | ||||||||||||
| Cash collections | ||||||||||||
| Total cash available | ||||||||||||
| Cash disbursements: | ||||||||||||
| Inventory purchases | ||||||||||||
| Selling & admin. expenses | ||||||||||||
| Equipment purchases | ||||||||||||
| Cash dividends | ||||||||||||
| Total cash disbursements | ||||||||||||
| Excess (deficiency) of cash | ||||||||||||
| Financing: | ||||||||||||
| Borrowing | ||||||||||||
| Repayment of principal | ||||||||||||
| Interest | ||||||||||||
| Total financing | check figure: | |||||||||||
| Ending cash balance | Mar. 31 cash balance = | |||||||||||
| $37,650 | ||||||||||||
| 4. | Prepare an absorption costing income statement for the quarter ended March 31 in the space below. | |||||||||||
| check figure: | ||||||||||||
| Net income = | ||||||||||||
| $55,350 | ||||||||||||
| 5. | Prepare a balance sheet as of March 31 in the space below. | |||||||||||
| check figure: | ||||||||||||
| A = L + SE = | ||||||||||||
| $658,950 | ||||||||||||
You have just been hired as a new management trainee by Earrings Unlimited, a distributor of earrings to various retail outlets located in shopping malls across the country. In the past, the company has done very little in the way of budgeting and at certain times of the year has experienced a shortage of cash. Since you are well trained in budgeting, you have decided to prepare a master budget for the upcoming second quarter. To this end, you have worked with accounting and other areas to gather the information assembled below.
The company sells many styles of earrings, but all are sold for the same priceâ$15 per pair. Actual sales of earrings for the last three months and budgeted sales for the next six months follow (in pairs of earrings):
| January (actual) | 21,800 | June (budget) | 51,800 |
| February (actual) | 27,800 | July (budget) | 31,800 |
| March (actual) | 41,800 | August (budget) | 29,800 |
| April (budget) | 66,800 | September (budget) | 26,800 |
| May (budget) | 101,800 | ||
The concentration of sales before and during May is due to Motherâs Day. Sufficient inventory should be on hand at the end of each month to supply 40% of the earrings sold in the following month.
Suppliers are paid $4.90 for a pair of earrings. One-half of a monthâs purchases is paid for in the month of purchase; the other half is paid for in the following month. All sales are on credit. Only 20% of a monthâs sales are collected in the month of sale. An additional 70% is collected in the following month, and the remaining 10% is collected in the second month following sale. Bad debts have been negligible.
Monthly operating expenses for the company are given below:
| Variable: | |||
| Sales commissions | 4 | % of sales | |
| Fixed: | |||
| Advertising | $ | 290,000 | |
| Rent | $ | 27,000 | |
| Salaries | $ | 124,000 | |
| Utilities | $ | 11,500 | |
| Insurance | $ | 3,900 | |
| Depreciation | $ | 23,000 | |
Insurance is paid on an annual basis, in November of each year.
The company plans to purchase $20,500 in new equipment during May and $49,000 in new equipment during June; both purchases will be for cash. The company declares dividends of $21,750 each quarter, payable in the first month of the following quarter.
Selected items from the companyâs March 31 balance sheet are as follows:
| Cash | $ | 83,000 |
| Accounts receivable ($41,700 February sales; $501,600 March sales) | 543,300 | |
| Inventory | 130,928 | |
| Accounts payable | 109,000 | |
| Dividends payable | 21,750 | |
The company maintains a minimum cash balance of $59,000. All borrowing is done at the beginning of a month; any repayments are made at the end of a month.
The company has an agreement with a bank that allows the company to borrow in increments of $1,000 at the beginning of each month. The interest rate on these loans is 1% per month and for simplicity we will assume that interest is not compounded. At the end of the quarter, the company would pay the bank all of the accumulated interest on the loan and as much of the loan as possible (in increments of $1,000), while still retaining at least $59,000 in cash.
Required:
Prepare a master budget for the three-month period ending June 30. Include the following detailed schedules:
2. A cash budget. Show the budget by month and in total. Determine any borrowing that would be needed to maintain the minimum cash balance of $59,000.
