Question
In: Accounting
ABC Manufacturing Company produces and sells one product, a console table. Below is information on its...
ABC Manufacturing Company produces and sells one product, a console table. Below is information on its activities for the next few months. 1. Sales projections for the coming months are as follows: Estimated Sales (in units) July 11,000 August 12000 September 10,000 October 9,000
Actual sales in May were 10,000 units; actual sales in June were 14,000 units.
2. ABC console table’s selling price is $450/unit. Estimated cash collections from sales of each month (including June) of console tables to customers are as follows: 55% collected in the month of sale, 40% collected in the month following sale, and 5% cannot be collected.
3. Desired ending inventory of ABC console tables is 20% of the next month’s projected sales .There are 5,000 units of console tables in inventory on June 30.
4. Two materials are used in the production of ABC console table: Red Oak and Glass. Materials requirements per unit of console table are as follows:
Direct Material Units of direct materials Cost per unit
per unit of ABC console table
Red Oak: 42 pounds $5.00/pound
Glass: 9 square feet $2.00/square foot
Desired ending inventory of Red Oak is 25% of the following month’s production need because Red Oak is sometimes in short supply; desired ending inventory of Glass is 10% of the following month’s production need . Inventories of materials as of June 30 are 25,500 pounds of Red Oak and 7,000 square feet of Glass.
5. The company pays for materials purchases as follows: 55% in the month of purchase, and 45% in the month following purchase. The accounts payable balance (due to materials purchases) on June 30 was $950,000, which is all payable in July.
6. ABC console tables have two departments to pass before they are completed. Department Direct labor hours per unit of console table Cost per direct labor hour Shaping: 0.75 hour $24 Finishing: 2 hours $16
7. Direct labor costs are paid in cash as incurred.
8. Total variable manufacturing overhead is estimated at $2.5/direct labor hour incurred. Total fixed manufacturing overhead is estimated at $500,000/month, of which $5,000 is depreciation on factory buildings and equipment. Overhead costs are paid when incurred.
9. Total variable selling and administrative costs are $2/unit of ABC console table sold. Total fixed selling and administrative costs are estimated at $300,000/month, of which $65,000 is depreciation on administrative buildings and equipment. Selling and administrative costs are paid as the costs are incurred
Required: (30 pts)I Preparation of Spreadsheet File
Create one Excel spreadsheet file consisting of the following five separate worksheets:
Sheet 1: Data This worksheet contains the data necessary to do all the other worksheets. List on this worksheet all of the data shown above, clearly labeled. None of the other worksheets should contain any numbers; they should contain ONLY FORMULAS– all cells on the other worksheets should be linked to cells in the data worksheet, cells within the same worksheet, or cells in the other worksheets. For example, the cell for direct labor cost for July should contain a formula that multiplies the production in units for July (from the production schedule) times the labor hours per unit (from the data worksheet) times the hourly wage rate (from the data worksheet). So if the sales estimate for console tables changes, you should be able to make the change only on the data worksheet; all the other worksheets should automatically adjust to the changes. The production units will change; the manufacturing costs will change, etc. The data worksheet can be in any format; just be sure to label each data item clearly.
Sheet 2: Include the following two budgets on the second worksheet, clearly labeled: Sales Budget: Prepare a schedule of sales revenue and cash receipts from sales for each of the months of July, August and September. List cash collections separately on lines as follows: cash collections from sales one month ago and cash collections from sales in the current month. Also, list total cash collections in July, August and September. Production Budget: Prepare a production budget for ABC console tables, in units, for each of the months of July, August and September.
Sheet 3: Include the following one budget on the third worksheet, clearly labeled: Direct Materials Cost Budget: Prepare a direct materials purchases budget, in units and in total dollars, for July and August. List Red Oak purchase costs, Glass purchase costs, and total material purchase costs separately. List payments for current month purchase and for prior month purchase on separate lines. Also, list total payments in July and August.
Sheet 4: Include the following one budget on the fourth worksheet, clearly labeled: Direct Labor Cost Budget: Prepare the budget for costs of direct labor used for July and August, in units and in total dollars. List shaping labor cost, finishing labor cost, and total labor cost separately. Also, list total payments for each month.
Sheet 5: Selling & Administrative Expense Budget: Prepare a selling and administrative expense budget for July and August. Show variable selling and administrative costs, fixed selling and administrative costs and total selling and administrative costs separately for each month. List total cash payments for each month.
Solutions
Expert Solution
Please give positive ratings so I can
keep answering. It would help me a lot. Please comment if you have
any query. Thanks!  |
| Due to character limit I am attaching the image. |
| ABC Manufacturing Company | ||||||
| Sheet 1 | June | July | August | September | Total | Note |
| Budgeted Sales units | 14,000.00 | 11,000.00 | 12,000.00 | 10,000.00 | A | |
| Sell price per unit | 450.00 | 450.00 | 450.00 | 450.00 | B | |
| Budgeted Sales Revenue | 6,300,000.00 | 4,950,000.00 | 5,400,000.00 | 4,500,000.00 | 14,850,000.00 | C=A*B |
| Sheet 2 | ||||||
| Collection Budget | June | July | August | September | Total | |
| 55% of sale in the same month | 3,465,000.00 | 2,722,500.00 | 2,970,000.00 | 2,475,000.00 | 8,167,500.00 | D= 55% of C |
| 40% of sale in the following month | 2,520,000.00 | 1,980,000.00 | 2,160,000.00 | 6,660,000.00 | E= 40% of C of previous month. | |
| Total Scheduled Collections | 5,242,500.00 | 4,950,000.00 | 4,635,000.00 | 14,827,500.00 |
| Sheet 3 | |||||||
| Production Budget | June | July | August | September | Total | October | |
| Budgeted Sales units | 14,000.00 | 11,000.00 | 12,000.00 | 10,000.00 | 9,000.00 | See A | |
| Add: Closing | 2,200.00 | 2,400.00 | 2,000.00 | 1,800.00 | F= 20% of A of next month. | ||
| Less: Opening | 2,800.00 | 2,200.00 | 2,400.00 | 2,000.00 | G= 20% of A of same month. | ||
| Production Budget | 13,400.00 | 11,200.00 | 11,600.00 | 9,800.00 | H=A+F-G |
| Material Purchase Budget- Red Oak | July | August | September | Total | |
| Production Budget | 11,200.00 | 11,600.00 | 9,800.00 | See H | |
| Red Oak required per unit | 42.00 | 42.00 | 42.00 | I | |
| Red Oak required | 470,400.00 | 487,200.00 | 411,600.00 | J=H*I | |
| Add: Closing | 121,800.00 | 102,900.00 | K= 25% of J of next month. | ||
| Less: Opening | 25,500.00 | 121,800.00 | L= 25% of L of same month. | ||
| Material Purchase Budget- Red Oak | 566,700.00 | 468,300.00 | M=J+K-L | ||
| Cost per pound | 5.00 | 5.00 | N | ||
| Direct Material cost- Red Oak | 2,833,500.00 | 2,341,500.00 | 5,175,000.00 | O=M*N | |
| Material Purchase Budget- Glass | July | August | September | Total | |
| Production Budget | 11,200.00 | 11,600.00 | 9,800.00 | See H | |
| Glass required per unit | 9.00 | 9.00 | 9.00 | P | |
| Glass required | 100,800.00 | 104,400.00 | 88,200.00 | Q=H*P | |
| Add: Closing | 10,440.00 | 8,820.00 | R= 10% of Q of next month. | ||
| Less: Opening | 7,000.00 | 10,440.00 | S= 10% of Q of same month. | ||
| Material Purchase Budget- Glass | 104,240.00 | 102,780.00 | T=Q+R-S | ||
| Cost per pound | 2.00 | 2.00 | U | ||
| Direct Material cost- Glass | 208,480.00 | 205,560.00 | 414,040.00 | V=T*U | |
| Total Direct Material cost | 3,041,980.00 | 2,547,060.00 | 5,589,040.00 | W=O+V | |
| 55% paid in same month | 1,673,089.00 | 1,400,883.00 | 3,073,972.00 | X=W*55% | |
| 45% paid in next month | 950,000.00 | 1,275,075.00 | 2,225,075.00 | Y= 45% of W of previous month. | |
| Direct Material Payment | 2,623,089.00 |
 ekkarill92 answered 1 year agoRelated SolutionsCVP – ABC Company The following information pertains to ABC Company and its product – Product...CVP – ABC Company
