Question

In: Finance

Master Budget Project Okay Company is preparing to build its master budget. The budget will detail...

Master Budget Project
Okay Company is preparing to build its master budget. 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. This will be the first year of operation for Okay Company.
b. Budgeted unit sales by quarter for 2017 are projected as follows: First quarter 6,300, Second quarter 6,100, Third quarter 6,100 & Fourth quarter 6,450. First and second quarter 2018 budgeted sales units is 6,400 each quarter.
c. The selling price is $45 per unit. Sales are estimated to be collected 75% in cash and 25% credit. Of the credit sales, 85% are estimated to be collected in the quarter following the sale and 15% are collected in the second quarter following the sale.
d. Since this is the first year of operations there is no beginning inventory of finished goods at the beginning of the year. Okay’s ending finished good inventory policy is 35% of the following quarter’s unit sales needs.
e. Each unit uses 0.50 hours of direct labor and 2 units of direct materials. Laborers are paid $12 per hour and one unit of direct materials costs $4.50.
f. Since this is the first year of operations there is no beginning inventory of direct materials at the beginning of the year. Okay plans to have 30% of the direct materials needed for the next quarter’s production units on hand at the end of each quarter.
g. Okay buys direct materials on account. 80% of the purchases are paid for in the quarter of acquisition, and the remaining 20% are paid for in the following quarter.
h. Fixed overhead totals $25,000 each quarter. Of this total, $5,000 represents depreciation. All other fixed expenses are paid for in cash in the quarter incurred.
i. Variable overhead is budgeted at $3 per units produced. All variable overhead expenses are paid for in the quarter incurred.
j. Fixed selling and administrative expenses total $15,000 per quarter, including $2,500 depreciation.
k. Variable selling and administrative expenses are budgeted at $1.25 per unit sold. All selling and administrative expenses are paid for in the quarter incurred.
l. Okay will pay quarterly dividends of $10,000. During the fourth quarter, $135,000 of equipment will be purchased.

Required: Prepare a master budget for Okay Company for each quarter of 2017 and for the year in total. The following component budgets must be included:
1.(2 points) Calculate the Ending Cash Balance if 1st Quarter Sales is changed to 6,000 units
2.(2 points) For correct formulas in excel spreadsheet.

Solutions

Expert Solution

Hello

I have copy, pasted my excel output here. If you have any other requirement, let me know in hte comments.

Master Budget

Computation of Sales
Particulars 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter Year
Unit Sales 6300.00 6100.00 6100.00 6450.00 24950.00
Sale Price 45.00 45.00 45.00 45.00 45.00
Sales 283500.00 274500.00 274500.00 290250.00 1122750.00
Recovery of Sales
Cash Sales(75%) 212625.00 205875.00 205875.00 217687.50 842062.50
Credit Sales(25%) 70875.00 68625.00 68625.00 72562.50 280687.50
Collection of Credit Sales
1st Quarter 60243.75 10631.25 70875.00
2nd Qaurter 58331.25 10293.75 68625.00
3rd Quarter 58331.25 10293.75 68625.00
4th Quarter 61678.13 61678.13
Total Cash Collections (1) 272868.75 274837.50 274500.00 289659.38 1111865.63
Opening Finished Goods Inventory 0.00 2135.00 2135.00 2257.50
Consumption of Finished Goods 6300.00 6100.00 6100.00 6450.00 24950.00
Ending Inventory needs 2135.00 2135.00 2257.50 2240.00
Hence, net production of Finished Goods 8435.00 6100.00 6222.50 6432.50 27190.00
Opening Direct Material Inventory(units) 0.00 3660.00 3733.50 3859.50
Consumption of Direct Material(units) 16870.00 12200.00 12445.00 12865.00 54380.00
Ending Inventory Needs(units) 3660.00 3733.50 3859.50 3840.00
Direct Material Purchases(units) 20530.00 12273.50 12571.00 12845.50 58220.00
Payment for Direct Material(units) 16424.00 13924.80 12511.50 12790.60 55650.90
Direct Material Cost (2) 73908.00 62661.60 56301.75 57557.70 250429.05
Direct Labor Cost (3) 50610.00 36600.00 37335.00 38595.00 163140.00
Payment of Overheads
Fixed Overheads 20000.00 20000.00 20000.00 20000.00 80000.00
Variable Overheads 25305.00 18300.00 18667.50 19297.50 81570.00
Total Overheads (4) 45305.00 38300.00 38667.50 39297.50 161570.00
Selling and Admin Overheads
Fixed Overheads 12500.00 12500.00 12500.00 12500.00 50000.00
Variable Overheads 7875.00 7625.00 7625.00 8062.50 31187.50
Total Selling & Admin Expenses (5) 20375.00 20125.00 20125.00 20562.50 81187.50
Payment of Dividend (6) 10000.00 10000.00 10000.00 10000.00 40000.00
Purchase of Equipment (7) 135000.00 135000.00
Net Cash Flows (8) 72670.75 107150.90 112070.75 -11353.33 280539.08
[(1)-(2)-(3)-(4)-(5)-(6)-(7)]
Opening Cash Balance 0.00 72670.75 179821.65 291892.40 0.00
Closing Balance 72670.75 179821.65 291892.40 280539.08 280539.08

