Carter Corporation prepares its master budget on a quarterly basis The following data have been assembled to assist in preparation of the master budget for the second quarter of 2019 Actual sales for March and budgeted sales for the next four months are as follows March $120,000 April 150,000 May 160,000 June 100,000 July 110,000 The company's gross profit rate is 44 percent of sales Monthly expenses are budgeted as follows salaries and wages, $12,000 per month advertising, $15,000 per month utilities, 2 percent of sales depreciation, $7,500 per month other expense, 4 percent of sales and rent, $17,000 per month At the end of each month, inventory is to be on hand equal to 25 percent of the following month's sales needs, stated at cost One half of a month's inventory purchases is paid for in the month of purchase the other half is paid for in the following month Sales are 20 percent for cash and 80 percent on credit All credit sale terms are n 30 therefore, accounts are collected the month following sale The accounts receivable at March 31 are a result of March credit sales As of March 31, 2019 (the end of the prior quarter), the company's general ledger showed the following account balances Debits Credits Cash $24,000 Accounts Receivable 86,000 Inventory 25,000 Plant and Equip (net) 99,000 Accounts Payable $50,000 Capital Stock 125,000 Retained earnings 59,000 $234,000 $234,000 During May, the company will purchase a new copy machine for $2,000 cash During June, other equipment will be purchased for cash at a cost of $20,000 Assume there will be no equipment purchases in April 2019 During April, the company will declare and pay $14,000 in cash dividends Assume no dividends will be paid in May or June of 2019 The company must maintain a minimum cash balance of $7,500 An open line of credit is available at a local bank for any borrowing that may be needed during the quarter All borrowing is done at the beginning of a month, and all repayments are made at the end Borrowings and repayments of principal must be in multiples of $1,000 Interest is paid at the end of each month The interest rate is 12 percent per annum (Figure interest on whole months, e g , 1 12, 2 12 ) Required Prepare a budgeting spreadsheet that is interactive It should automatically update when changes are made to the input data, such as changes in sales forecast, equipment purchases, etc Spreadsheets Hints Create a worksheet for inputs that includes all potential variables that can be changed Label the worksheet tab as inputs Create a worksheet for each of the different budgets Label the tabs appropriately The following budgets should be included Cash Budget Budgeted Income Statement Budgeted Balance Sheet Each budget should be on a separate worksheet have a heading centered over the rest of the budget that includes the following Name of Company Name of Budget Date December 31, 2019 or For the Quarter ended December 31, 2019 be prepared on the monthly basis with a total column for the quarter The budgeted income statement and budgeted balance sheet should be on the quarter basis (not monthly)

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 14, 2024

Carter Corporation prepares its master budget on a quarterly basis. The following data have been assembled to assist in preparation of the master budget for

Carter Corporation prepares its master budget on a quarterly basis. The following data have been assembled to assist in preparation of the master budget for the second quarter of 2019:

Actual sales for March and budgeted sales for the next four months are as follows:

March $120,000

April 150,000

May 160,000

June 100,000

July 110,000

The company's gross profit rate is 44 percent of sales.
Monthly expenses are budgeted as follows: salaries and wages, $12,000 per month; advertising, $15,000 per month; utilities, 2 percent of sales; depreciation, $7,500 per month; other expense, 4 percent of sales; and rent, $17,000 per month.
At the end of each month, inventory is to be on hand equal to 25 percent of the following month's sales needs, stated at cost.
One half of a month's inventory purchases is paid for in the month of purchase; the other half is paid for in the following month.
Sales are 20 percent for cash and 80 percent on credit. All credit sale terms are n/30; therefore, accounts are collected the month following sale. The accounts receivable at March 31 are a result of March credit sales.
As of March 31, 2019 (the end of the prior quarter), the company's general ledger showed the following account balances:

Debits Credits

Cash $24,000 Accounts Receivable 86,000

Inventory 25,000

Plant and Equip (net) 99,000

Accounts Payable $50,000

Capital Stock 125,000

Retained earnings _______ 59,000

$234,000 $234,000

During May, the company will purchase a new copy machine for $2,000 cash. During June, other equipment will be purchased for cash at a cost of $20,000. Assume there will be no equipment purchases in April 2019.
During April, the company will declare and pay $14,000 in cash dividends. Assume no dividends will be paid in May or June of 2019.
The company must maintain a minimum cash balance of $7,500. An open line of credit is available at a local bank for any borrowing that may be needed during the quarter. All borrowing is done at the beginning of a month, and all repayments are made at the end. Borrowings and repayments of principal must be in multiples of $1,000. Interest is paid at the end of each month. The interest rate is 12 percent per annum. (Figure interest on whole months, e.g., 1/12, 2/12.)

Required:

Prepare a budgeting spreadsheet that is interactive. It should automatically update when changes are made to the input data, such as changes in sales forecast, equipment purchases, etc.

Spreadsheets Hints

Create a worksheet for inputs that includes all potential variables that can be changed. Label the worksheet tab as "inputs."
Create a worksheet for each of the different budgets. Label the tabs appropriately. The following budgets should be included:
1. Cash Budget
2. Budgeted Income Statement
3. Budgeted Balance Sheet
Each budget should:
1. be on a separate worksheet
2. have a heading centered over the rest of the budget that includes the following:
  1. Name of Company
  2. Name of Budget
  3. Date: December 31, 2019 or For the Quarter ended December 31, 2019
3. be prepared on the monthly basis with a total column for the quarter. The budgeted income statement and budgeted balance sheet should be on the quarter basis (not monthly).