Question
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
REQUIRED TASK (USE ONLY EXCEL)
- Opening balance sheet- this is given above
- Sales budget
- Schedule of cash collection
- Production Budget
- Direct Material Budget
- Schedule of Cash payments
- Direct Labour Budget
- Manufacturing Overhead Budget
- Ending Finished Goods inventory
- Selling and administrative budget
- Cash budget
- Cost of good manufactured budget
- Budgeted income statement (with detailed COGS calculation)
- Budgeted Statement of retained earnings
- 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 |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started