To prepare the master budget you will need to prepare subsidiary budgets that link together to create the Income Statement and Balance Sheet for the 3 month period.
You should use Excel to prepare your budget. Each minor budget should be on a separate worksheet. Your budget should have its formulas linked so that if one or more elements change the budget can be easily amended.
Your budget figures should be calculated to 2 decimal places, but budgets should only display whole dollars / units.
You have been working with the Production Manager and Finance Manager and together you have come up with budget data, including standard costings and assumptions to apply to the budget.
Required:
Prepare the following Budgets for the months of July, August and September and for the quarter:
a) Sales Budget
b) Production Budget (in units only)
c) Materials Cost Budget
d) Wages Cost Budget
e) Production Overheads Cost Incurred Budget
f) Total Production Cost Incurred Budget (summary of budgets c, d & e)
g) Raw Materials Purchases Budget
h) Marketing and Administration Cost Budget
i) Cash Receipts Budget
j) Cash Payments Budget
k) Summary Cash Budget (summary budgets i & j)
l) Budgeted Income Statement for each month
m) Budgeted Balance Sheet for each month
Budget Preparati ata Budget Assumptions Budgeted Sales Units Month 1 July 2 August 3 September 4 October 5 November COMPANY NAME: Parlby Bullbars Pty Ltd. YEAR: 80 85 70 45 75 2018 i October i), November Percentage of sales collected in month of sale Percentage of sales collected in month after sale Percentage of sales treated as Bad Debts 35.00% 64.00% 1.00% Product Name Standard Unit Selling Price Standard 3 Hoop $990 Finished goods as a percentage of next month's sales Raw materials as a percentage of next month's production 20.00% 35.00% Standard cost Schedule for 1 unit of production Units Raw materials purchases: Meters Unit Price Cost 3.0 $120.00 S360.00 $2.00 $10.00 0.5 $30.00 $15.00 S385.00 Raw Materials Name Steel Material Wielding Wire Paint Percentage paid in the month of purchase Percentage paid in the month after purchase 70.00% 30.00% All other production costs (Labour & Overhead) Percentage paid in month incurred Percentage paid in the following month 80.00% 20.00% Rate per Direct Labour Type Hours Hour $25.00 $100.00 1.0 $24.00 $24.00 $124.00 Fixed Selling and Administration Expenses All paid in the month incurred 100.00% Rate per Variable Selling and Administration Expenses Variable Manufacturing Overhead Hours Hour Percentage paid in the month incurred Percentage paid in the following month 75.00% 25.00% 5.0 $4.00 $20.00 Fixed Manufacturing Overheads Budgeted Normal Monthly Loan Payments Payment Amount Total Completed $ per Month per Month $16,500 Units $6,600 250 $66.00 Tax payable Total Standard Cost 30.00% Tax Rate Month Paid in August, November, February, May $8.00 $18,000 Dividend Variable per unit of product sold Fixed Total per Month $125,000 Amount declared Due in month declared Included as part of Fixed Overhead Included as part of Selling &Administration $6,300 $1,900 Budget Preparati ata Budget Assumptions Budgeted Sales Units Month 1 July 2 August 3 September 4 October 5 November COMPANY NAME: Parlby Bullbars Pty Ltd. YEAR: 80 85 70 45 75 2018 i October i), November Percentage of sales collected in month of sale Percentage of sales collected in month after sale Percentage of sales treated as Bad Debts 35.00% 64.00% 1.00% Product Name Standard Unit Selling Price Standard 3 Hoop $990 Finished goods as a percentage of next month's sales Raw materials as a percentage of next month's production 20.00% 35.00% Standard cost Schedule for 1 unit of production Units Raw materials purchases: Meters Unit Price Cost 3.0 $120.00 S360.00 $2.00 $10.00 0.5 $30.00 $15.00 S385.00 Raw Materials Name Steel Material Wielding Wire Paint Percentage paid in the month of purchase Percentage paid in the month after purchase 70.00% 30.00% All other production costs (Labour & Overhead) Percentage paid in month incurred Percentage paid in the following month 80.00% 20.00% Rate per Direct Labour Type Hours Hour $25.00 $100.00 1.0 $24.00 $24.00 $124.00 Fixed Selling and Administration Expenses All paid in the month incurred 100.00% Rate per Variable Selling and Administration Expenses Variable Manufacturing Overhead Hours Hour Percentage paid in the month incurred Percentage paid in the following month 75.00% 25.00% 5.0 $4.00 $20.00 Fixed Manufacturing Overheads Budgeted Normal Monthly Loan Payments Payment Amount Total Completed $ per Month per Month $16,500 Units $6,600 250 $66.00 Tax payable Total Standard Cost 30.00% Tax Rate Month Paid in August, November, February, May $8.00 $18,000 Dividend Variable per unit of product sold Fixed Total per Month $125,000 Amount declared Due in month declared Included as part of Fixed Overhead Included as part of Selling &Administration $6,300 $1,900
