PREPARE A MASTER BUDGET

Sales Budget and Cash Collection

• Forecast Sales (unit)

January	February	March	April	May
10,000	35,000	15,000	10,000	25,000

• Selling Price: $75.00 per bedsheet
• Sales Collected in the following pattern:

Same Month	1 month Later	2 months later
60%	30%	10%

• All accounts receivable at the end of December will be collected in January

Inventory Production and raw Materials Details

• Desired ending inventory of bed sheet is 20% of next months sales
• Inventory on December 31 was 2,270 bedsheets
• The fabric required for each bed sheet 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

REQUIRED TASK (USE ONLY EXCEL)

1. Opening balance sheet
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

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