(1) Cash Balance at the end of Q1 if sales of Q1 changed to 6000 units = $64,912

Cash Balance at the end of the year if sales of Q1 changed to 6000 units = $272,814.08

I hope this solves your query.

Do give a thumbs up if you find this helpful.


Related Solutions

Master Budget Project Okay Company is preparing to build its master budget. The budget will detail...
Master Budget Project Okay Company is preparing to build its master budget. 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: This will be the first year of operation for Okay Company. Budgeted unit sales by quarter for 2017 are projected as follows: First quarter 6,300, Second quarter 6,100, Third quarter 6,100 & Fourth quarter 6,450. First and second quarter 2018 budgeted sales units...
Budget Preparation Westport Company is preparing its master budget for May. Use the estimates provided to...
Budget Preparation Westport Company is preparing its master budget for May. Use the estimates provided to determine the amounts necessary for each of the following requirements. (Estimates may be related to more than one requirement.) a. What should total sales revenue be if territories E and W estimate sales of 50,000 and 100,000 units, respectively, and the unit selling price is $35? b. If the beginning finished goods inventory is an estimated 6,000 units and the desired ending inventory is...
Budget Preparation Collins Company is preparing its master budget for April. Use the given estimates to...
Budget Preparation Collins Company is preparing its master budget for April. Use the given estimates to determine the amounts necessary for each of the following requirements. (Estimates may be related to more than one requirement.) a. What should total sales revenue be if territories A and B estimate sales of 10,000 and 12,000 units, respectively, and the unit selling price is $40? $Answer b. If the beginning finished goods inventory is an estimated 2,000 units and the desired ending inventory...
Budget Preparation Westport Company is preparing its master budget for May. Use the estimates provided to...
Budget Preparation Westport Company is preparing its master budget for May. Use the estimates provided to determine the amounts necessary for each of the following requirements. (Estimates may be related to more than one requirement.) a. What should total sales revenue be if territories E and W estimate sales of 50,000 and 100,000 units, respectively, and the unit selling price is $27? b. If the beginning finished goods inventory is an estimated 7,000 units and the desired ending in- ventory...
1A . Pargo Company is preparing its master budget for 2017. Relevant data pertaining to its...
1A . Pargo Company is preparing its master budget for 2017. Relevant data pertaining to its sales, production, and direct materials budgets are as follows. Sales:Sales for the year are expected to total 1,000,000 units. Quarterly sales are 20%, 25%,25%,and 30% respectively.The sales price is expected to be $40 per unit for the first three quartersand $45 per unit beginning in the fourth quarter.Sales in the first quarter of 2018 are expected to be 20% higher than the budgeted sales...
Renfro Company is preparing its master budget for May. Renfro’s sales budget shows expected sales for...
Renfro Company is preparing its master budget for May. Renfro’s sales budget shows expected sales for May of 100,000 units. The beginning finished goods inventory is an estimated 12,000 units and the desired ending inventory is 9,000 units. Materials are expected to be purchased at $2 per pound and each unit of product requires 3 pounds of material. Beginning and ending materials inventories are expected to be 24,000 and 20,000 pounds, respectively. Direct labor for each unit produced requires 0.5...
Westport Company is preparing its master budget for May. Use the estimates provided to determine the...
Westport Company is preparing its master budget for May. Use the estimates provided to determine the amounts necessary for each of the following requirements. (Estimates may be related to more than one requirement.) a. What should total sales revenue be if territories E and W estimate sales of 50,000 and 100,000 units, respectively, and the unit selling price is $65? $Answer b. If the beginning finished goods inventory is an estimated 6,000 units and the desired ending inventory is 11,000...
Fortune, Inc., is preparing its master budget for the first quarter. The company sells a single...
Fortune, Inc., is preparing its master budget for the first quarter. The company sells a single product at a price of $25 per unit. Sales (in units) are forecasted at 41,000 for January, 61,000 for February, and 51,000 for March. Cost of goods sold is $12 per unit. Other expense information for the first quarter follows. Commissions 9 % of sales dollars Rent $ 18,000 per month Advertising 13 % of sales dollars Office salaries $ 72,000 per month Depreciation...
5. ABC Company is preparing their master budget and is preparing a schedule of expected cash...
5. ABC Company is preparing their master budget and is preparing a schedule of expected cash collections from sales for the first quarter of 2019. They expect sales in January to be $400,000, February to be $300,000, and March to be $500,000. !0% of the sales will be paid for in cash. The remainder will be charged on account. From past experience, the company knows their customers take 3 months to pay their bills in total, with 40% of a...
Pargo Company is preparing its master budget for 2017. Relevant data pertaining to its sales, production,...
Pargo Company is preparing its master budget for 2017. Relevant data pertaining to its sales, production, and direct materials budgets are as follows. Sales. Sales for the year are expected to total 1,000,000 units. Quarterly sales are 18%, 23%, 23%, and 36%, respectively. The sales price is expected to be $38 per unit for the first three quarters and $43 per unit beginning in the fourth quarter. Sales in the first quarter of 2018 are expected to be 10% higher...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT