Accounting .

zykeria112

CopyofSP22ComprehensiveProject2Template.xlsx

Home >Business & Finance homework help >Accounting homework help >Accounting .

Instructions

Part I (Chapter 22)

Requirement A

Complete the Master Budget (ALL parts plus schedules) in Excel.

The budget information is provided in a separate document.

To complete your Comprehensive Project 2 assignment, use the master budget example template. You may use the template provided in this workbook or the workbook used for the example completed in class. Using the example enables you to setup the budget in excel ensuring that you have formulas set up correctly and correctly calculated values.

Your Comprehensive Project 2 Assignment will look exactly like the Royal Co. example with the exception of the budget specific standard numbers. You will simply need to change the appropriate numbers. These budget specific standard numbers and any other budget requirements will be provided in a separate document.

There are some parts of the budget that will require you to make some decisions and/or understand how it relates to other/prior parts.

The Cash Budget will require you to determine whether the company will need to borrow money in order to meet cash requirements. If you borrow money, you may need to pay some or all back with interest and you will have to calculate that interest. You will need to understand where how to calculate or where to get the numbers for the income statement and balance sheet.

Read/Review all the information provided as well as chapter 22. This is not an assignment that can be successfully completed in a couple of hours the night it is due.

Requirement B

1. What are the budgeted sales for February?

2. What are the expected cash collections for February?

3. What is the accounts receivable balance at the end of March?

4. What is the estimated cost of raw materials purchases for February?

5. What is the estimated accounts payable balance at the end of March?

6. What is the estimated finished goods inventory balance at the end of March?

7 What is the estimated cost of goods sold and gross margin for March?

8. What is the estimated total selling and administrative expense for February?

9. What is the estimated net operating income for March?

10. What is the estimated retained earnings balance for March?

Part II (Chapter 25)

Requirement C

This question is assignment version specific. Please refer to the assignment document for further details.

Requirement D

This question is assignment version specific. Please refer to the assignment document for further details.

A. Input

_____________________________ Master Budget
Input Section
Sales Budget
Expected unit sales in the first quarter
Selling price
Cash Collections
Beginning Balance
Collected in month of sale
Collected in the month following sale
Uncollectible amount
Production Budget
Desired ending inventory		% of next month's sales
Beginning Inventory		units
Raw Materials Budget
Raw materials per unit		pounds
Cost per pound
Desired ending inventory
Beginning inventory		pounds
Cash payments
Beginning balance
Paid in month of purchase
Paid in the month following purchase
Direct Labor Budget
Direct labor hours per unit
Direct labor cost per hour
Manufacturing Overhead Budget
Variable Manufacturing Overhead Rate		per hour
Fixed Manufacturing Overhead Rate		per month
Depreciation		per month
Selling and Administrative Expense Budget
Variable Selling & Admin Rate		per unit
Fixed Selling & Admin Rate		per month
Depreciation		per month
Cash Budget
Beginning balance
Dividends
Purchase of equipment
Purchase of equipment
Interest Rate

A. Template

_________________________ Master Budget	2021
		units
		units
		units
		units
		units
SALES BUDGET
				Quarter
Budgeted sales (units)
Selling price per unit
Total sales
SCHEDULE OF EXPECTED CASH COLLECTIONS
				Quarter
Accounts receivable beginning balance
April sales
April
May
May sales
May
June
June sales
June
Total cash collections
PRODUCTION BUDGET
				Quarter
Budgeted sales
Add desired ending inventory
Total needs
Less beginning inventory
Required production
DIRECT MATERIALS BUDGET
				Quarter
Required production in units
Raw materials per unit (pounds)
Production needs (pounds)
Add desired ending inventory (pounds)
Total needs (pounds)
Less beginning inventory (pounds)
Raw materials to be purchased (pounds)
Cost of raw materials to be purchased at $0.40 per pound
SCHEDULE OF EXPECTED CASH DISBURSEMENTS FOR MATERIAL
				Quarter
