Assignment: Master Budget Project
You are going to help a company that makes widgets develop their master budget. The company is preparing to build its master budget for the coming year (2018). The budget will detail each quarter's activity and the activity for the year in total. The master budget will be based on the following information:
a. Fourth-quarter sales for 2017 are 55,000 units.
b. Unit sales by quarter (for 2018) are projected as follows:
First quarter 65,000
Second quarter 70,000
Third quarter 75,000
Fourth quarter 90,000
The selling price is $300 per unit. 85% of all sales are collected in cash in the same quarter as the sale and remaining 15% of sales are collected in the following quarter. There are no bad debts.
c. The company has a policy that 15% of the following quarter's sales units should be in ending finished goods inventory. Sales units for first and second quarter of 2019are forecasted for 75,000 units.
d. Each unit uses five hours of direct labor and seven units of direct materials. Laborers are paid $10 per hour, and one unit of direct materials costs $12.
e. At the end of each quarter, they plan to have 30% of the direct materials needed for the next quarter's production units.
f. This company buys direct materials on account. Half of the purchases are paid for in the quarter of acquisition, and the remaining half are paid for in the following quarter. Wages and salaries are paid in the quarter incurred.
g. Fixed overhead totals $1,000,000 each quarter. Of this total, $350,000 represents depreciation. All other fixed expenses are paid for in cash in the quarter incurred. The fixed overhead rateper unit is computed by dividing the year's total fixed overhead by the year's budgeted production in units.
h. Variable overhead is budgeted at $20 per unit produced. All variable overhead expenses are paid for in the quarter incurred.
i. Fixed selling and administrative expenses total $1,250,000 per quarter, including $50,000 depreciation.
j. Variable selling and administrative expenses are budgeted at $50 per unit sold. All selling and administrative expenses are paid for in the quarter incurred.
k. At the end of the fourth quarter, $2,000,000 of equipment will be purchased.
l. Balances in the following accounts as of Dec. 31, 2017 should be:
Cash $762,250
Accounts receivable $2,475,000
Accounts payable $7,248,000 (For purchase of direct materials only)
Required:
Prepare a master budget for theCompany for each quarter of 2018 and for the year in total.
The following component budgets must be included:
1. Sales budget
2. Production budget
3. Direct materials purchases budget
4. Direct labor budget
5. Overhead budget
6. Operating expenses budget
7. Budgeted income statement (using absorption costing) (Note:Ignore income taxes.)
8. Cash budget
9. Calculate 2018 Net Income if 4thQtr Sales for 2018 is changed to 75,000 units
10. Calculate the Ending Cash Balance of 4th QTR if 4thQtr Sales for 2018 is changed to 75,000 units
11. For correct formulas in excel spreadsheet.
Attachment:- Budget-Project-TEMPLATE.rar