Question

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:

1. 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

2. The company's gross profit rate is 44 percent of sales.
3. 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.
4. 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.
5. 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.
6. 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.
7. 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

8. 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.
1. 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.
10. 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

1. Create a worksheet for inputs that includes all potential variables that can be changed. Label the worksheet tab as "inputs."
2. Create a worksheet for each of the different budgets. Label the tabs appropriately. The following budgets should be included:
   1. Sales Budget
   2. Inventory Purchases Budget
   3. Selling and Administrative Budget
   4. Cash Collections from Customers Schedule
   5. Cash Paid for Inventory Purchases Schedule
   6. Cash Budget
   7. Budgeted Income Statement
   8. Budgeted Balance Sheet
3. 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).
4. All worksheets should be interactive (i.e. all worksheet pages except the Input worksheet, should be formula driven).
5. We will use the following to grade the project:

1. You will lose 10 points if you do not have separate worksheets for each budget.
2. You will lose 15 points if the project is late.
3. You will lose up to 20 points if your worksheets are not interactive.
4. You will lose up to 20 points on layout.
5. You will lose 15 points for not doing a cash budget.
6. You will lose 15 points for not doing a budgeted income statement.
7. You will lose 15 points for not balancing your budgeted balance sheet.
8. You will lose points based on accuracy also.

6. Email me your file. Label it using your last name followed by your first name. For example, my file would be names bouillonmarv. My email address is [email protected].

Hints for Creating Interactive Cash Budget

1. To calculate borrowings to the nearest thousands, you may use the ROUNDUP function.
2. To calculate repayments, you may use the ROUNDDOWN function.

 

Answer 1

Posted first five parts as per HomeworkLib policy. Please post remaining separately:

Please hit LIKE button if this helped. For any further explanation, please put your query in comment, will get back to you.
a. Sales Budget		Last year	Current year				Working
			Apr	May	Jun	Total	Jul
Budgeted sales units						-
Budgeted Selling Price						$                            -
Total Sales			150,000	160,000	100,000	410,000	110,000
Cash Sales	20% of Sale		$                        30,000	$                32,000	$                 20,000	$                   82,000
Sales on Account	80% of Sale		$                      120,000	$              128,000	$                 80,000	$                328,000
d. Cash Receipt Budget			Apr	May	Jun	Total	Receivable
Accounts Receivable on Apr 1			$                        86,000			$                   86,000
Credit Sale from:
April				$              120,000		$                120,000
May					$               128,000	$                128,000
June						$                            -	$         80,000
Total Collection from receivable			$                        86,000	$              120,000	$               128,000	$                334,000	$         80,000
Cash Sale			$                        30,000	$                32,000	$                 20,000	$                   82,000
Total Collection			$                      116,000	$              152,000	$               148,000	$                416,000
b. Purchase Budget
			Apr	May	Jun	Total	Jul
Budgeted Cost of Goods Sold	56% of Sale		$                        84,000	$                89,600	$                 56,000	$                229,600	$         61,600
Add: Desired ending inventory	25%		$                        22,400	$                14,000	$                 15,400	$                   15,400
Units available			$                      106,400	$              103,600	$                 71,400	$                245,000
Less: Beginning Inventory			$                      (25,000)	$              (22,400)	$               (14,000)	$                (25,000)
Budgeted Purchase			$                        81,400	$                81,200	$                 57,400	$                220,000
e. Cash Disbursment Budget
			Apr	May	Jun	Total	Payable