The following information pertains to ABC Company and its product –
Product Z:
Selling Price per unit. $45.00
Direct Material Cost per kg $2.00
Direct Labour Cost per unit $1.20
Variable Overhead cost per unit $0.80
Material required per unit. 2KGS
Other variable expenses per unit. $0.60
Annual Fixed Costs:
Advertising. $15,000
Fixed Manufacturing. $60,000
Other Fixed Expenses. $8,000
Required:
What is the Breakeven Point in both units and sales dollars? For
this you will need...
A Company produces two products. Relevant information for each product is shown in the Table below....A Company produces two products. Relevant information for each
product is shown in the Table below. The company has a goal of $48
in profits and incurs $1 penalty for each dollar it falls short of
this goal. A total of 32 hours of labor are available. A $2 penalty
is incurred for each hour of overtime (labor over 32 hours) used,
and $1 penalty is incurred for each hour of available labor that is
unused. Marketing considerations require at...
Grainger Company produces only one product and sells that product for $110 per unit. Cost information...Grainger Company produces only one product and sells that
product for $110 per unit. Cost information for the product is as
follows:
Direct Material
$16
per Unit
Direct Labor
$26
per Unit
Variable Overhead
$5
per Unit
Fixed Overhead
$33,500
Selling expenses are $4 per unit and are all variable.
Administrative expenses of $20,000 are all fixed. Grainger produced
5,000 units; sold 4,000; and had no beginning inventory.
A. Compute net income under
i. Absorption Costing $
ii. Variable Costing...
Grainger Company produces only one product and sells that product for $110 per unit. Cost information...Grainger Company produces only one product and sells that
product for $110 per unit. Cost information for the product is as
follows:
Direct Material
$14
per Unit
Direct Labor
$25
per Unit
Variable Overhead
$4
per Unit
Fixed Overhead
$27,200
Selling expenses are $4 per unit and are all variable.
Administrative expenses of $16,000 are all fixed. Grainger produced
4,000 units; sold 3,200; and had no beginning inventory.
A. Compute net income under
i. Absorption Costing $. ??
ii. Variable Costing...
Grainger Company produces only one product and sells that product for $110 per unit. Cost information...Grainger Company produces only one product and sells that
product for $110 per unit. Cost information for the product is as
follows:
Direct Material
$16
per Unit
Direct Labor
$24
per Unit
Variable Overhead
$6
per Unit
Fixed Overhead
$26,800
Selling expenses are $3 per unit and are all variable.
Administrative expenses of $12,000 are all fixed. Grainger produced
4,000 units; sold 3,200; and had no beginning inventory.
A. Compute net income under
i. Absorption Costing $
ii. Variable Costing...
Grainger Company produces only one product and sells that product for $100 per unit. Cost information...Grainger Company produces only one product and sells that
product for $100 per unit. Cost information for the product is as
follows:
Direct Material
$14
per Unit
Direct Labor
$24
per Unit
Variable Overhead
$4
per Unit
Fixed Overhead
$34,000
Selling expenses are $3 per unit and are all variable.
Administrative expenses of $15,000 are all fixed. Grainger produced
5,000 units; sold 4,000; and had no beginning inventory.
A. Compute net income under
i. Absorption Costing
ii. Variable Costing ...
The Central Valley Company is a manufacturing firm that produces and sells a single product. The...The Central Valley Company is a manufacturing firm that produces
and sells a single product. The company’s revenues and expenses for
the last four months are given below.
Central Valley Company
Comparative Monthly Income Statements
March
April
May
June
Sales in units
6,800
6,300
7,050
7,300
Sales revenue
$
829,600
$
768,600
$
860,100
$
890,600
Less: Cost of goods sold
418,500
393,000
426,250
444,000
Gross margin
411,100
375,600
433,850
446,600
Less: Operating expenses
Shipping expense
76,000
71,000
79,250
80,000...
The Central Valley Company is a manufacturing firm that produces and sells a single product. The...
The
Central Valley Company is a manufacturing firm that produces and
sells a single product. The company’s revenues and expenses for the
last four months are given below.
Central Valley Company
Comparative Income Statement
March
April
May
June
Sales in units
5,800
5,300
6,450
7,600
Sales revenue
$
742,400
$
678,400
$
825,600
$
972,800
Less: Cost of goods sold
392,200
366,336
429,312
496,128
Gross margin
$
350,200
$
312,064
$
396,288
$
476,672
Less: Operating Expenses
Shipping expense...
The Central Valley Company is a manufacturing firm that produces and sells a single product. The...The Central Valley Company is a manufacturing firm that produces
and sells a single product. The company’s revenues and expenses for
the last four months are given below. Central Valley Company
Comparative Income Statement March April May June Sales in units
6,900 6,400 8,100 9,800 Sales revenue $ 793,500 $ 736,000 $ 931,500
$ 1,127,000 Less: Cost of goods sold 421,350 397,440 484,380
574,770 Gross margin $ 372,150 $ 338,560 $ 447,120 $ 552,230 Less:
Operating Expenses Shipping expense $...
Novak Company sells one product. Presented below is information for January for Novak Company. Jan. 1...Novak Company sells one product. Presented below is information
for January for Novak Company.
Jan. 1
Inventory
125
units at $4 each
4
Sale
104
units at $8 each
11
Purchase
158
units at $6 each
13
Sale
130
units at $9 each
20
Purchase
149
units at $6 each
27
Sale
87
units at $11 each
Novak uses the FIFO cost flow assumption. All purchases and sales
are on account.
Assume Novak uses a periodic system. Prepare all necessary...
ADVERTISEMENT
ADVERTISEMENT
Latest Questions
ADVERTISEMENT
|