Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Please I need help on my accounting problem. Opening Balance Sheet REQUIRED TASK (USE ONLY EXCEL) Opening balance sheet- this is given above Sales budget

Please I need help on my accounting problem.

Opening Balance Sheet

image text in transcribed

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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Cost Accounting A Managerial Emphasis

Authors: Charles T Horngren

5th Edition

0131796712, 978-0131796713

More Books

Students also viewed these Accounting questions