PLEASE help this is due by midnight
(CH.21 INVOLVED 2)
Create a master budgeted financial statements (Income statement , Statement of Retained Earnings and Balance Sheet) using a electronic spreadsheets such ad google sheets.
information provided in photos provided.
File Edit View insert format Data Tools Extensions Help Menus 100% View only - If. Storm Tocin. inc. Storm Tools has formed a new business unit to produce battery-powered drills. The business unit was formed by the transfer of selected assets and obligations from the parent company. The unit's initial balance sheet on January 1 contained cash ($500,000), plant and equipment ($2,500,000), notes payable to the parent ($1,000,000), and residual equity ($2,225,000). The business unit is expected to repay the note at $50,000 per month, plus all accrued interest at 1/2% per month. Payments are made on the last day of each month. The unit is scheduled to produce 25,000 drills during January, with an increase of 2,500 units per month for the next three months. Each drill requires $40 of raw materials. Raw materials are purchased on account, and paid in the month following the month of purchase. The plant manager has established a goal to end each month with raw materials on hand, sufficient to meet 25% of the following month's planned production. The unit expects to sell 20,000 drills in January; 25,000 in February, 25,000 in March, and 30,000 per month thereafter. The selling price is $100 per drill. Half of the drills will be sold for cash through a website. The others will be sold to retailers on account, who pay 40% in the month of purchase, and 60% in the following month. Uncollectible accounts are not material. Each drill requires 20 minutes of direct labor to assemble. Labor rates are $24 per hour. Variable factory overhead is applied at $9 per direct labor hour. The fixed factory overhead is $25,000 per month; 60% of this amount is related to depreciation of plant and equipment. With the exception of depreciation, all overhead is funded as incurred. Selling, general, and administrative costs are funded in cash as incurred, and consist of fixed components (salaries, $100,000; office, $40,000; and advertising, $75,000 ) and variable components (15\% of sales). Prepare a monthly comprehensive budget plan for Storm's new business unit for January through March. The plan should include the (a) sales and cash collections budget, (b) production budget, (c) direct materials purchases and payments budget, (d) direct labor budget, (e) factory overhead budget, (f) ending finished goods budget (assume total factory overhead is applied to production at the rate of $11.73 per direct labor hour), (g) SG\&A budget, and (h) cash budget. A JX. Storm Tools has formed a new business unit to produce battery-powered drills. The busine Storm Tools has formed a new business unit to produce battery-powered drills. The business unit was formed by the transfer of selected assets and obligations from the parent company. The unit's initial balance sheet on January 1 contained cash ($500,000), plant and equipment ($2,500,000), notes payable to the parent ($1,000,000), and residual equity ($2,000,000). The business unit is expected to repay the note at $50,000 per month, plus all accrued interest at 1/2% per month. Payments are made on the last day of each month. The unit is scheduled to produce 25,000 drills during January, with an increase of 2,500 units per month for the next three months. Each drill requires $40 of raw materials. Raw materials are purchased on account, and paid in the month following the month of purchase. The plant manager has established a goal to end each month with raw materials on hand, sufficient to meet 25% of the following month's planned production. The unit expects to sell 20,000 drills in January; 25,000 in February, 25,000 in March, and 30,000 per month thereafter. The selling price is $100 per drill. Half of the drills will be sold for cash through a website. The others will be sold to retailers on account, who pay 40% in the month of purchase, and 60% in the following month. Uncollectible accounts are not material. Each drill requires 20 minutes of direct labor to assemble. Labor rates are $24 per hour. Variable factory overhead is applied at $9 per direct labor hour. The fixed factory overhead is $25,000 per month; 60% of this amount is related to depreciation of plant and equipment. With the exception of depreciation, all overhead is funded as incurred. Selling, general, and administrative costs are funded in cash as incurred, and consist of foxed components (salaries, $100,000; office, $40,000; and advertising, $75,000 ) and variable components ( 15% of sales). Prepare a monthly comprehensive budget plan for Storm's new business unit for January through March. The plan should include the (a) sales and cash collections budget, (b) production budget, (c) direct materials purchases and payments budget, (d) direct labor budget, (e) factory overhead budget, (f) ending finished goods budget (assume total factory overhead is applied to production at the rate of $11.73 per direct labor hour), (g) SG\&A budget, and (h) cash budget. Q Menus 100% View only AtF1 fo. Stom Took, ine. 1 c o E Q H Sterm Tools, ine 2. Sules Collections Budres 9 4 b. a 7 \begin{tabular}{|c|c|c|c|c|c|} \hline & Jamuary & February & March & April & Quarter 1 \\ \hline Sales & $1875,000 & $3,125,000 & $3,125,000 & 53,750,000 & 8,125,000 \\ \hline sold foe can & $937,500 & $1,562,500 & $1,562,500 & $1,875,000 & $4,062,500 \\ \hline Sold on Credit (Acceunts receivable) & $937,500 & 51,562,500 & $1,562,500 & 51,875,000 & $,4,062,500 \\ \hline Carr collection month of sale & $375,000 & $625,000 & $625,000 & 5750,000 & $1,625,000 \\ \hline Cash colection sutsequent month & & $562,500 & $937,500 & 5937,500 & 1500000 \\ \hline Tatalcollection & $375,000 & $1,187,500 & 51,562,500 & 51,677,500 & $3,125,000 \\ \hline Accounts recelvable & 556,500 & 5937,500 & $337,500 & & $937,500 \\ \hline \end{tabular} \begin{tabular}{|c|c|c|} \hline & Jamuary & February \\ \hline Sales & $1875,000 & $3,125,000 \\ \hline Sold forcon & $937,500 & $1,562,500 \\ \hline Sold on Credit (Aceounts receivable) & $937,500 & 51,562,500 \\ \hline Casr collection month of sale & $375,000 & $625,000 \\ \hline Cash colection sutsequent month & & $562,500 \\ \hline Iatalcollection & $375,000 & $1,187,500 \\ \hline Accounts recelvable & 5567,500 & $937,500 \\ \hline \multicolumn{3}{|l|}{ Asumptions } \\ \hline \multicolumn{3}{|l|}{ Cakcultion } \\ \hline \multicolumn{3}{|l|}{ Sales Budiet } \\ \hline Sold for cash & 50.000 & \\ \hline Gredit sales collection per month & 40.00N & 6000s \\ \hline \end{tabular} 17 A c F I 18 10 20 21 27 21 24 21 20 21 24 2) 30 91 an 33 34 35 14 Problem Assumptions Sales Cash Collections File Edit View Insert Format Data Tools Extensions Help Menus 100% View only Storm Tools, inc. Units sold Sales price per unit Sold for cash Credit sales collection per month Beginning units Production FG units Monthy increase Units per Finished Good Cost of unit Beginning Required ending as $ of next month recuired Paid in month following purchase Prodution Budget 0 25,000 27,500 30,000 32,500 2,500 2,500 2,500 Direct Materials Budget March April 25.000 30,000 $125 Storm Tools, Ine. Assumptions Sales Budget January February 25,000 $125 60.00% $125 50.00x 40.00K 27,500 2,500 1 $45 0 25.00% Direct Labor Budget Direct Labor Budget Cash Collections File Edit View insert format Data Tools Extensions Help Menus 100% View only - If. Storm Tocin. inc. Storm Tools has formed a new business unit to produce battery-powered drills. The business unit was formed by the transfer of selected assets and obligations from the parent company. The unit's initial balance sheet on January 1 contained cash ($500,000), plant and equipment ($2,500,000), notes payable to the parent ($1,000,000), and residual equity ($2,225,000). The business unit is expected to repay the note at $50,000 per month, plus all accrued interest at 1/2% per month. Payments are made on the last day of each month. The unit is scheduled to produce 25,000 drills during January, with an increase of 2,500 units per month for the next three months. Each drill requires $40 of raw materials. Raw materials are purchased on account, and paid in the month following the month of purchase. The plant manager has established a goal to end each month with raw materials on hand, sufficient to meet 25% of the following month's planned production. The unit expects to sell 20,000 drills in January; 25,000 in February, 25,000 in March, and 30,000 per month thereafter. The selling price is $100 per drill. Half of the drills will be sold for cash through a website. The others will be sold to retailers on account, who pay 40% in the month of purchase, and 60% in the following month. Uncollectible accounts are not material. Each drill requires 20 minutes of direct labor to assemble. Labor rates are $24 per hour. Variable factory overhead is applied at $9 per direct labor hour. The fixed factory overhead is $25,000 per month; 60% of this amount is related to depreciation of plant and equipment. With the exception of depreciation, all overhead is funded as incurred. Selling, general, and administrative costs are funded in cash as incurred, and consist of fixed components (salaries, $100,000; office, $40,000; and advertising, $75,000 ) and variable components (15\% of sales). Prepare a monthly comprehensive budget plan for Storm's new business unit for January through March. The plan should include the (a) sales and cash collections budget, (b) production budget, (c) direct materials purchases and payments budget, (d) direct labor budget, (e) factory overhead budget, (f) ending finished goods budget (assume total factory overhead is applied to production at the rate of $11.73 per direct labor hour), (g) SG\&A budget, and (h) cash budget. A JX. Storm Tools has formed a new business unit to produce battery-powered drills. The busine Storm Tools has formed a new business unit to produce battery-powered drills. The business unit was formed by the transfer of selected assets and obligations from the parent company. The unit's initial balance sheet on January 1 contained cash ($500,000), plant and equipment ($2,500,000), notes payable to the parent ($1,000,000), and residual equity ($2,000,000). The business unit is expected to repay the note at $50,000 per month, plus all accrued interest at 1/2% per month. Payments are made on the last day of each month. The unit is scheduled to produce 25,000 drills during January, with an increase of 2,500 units per month for the next three months. Each drill requires $40 of raw materials. Raw materials are purchased on account, and paid in the month following the month of purchase. The plant manager has established a goal to end each month with raw materials on hand, sufficient to meet 25% of the following month's planned production. The unit expects to sell 20,000 drills in January; 25,000 in February, 25,000 in March, and 30,000 per month thereafter. The selling price is $100 per drill. Half of the drills will be sold for cash through a website. The others will be sold to retailers on account, who pay 40% in the month of purchase, and 60% in the following month. Uncollectible accounts are not material. Each drill requires 20 minutes of direct labor to assemble. Labor rates are $24 per hour. Variable factory overhead is applied at $9 per direct labor hour. The fixed factory overhead is $25,000 per month; 60% of this amount is related to depreciation of plant and equipment. With the exception of depreciation, all overhead is funded as incurred. Selling, general, and administrative costs are funded in cash as incurred, and consist of foxed components (salaries, $100,000; office, $40,000; and advertising, $75,000 ) and variable components ( 15% of sales). Prepare a monthly comprehensive budget plan for Storm's new business unit for January through March. The plan should include the (a) sales and cash collections budget, (b) production budget, (c) direct materials purchases and payments budget, (d) direct labor budget, (e) factory overhead budget, (f) ending finished goods budget (assume total factory overhead is applied to production at the rate of $11.73 per direct labor hour), (g) SG\&A budget, and (h) cash budget. Q Menus 100% View only AtF1 fo. Stom Took, ine. 1 c o E Q H Sterm Tools, ine 2. Sules Collections Budres 9 4 b. a 7 \begin{tabular}{|c|c|c|c|c|c|} \hline & Jamuary & February & March & April & Quarter 1 \\ \hline Sales & $1875,000 & $3,125,000 & $3,125,000 & 53,750,000 & 8,125,000 \\ \hline sold foe can & $937,500 & $1,562,500 & $1,562,500 & $1,875,000 & $4,062,500 \\ \hline Sold on Credit (Acceunts receivable) & $937,500 & 51,562,500 & $1,562,500 & 51,875,000 & $,4,062,500 \\ \hline Carr collection month of sale & $375,000 & $625,000 & $625,000 & 5750,000 & $1,625,000 \\ \hline Cash colection sutsequent month & & $562,500 & $937,500 & 5937,500 & 1500000 \\ \hline Tatalcollection & $375,000 & $1,187,500 & 51,562,500 & 51,677,500 & $3,125,000 \\ \hline Accounts recelvable & 556,500 & 5937,500 & $337,500 & & $937,500 \\ \hline \end{tabular} \begin{tabular}{|c|c|c|} \hline & Jamuary & February \\ \hline Sales & $1875,000 & $3,125,000 \\ \hline Sold forcon & $937,500 & $1,562,500 \\ \hline Sold on Credit (Aceounts receivable) & $937,500 & 51,562,500 \\ \hline Casr collection month of sale & $375,000 & $625,000 \\ \hline Cash colection sutsequent month & & $562,500 \\ \hline Iatalcollection & $375,000 & $1,187,500 \\ \hline Accounts recelvable & 5567,500 & $937,500 \\ \hline \multicolumn{3}{|l|}{ Asumptions } \\ \hline \multicolumn{3}{|l|}{ Cakcultion } \\ \hline \multicolumn{3}{|l|}{ Sales Budiet } \\ \hline Sold for cash & 50.000 & \\ \hline Gredit sales collection per month & 40.00N & 6000s \\ \hline \end{tabular} 17 A c F I 18 10 20 21 27 21 24 21 20 21 24 2) 30 91 an 33 34 35 14 Problem Assumptions Sales Cash Collections File Edit View Insert Format Data Tools Extensions Help Menus 100% View only Storm Tools, inc. Units sold Sales price per unit Sold for cash Credit sales collection per month Beginning units Production FG units Monthy increase Units per Finished Good Cost of unit Beginning Required ending as $ of next month recuired Paid in month following purchase Prodution Budget 0 25,000 27,500 30,000 32,500 2,500 2,500 2,500 Direct Materials Budget March April 25.000 30,000 $125 Storm Tools, Ine. Assumptions Sales Budget January February 25,000 $125 60.00% $125 50.00x 40.00K 27,500 2,500 1 $45 0 25.00% Direct Labor Budget Direct Labor Budget Cash Collections
