UPDATED!!!!!!

Need Help Please... ACCOUNTING

Opening Balance Sheet

REQUIRED TASK (USE ONLY EXCEL)

1. Opening balance sheet- this is given above
2. Sales budget
3. Schedule of cash collection
4. Production Budget
5. Direct Material Budget
6. Schedule of Cash payments
7. Direct Labour Budget
8. Manufacturing Overhead Budget
9. Ending Finished Goods inventory
10. Selling and administrative budget
11. Cash budget
12. Cost of good manufactured budget
13. Budgeted income statement (with detailed COGS calculation)
14. Budgeted Statement of retained earnings
15. Budgeted balance sheet

PREPARE A MASTER BUDGET

Sales Budget and Cash Collection

• Forecast Sales (unit) January 10,000,February 35,000, March 15,000, April 10,000, May 25,000
• Selling Price: $75.00 per seat cover
• Sales Collected in the following pattern: Same month 60%, 1 month later 30%, 2 months later 10%
• All accounts receivable at the end of December will be collected in January

Inventory Production and raw Materials Details

*Direct Material per unit (needed) not given JUST ASSUME it is 1 (one)

• Desired ending inventory of seat cover is 20% of next months sales
• Inventory on December 31 was 2,270 seat cover
• The fabric required for each seat cover cost $16.00
• The fabric required to be in the ending inventory at the end of each month is 10% of next months requirement
• All A/P on December 31 will be paid in January
• 40% of the cost of fabric is paid in the month of purchase, 60% the next month.

Direct Labour and Manufacturing Overhead Details

• It takes 0.75 hours to make each bed sheet
• We pay our production employees $25 per hour
• The direct labour payments are made in the same moth the employees earned the wages.
• Fixed overhead is $160,000 per month (including $34,000 of depreciation)
• Variable overhead is $8.00 per direct labour hour
• You will need to calculate the predetermined overhead rate

Administrative and Selling Expense Details

• Variable administrative and selling expense are $5.00 per unit sold
• Fixed administrative and selling expense are:

Advertising $65,000

Admin salaries $130,000

Insurance $28,000

Office Depreciation $41,000

• Other cash payments:

February equipment purchase $123,000

February dividends paid $55,000

Cash Management Details

• We have an unlimited capacity short-term demand loan which automatically covers any cash deficiency (short-term bank loan on the B/S)
• Amounts are borrowed at the start of the month, repaid at the end of the month
• Interest is paid based on the amount of the principal repaid
• We require a minimum cash balance of $45,000
• Interest is charged at 10% per year

CHECK FIGURES
		Figure	Schedule
Total cash collections for the quarter		4,147,500	Expected cash collections
Total required production for the quarter (units)		59,730	Production
Raw materials to be purchased for the quarter		957,380	Direct materials
March 31st, 2022 Ending cash balance		316,123	Cash budget
Total cost of goods manufactured for the quarter		2,913,998	Schedule of COGM
Total assets for the quarter ended March 31		2,484,995	Balance sheet

Balance Sheet December 31, 2021 $ ASSETS Current assets: Cash Accounts receivable Raw materials inventory (a) Finished goods inventory (b) Total current assets Capital assets: Land Buildings and equipment (c) Less: Accumulated depreciation Capital assets, net Total assets 102,011 360,000 19,100 111,841 592,952 2,270 disks @ $49.269 240,000 2,000,000 (800,000) 1,440,000 $ 2,032,952 $ 12,000 (Full amount was borrowed 91,200 103,200 LIABILITIES AND SHAREHOLDERS' EQUITY Current liabilities: Short-term bank loan Accounts payable Total current liabilities Shareholders' equity: Common shares Retained earnings Total shareholders' equity Total liabilities and shareholders' equity 640,000 1,289,752 1,929,752 $ 2,032,952 Balance Sheet December 31, 2021 $ ASSETS Current assets: Cash Accounts receivable Raw materials inventory (a) Finished goods inventory (b) Total current assets Capital assets: Land Buildings and equipment (c) Less: Accumulated depreciation Capital assets, net Total assets 102,011 360,000 19,100 111,841 592,952 2,270 disks @ $49.269 240,000 2,000,000 (800,000) 1,440,000 $ 2,032,952 $ 12,000 (Full amount was borrowed 91,200 103,200 LIABILITIES AND SHAREHOLDERS' EQUITY Current liabilities: Short-term bank loan Accounts payable Total current liabilities Shareholders' equity: Common shares Retained earnings Total shareholders' equity Total liabilities and shareholders' equity 640,000 1,289,752 1,929,752 $ 2,032,952