Accounts Payable Apr 1			$                        50,000			$                   50,000
Merchandise purchased in:
April			$                        40,700	$                40,700		$                   81,400
May				$                40,600	$                 40,600	$                   81,200
June					$                 28,700	$                   28,700	$         28,700
Total Payment for Purchases of Material			$                        90,700	$                81,300	$                 69,300	$                241,300	$         28,700
c. Monthly Selling and admin Expense Budgets			Apr	May	Jun	Total
Utilities	2% of Sale		$                          3,000	$                  3,200	$                   2,000	$                     8,200
Other Expense	4% of Sale		$                          6,000	$                  6,400	$                   4,000	$                   16,400
Salaries and wages			$                        12,000	$                12,000	$                 12,000	$                   36,000
Advertising			$                        15,000	$                15,000	$                 15,000	$                   45,000
Rent			$                        17,000	$                17,000	$                 17,000	$                   51,000
Depreciation			$                          7,500	$                  7,500	$                   7,500	$                   22,500
Total Budgeted Selling Expense			$                        60,500	$                61,100	$                 57,500	$                179,100

В C D E F G н Please hit LIKE button if this helped. For any furth 2 3 a. Sales Budget Current year Working Last year May Total Apr Jun Jul -SUM (D5:F5) F6 5 Budgeted sales units 6 Budgeted Selling Price 7 Total Sales 8 Cash Sales 9 Sales on Account 160000 150000 100000 SUM(D7:F7) -SUM(D8: F8) -SUM(D9: F9) EG4 110000 =F7 20 % -E7 20 % 20% of Sale -D7 20 % -E7 80 % 80% of Sale D7 80 % F7 80% 10 d. Cash Receipt Budget Receivable -D4 -E4 - F4 11 Accounts Receivable on Apr 1 86000 =SUM (D11:F11) 12 Credit Sale from 13 April 14 May 15 June D9 -SUM(D13: F13) -E9 -SUM(D14:F14) -SUM(D15: F15) F$9.100 % 16 17 Total Collection from receivable -SUM(D11:D16) SUM(E11:E16) SUM(G11:G16) =SUM(H11:H16) -SUM(F11: F16 ) 18 Cash Sale D8 -E8 -F8 -SUM(D18:F18) 19 Total Collection -SUM(F17:F18) -SUM(G17:G18) -SUM(D17:D18) -SUM(E17:E18) 20 b. Purchase Budget 21 D4 E4 F4 G4 -H4 ESUM(D22:F22) H7 56 % 22 Budgeted Cost of Goods Sold 23 Add: Desired ending inventory 24 Units available 56% of Sale o.25 D7 56% -E7 56 % =F7 56 % =E22 SB23 F22 SB23 H22 SB23 F23 SUM(G22:G23) ED25 -SUM(G24:G25) SUM(D22:D23) -SUM(E22:E23) D23 SUM(F22:F23) E23 25 Less: Beginning Inventory -25000 -SUM(D24:D25) SUM(E24:E25) 26 Budgeted Purchase 27 e. Cash Disbursment Budget SUM(F24:F25)

e. Cash Disbursment Budget 28 D21 -E21 =F21 -G21 Payable -SUM(D29:F29) 29 Accounts Payable Apr 1 30 Merchandise purchased in 31 April 32 May 50000 D26/2 -SUM(D31:F31) -SUM (D32: F32) -SUM(D33: F33) -D26/2 E26/2 -E32 33 June F26/2 F33 34 Total Payment for Purchases of Material -SUM(G29:G33) -SUM(H29:H33) -SUM(D29:D33) =SUM(E29:E33) -SUM(F29:F33) 35 36 c. Monthly Selling and admin Expense Budgets -D28 -E28 -F28 -G28 37 38 Utilities 39 Other Expense 40 Salaries and wages 41 Advertising 42 Rent 43 Depreciation E7 2% -E7 4 % -F7 2% =F7 4 % -SUM(D38: F38) -SUM(D39: F39) =SUM(D40 : F40) =SUM(D41 : F41) 2% of Sale D7*2% 4% of Sale -D7 4% 12000 D40 -E40 15000 D41 -E41 17000 -D42 -E42 -SUM(D42:F42) =D43 7500 -E43 -SUM(D43:F43) 44 -SUM(G38: G44) 45 Total Budgeted Selling Expense -SUM(F38: F44 ) -SUM(D38:D44) SUM(E38:E44) 46