Accounts payable beginning balance
April purchases:
April
May
May purchases:
May
June
June purchases:
June
Total cash disbursements for materials
DIRECT LABOR BUDGET
				Quarter
Required production
Direct labor-hours per unit
Total direct labor–hours needed
Direct labor cost per hour
Total direct labor cost
MANUFACTURING OVERHEAD BUDGET
				Quarter
Budgeted direct labor-hours
Variable manufacturing overhead rate
Variable manufacturing overhead
Fixed manufacturing overhead
Total manufacturing overhead
Less depreciation
Cash disbursements for manufacturing overhead
ENDING FINISHED GOODS INVENTORY BUDGET
Computation of absorption unit product cost:
	Quantity		Cost		Total
Direct materials		pounds		per pound
Direct labor		hours		per hour
Manufacturing overhead		hours		per hour*
Unit product cost
Predetermined Overhead Rate =	Total manufacturing overhead =
	Total direct labor hours
Budgeted ending finished goods inventory:
Ending finished goods inventory in units
Unit product cost
Ending finished goods inventory in dollars
SELLING AND ADMINISTRATIVE EXPENSE BUDGET
				Quarter
Budgeted sales in units
Variable selling and administrative expense per unit
Variable selling and administrative expense
Fixed selling and administrative expense
Total selling and administrative expense
Less depreciation
Cash disbursements for selling and administrative expenses
CASH BUDGET
	Cash Budget
				Quarter
Cash balance, beginning
Add receipts:
Cash collections
Total cash available
Less disbursements:
Direct materials
Direct labor
Manufacturing overhead
Selling & administrative
Equipment purchases
Dividends
Total disbursements
Excess (deficiency) of cash available over disbursements
Financing:
Borrowings
Repayments
Interest*
Total financing
Cash balance, ending
Interest
BUDGETED INCOME STATEMENT
Budgeted Income Statement
Net sales			Computation of net sales
Cost of goods sold			Sales
Gross margin			Less uncollectible amounts
Selling & administrative expenses			Net sales
Net operating income
Interest expense
Net income			Computation of cost of goods sold
			Budgeted sales (units)
			Unit product cost
			Cost of goods sold
BEGINNING BALANCE SHEET (REQUIRED)	(Based on information given, beginning balances and calculations)
Balance Sheet
Current assets:
Cash
Accounts receivable
Raw materials inventory
Finished goods inventory
Plant and equipment:
Land
Buildings and equipment
Accumulated depreciation
Total assets
Liabilities:
Accounts payable
Stockholders’ equity:
Common stock
Retained earnings
Total liabilities and stockholders’ equity
Budgeted Balance Sheet
Current assets:
Cash
Accounts receivable
Raw materials inventory
Finished goods inventory
Plant and equipment:
Land
Buildings and equipment
Accumulated depreciation
Total assets
Liabilities:
Accounts payable
Stockholders’ equity:
Common stock
Retained earnings
Total liabilities and stockholders’ equity

Parts B, C and D

Requirement B
1. What are the budgeted sales for February?
2. What are the expected cash collections for February?
3. What is the accounts receivable balance at the end of March?
4. What is the estimated cost of raw materials purchases for February?
5. What is the estimated accounts payable balance at the end of March?
6. What is the estimated finished goods inventory balance at the end of March?
7 What is the estimated cost of goods sold and gross margin for March?
8. What is the estimated total selling and administrative expense for February?
9. What is the estimated net operating income for March?
10. What is the estimated retained earnings balance for March?
Requirement C
This question is assignment version specific. Please refer to the assignment document for further details.
Reference the DATA from your master budget.
The profit impact is computed as follows:	Alternate Solution 1		Alternate Solution 2
Units
Decision
Requirement D
This question is assignment version specific. Please refer to the assignment document for further details.
Reference the DATA from your master budget.
	Make	Buy
Units
Decision