acct 606

profileQ1114523292
Acct606FinalProject.xlsx
Home>Business & Finance homework help>Accounting homework help>acct 606

Data

SECTION ONE: DATA FOR BUDGETS
Sales Data: January Feburary March April May
Sales Units 293720 278947 263052 283317 273132
Selling price per unit $ 12.00 $ 12.00 $ 12.60 $ 12.60 $ 12.60
Percentage of sales collected in the month of sales 75% 75% 75% 75% 75%
Percentage of sales collected in the following month 25% 25% 25% 25% 25%
Variable Expenses:
Pounds of material needed per unit of Alpha 5 5 5 5 5
Price of material per pound $ 0.80 $ 0.90 $ 1.00 $ 1.10 $ 1.20
Direct labor hours needed per unit of Alpha 0.5 0.5 0.5 0.5 0.5
Direct labor rate per hour $ 10 $ 10 $ 10 $ 10 $ 10
Variable manufacturing overhead per unit of Alpha $ 0.50 $ 0.50 $ 0.50 $ 0.50 $ 0.50
Variable operating expenses per unit of Alpha
Sales Commission $ 0.80 $ 0.80 $ 0.80 $ 0.80 $ 0.80
Shipping and Handling $ 0.60 $ 0.60 $ 0.60 $ 0.60 $ 0.60
Fixed Expense:
Fixed manufacturing overhead $ 25,000 $ 25,000 $ 25,000 $ 25,000 $ 25,000
Depreciation portion of fixed overhead $ 3,000 $ 3,000 $ 3,000 $ 3,000 $ 3,000
Fixed operating expenses $ 12,000 $ 12,000 $ 12,000 $ 12,000 $ 12,000
Depreciation portion of fixed operating expenses $ 1,500 $ 1,500 $ 1,500 $ 1,500 $ 1,500
Inventory Policy:
Desired ending inventory of Alpha (% of next month sales) 20% 20% 20% 20% 20%
Desired ending inventory of Raw Materials (% of next month's production needs) 25% 25% 25% 25% 25%
Other Information:
Capital Expenditures $ 400,000 $200,000
Loan Repayments & Interest Expense 1% 1% 1% 1% 1%
Income tax rate 30% 30% 30% 30% 30%
Minimum cash retained at the end of month $ 20,000 $ 20,000 $ 20,000 $ 20,000 $ 20,000
December 20X1 Sales dollars $ 4,350,000
The balance of cash on December 31 of the previous year $ 21,000

Budget

Section Two: Budgets
Sales Budget January Feburary March April May
Sales in Units 293720 278947 263052 283317 273132
Unit Selling Price $ 12.00 $ 12.00 $ 12.60 $ 12.60 $ 12.60
Sales in Dollars $ 3,524,640 $ 3,347,364 $ 3,314,455 $ 3,569,794 $ 3,441,463
Production Budget January Feburary March April May
Sales Units 293720 278947 263052 283317 273132
Add: Desired Ending Inventory 55789 52610 56663 54626 0
Total: Required Units 349509 331557 319715 337943 273132
Less: Beginning Inventory 58744 55789 52610 56663 54626
Required Production Units 290765 275768 267105 281280 218506
Direct Materials Budget (Purchases Budget) January Feburary March April May
Units to be Produced 290765 275768 267105 281280 218506
Direct Material Qty Required Per Unit of Alpha (pounds) 5 5 5 5 5
Total Direct Materials Needed for Prodution (pounds) 1453825 1378840 1335525 1406400 1092530
Add: Desired Ending Inventory Direct Materials 344710 333881 351600 273133 0
Total Direct Materials Needed 1798535 1712721 1687125 1679533 1092530
Less: Beginning Inventory of Direct Materials 363456 344710 333881 351600 273133
Direct Material Purchase (pounds) 1435079 1368011 1353244 1327933 819397
Cost Per Pound $ 0.80 $ 0.90 $ 1.00 $ 1.10 $ 1.20
Total Cost Of Direct Material Purchases $ 1,148,063 $ 1,231,210 $ 1,353,244 $ 1,460,726 $ 983,276
Cost of Production Budget (Usage Budget) January Feburary March April May
Units to be Produced 290765 275768 267105 281280 218506
Direct Material Costs $ 1,163,060 $ 1,240,956 $ 1,335,525 $ 1,547,040 $ 1,311,036
Driect Labor Costs $ 1,453,825 $ 1,378,840 $ 1,335,525 $ 1,406,400 $ 1,092,530
Variable Manufacturing Cost $ 145,383 $ 137,884 $ 133,553 $ 140,640 $ 109,253
Fixed Manufacturing Cost $ 25,000 $ 25,000 $ 25,000 $ 25,000 $ 25,000
Total Production Costs $ 2,787,268 $ 2,782,680 $ 2,829,603 $ 3,119,080 $ 2,537,819
Cost of Production Per Unit $ 9.59 $ 10.09 $ 10.59 $ 11.09 $ 11.61
Operation Expense Budget January Feburary March April May
Variable (Sales Commission/Shipping) $ 411,208 $ 390,526 $ 368,273 $ 396,644 $ 382,385
Fixed $ 12,000 $ 12,000 $ 12,000 $ 12,000 $ 12,000
Total Operating Expenses $ 399,208 $ 378,526 $ 356,273 $ 384,644 $ 370,385
Budgeted Income Statament January Feburary March April May
Sales($) $ 3,524,640 $ 3,347,364 $ 3,314,455 $ 3,569,794 $ 3,441,463
Cost of Goods Sold $ 2,816,775 $ 2,814,575 $ 2,785,721 $ 3,141,986 $ 3,171,063
Gross Profit $ 707,865 $ 532,789 $ 528,734 $ 427,808 $ 270,400
Operating Expenses $ 399,208 $ 378,526 $ 356,273 $ 384,644 $ 370,385
Income from Operations $ 308,657 $ 154,263 $ 172,461 $ 43,164 $ (99,985)
Interest Expense $ - 0 $ - 0 $ - 0 $ - 0 $ - 0
Income Taxes $ 92,597 $ 46,279 $ 51,738 $ 12,949 $ - 0
Net Income $ 216,060 $ 107,984 $ 120,723 $ 30,215 $ (99,985)
Cash Budget January Feburary March April May
Beginning Cash Balance $ 21,000 $ 68,404 $ 42,848 $ 90,697 $ 146,798
Add: Receipts
Current Month Sales $ 2,643,480 $ 2,510,523 $ 2,485,841 $ 2,677,346 $ 2,581,097
Prior Period Month Sales $ 1,087,500 $ 881,160 $ 836,841 $ 828,614 $ 892,449
Total Receipts $ 3,730,980 $ 3,391,683 $ 3,322,682 $ 3,505,960 $ 3,473,546
Total Cash Available $ 3,751,980 $ 3,460,087 $ 3,365,530 $ 3,596,657 $ 3,620,344
Less: Disbursements
Direct Materials Purchases $ 1,148,063 $ 1,231,210 $ 1,353,244 $ 1,460,726 $ 983,276
Direct Labor $ 1,453,825 $ 1,378,840 $ 1,335,525 $ 1,406,400 $ 1,092,530
Fixed Manufacturing Overhead $ 22,000 $ 22,000 $ 22,000 $ 22,000 $ 22,000
Variable Manufacturing Overhead $ 145,383 $ 137,884 $ 133,553 $ 140,640 $ 109,253
Fixed Operating Expenses $ 10,500 $ 10,500 $ 10,500 $ 10,500 $ 10,500
Variable Operating Expenses $ 411,208 $ 390,526 $ 368,273 $ 396,644 $ 382,385
Income Taxes $ 92,597 $ 46,279 $ 51,738 $ 12,949 $ - 0
Capital Expenditures $ 400,000 $ 200,000 $ - 0 $ - 0 $ - 0
Loan Repayment & Interest Expense $ - 0 $ - 0 $ - 0 $ - 0 $ - 0
Total Disbursements $ 3,683,576 $ 3,417,239 $ 3,274,833 $ 3,449,859 $ 2,599,944
Excess (deficiency) of avaiable cash over disbursements $ 68,404 $ 42,848 $ 90,697 $ 146,798 $ 1,020,400
Financing/Borrowing $ - 0 $ - 0 $ - 0 $ - 0 $ - 0
Ending Cash Balance $ 68,404 $ 42,848 $ 90,697 $ 146,798 $ 1,020,400
Notes Payable $ - 0 $ - 0 $ - 0 $ - 0 $ - 0

Data-Rumors

SECTION ONE: DATA FOR BUDGETS
Sales Data: January Feburary March April May
Sales Units 293720 278947 263052 283317 273132
Selling price per unit $ 12.00 $ 12.00 $ 12.60 $ 12.60 $ 12.60
Percentage of sales collected in the month of sales 75% 75% 75% 75% 75%
Percentage of sales collected in the following month 25% 25% 25% 25% 25%
Variable Expenses:
Pounds of material needed per unit of Alpha 5 5 5 5 5
Price of material per pound $ 0.92 $ 1.17 $ 1.46 $ 1.79 $ 2.17
Direct labor hours needed per unit of Alpha 0.5 0.5 0.5 0.5 0.5
Direct labor rate per hour $ 11 $ 11 $ 11 $ 11 $ 11
Variable manufacturing overhead per unit of Alpha $ 0.50 $ 0.50 $ 0.50 $ 0.50 $ 0.50
Variable operating expenses per unit of Alpha
Sales Commission $ 0.80 $ 0.80 $ 0.80 $ 0.80 $ 0.80
Shipping and Handling $ 0.60 $ 0.60 $ 0.60 $ 0.60 $ 0.60
Fixed Expense:
Fixed manufacturing overhead $ 25,000 $ 25,000 $ 25,000 $ 25,000 $ 25,000
Depreciation portion of fixed overhead $ 3,000 $ 3,000 $ 3,000 $ 3,000 $ 3,000
Fixed operating expenses $ 12,000 $ 12,000 $ 12,000 $ 12,000 $ 12,000
Depreciation portion of fixed operating expenses $ 1,500 $ 1,500 $ 1,500 $ 1,500 $ 1,500
Inventory Policy:
Desired ending inventory of Alpha (% of next month sales) 20% 20% 20% 20% 20%
Desired ending inventory of Raw Materials (% of next month's production needs) 25% 25% 25% 25% 25%
Other Information:
Capital Expenditures $ 400,000 $200,000
Loan Repayments & Interest Expense 1% 1% 1% 1% 1%
Income tax rate 30% 30% 30% 30% 30%
Minimum cash retained at the end of month $ 20,000 $ 20,000 $ 20,000 $ 20,000 $ 20,000
December 20X1 Sales dollars $ 4,350,000
The balance of cash on December 31 of the previous year $ 21,000
Increase in Direct Materials 15%
Increase in Direct Labor 5%

Table1

Sales Units Advertising Expenditures Number of Dealers
248,000 19080 300
256,000 19440 337
264,000 19800 375
304,000 27000 312
320,000 30600 318
272,000 21600 303
296,000 28800 315
216,000 19800 225
232,000 19440 247
240,000 19620 270
268,000 23400 306
280,000 25200 309
Shiraz expects the following for the first five months of year 20X2:
January February March April May
Advertising Expenditures $28,000 $25,000 $27,000 $26,500 $25,800
No. Of Dealers 290 300 208 285 265

Budget-Rumors

Section Two: Budgets
Sales Budget January Feburary March April May
Sales in Units 293720 278947 263052 283317 273132
Unit Selling Price $ 12.00 $ 12.00 $ 12.60 $ 12.60 $ 12.60
Sales in Dollars $ 3,524,640 $ 3,347,364 $ 3,314,455 $ 3,569,794 $ 3,441,463
Production Budget January Feburary March April May
Sales Units 293720 278947 263052 283317 273132
Add: Desired Ending Inventory 55789 52610 56663 54626 0
Total: Required Units 349509 331557 319715 337943 273132
Less: Beginning Inventory 58744 55789 52610 56663 54626
Required Production Units 290765 275768 267105 281280 218506
Direct Materials Budget (Purchases Budget) January Feburary March April May
Units to be Produced 290765 275768 267105 281280 218506
Direct Material Qty Required Per Unit of Alpha (pounds) 5 5 5 5 5
Total Direct Materials Needed for Prodution (pounds) 1453825 1378840 1335525 1406400 1092530
Add: Desired Ending Inventory Direct Materials 344710 333881 351600 273133 0
Total Direct Materials Needed 1798535 1712721 1687125 1679533 1092530
Less: Beginning Inventory of Direct Materials 363456 344710 333881 351600 273133
Direct Material Purchase (pounds) 1435079 1368011 1353244 1327933 819397
Cost Per Pound 0.92 1.17 1.46 1.79 2.17
Total Cost Of Direct Material Purchases 1320273 1600573 1975736 2377000 1778091
Cost of Production Budget (Usage Budget) January Feburary March April May
Units to be Produced 290765 275768 267105 281280 218506
Direct Material Costs $ 1,337,519 $ 1,613,243 $ 1,949,867 $ 2,517,456 $ 2,370,790
Driect Labor Costs $ 1,453,825 $ 1,378,840 $ 1,335,525 $ 1,406,400 $ 1,092,530
Variable Manufacturing Cost $ 145,383 $ 137,884 $ 133,553 $ 140,640 $ 109,253
Fixed Manufacturing Cost $ 25,000 $ 25,000 $ 25,000 $ 25,000 $ 25,000
Total Production Costs $ 2,961,727 $ 3,154,967 $ 3,443,945 $ 4,089,496 $ 3,597,573
Cost of Production Per Unit $ 10.19 $ 11.44 $ 12.89 $ 14.54 $ 16.46
Operation Expense Budget January Feburary March April May
Variable (Sales Commission/Shipping) $ 411,208 $ 390,526 $ 368,273 $ 396,644 $ 382,385
Fixed $ 12,000 $ 12,000 $ 12,000 $ 12,000 $ 12,000
Total Operating Expenses $ 399,208 $ 378,526 $ 356,273 $ 384,644 $ 370,385
Budgeted Income Statament January Feburary March April May
Sales($) $ 3,524,640 $ 3,347,364 $ 3,314,455 $ 3,569,794 $ 3,441,463
Cost of Goods Sold $ 2,993,007 $ 3,191,154 $ 3,390,740 $ 4,119,429 $ 4,495,753
Gross Profit $ 531,633 $ 156,210 $ (76,285) $ (549,635) $ (1,054,290)
Operating Expenses $ 399,208 $ 378,526 $ 356,273 $ 384,644 $ 370,385
Income from Operations $ 132,425 $ (222,316) $ (432,558) $ (934,279) $ (1,424,675)
Interest Expense $ - 0 $ - 0 $ - 0 $ - 0 $ - 0
Income Taxes $ 39,728 $ - 0 $ - 0 $ - 0 $ - 0
Net Income $ 92,698 $ (222,316) $ (432,558) $ (934,279) $ (1,424,675)
Cash Budget January Feburary March April May
Beginning Cash Balance $ 21,000 $ (50,937) $ (399,576) $ (922,481) $ (1,769,705)
Add: Receipts
Current Month Sales $ 2,643,480 $ 2,510,523 $ 2,485,841 $ 2,677,346 $ 2,581,097
Prior Period Month Sales $ 1,087,500 $ 881,160 $ 836,841 $ 828,614 $ 892,449
Total Receipts $ 3,730,980 $ 3,391,683 $ 3,322,682 $ 3,505,960 $ 3,473,546
Total Cash Available $ 3,751,980 $ 3,340,747 $ 2,923,106 $ 2,583,479 $ 1,703,841
Less: Disbursements
Direct Materials Purchases $ 1,320,273 $ 1,600,573 $ 1,975,736 $ 2,377,000 $ 1,778,091
Direct Labor $ 1,453,825 $ 1,378,840 $ 1,335,525 $ 1,406,400 $ 1,092,530
Fixed Manufacturing Overhead $ 22,000 $ 22,000 $ 22,000 $ 22,000 $ 22,000
Variable Manufacturing Overhead $ 145,383 $ 137,884 $ 133,553 $ 140,640 $ 109,253
Fixed Operating Expenses $ 10,500 $ 10,500 $ 10,500 $ 10,500 $ 10,500
Variable Operating Expenses $ 411,208 $ 390,526 $ 368,273 $ 396,644 $ 382,385
Income Taxes $ 39,728 $ - 0 $ - 0 $ - 0 $ - 0
Capital Expenditures $ 400,000 $ 200,000 $ - 0 $ - 0 $ - 0
Loan Repayment & Interest Expense
Total Disbursements $ 3,802,917 $ 3,740,323 $ 3,845,587 $ 4,353,184 $ 3,394,759
Excess (deficiency) of avaiable cash over disbursements $ (50,937) $ (399,576) $ (922,481) $ (1,769,705) $ (1,690,918)
Financing/Borrowing
Ending Cash Balance $ (50,937) $ (399,576) $ (922,481) $ (1,769,705) $ (1,690,918)
Notes Payable
With the increase of direct material and labor costs, the company will not be able to last past the end of January.
Even if we borrow money, there isn't enough income to pay off the loan.
We suggest the company to increase its sales price per unit to help with the increase in costs.

Regression-2Varibales

SUMMARY OUTPUT
Regression Statistics
Multiple R 0.9738221948
R Square 0.9483296671
Adjusted R Square 0.9368473709
Standard Error 7672.9107923914
Observations 12
ANOVA
df SS MS F Significance F
Regression 2 9724804626.4147 4862402313.20735 82.5905943329 0.0000016203
Residual 9 529862040.251966 58873560.0279962
Total 11 10254666666.6667
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 40190.1232474975 19794.3363459151 2.0303849821 0.0728890809 -4587.7765002512 84968.0229952462 -4587.7765002512 84968.0229952462
Advertising Expenditures 5.9263760018 0.5805612764 10.208011183 0.0000030139 4.613055152 7.2396968516 4.613055152 7.2396968516
Number of Dealers 301.6851808837 60.6006045348 4.9782536527 0.000761256 164.5970892653 438.7732725021 164.5970892653 438.7732725021
Sales Forecast
Coefficients Y=a+b1x1+b2x2
Intercept 40190 a=40190
Advertising Expenditures 5.93 b1=5.93
Dealers 301.69 b2=301.69
x = # of dealers
Year 20X2 Expenditures Dealers Unit
January $ 28,000 290 293720
February $ 25,000 300 278947
March $ 27,000 208 263052
April $ 26,500 285 283317
May $ 25,800 265 273132

Regression-1Var#Dealers

SUMMARY OUTPUT
Regression Statistics
Multiple R 0.5916772646
R Square 0.3500819854
Adjusted R Square 0.285090184
Standard Error 25816.0659281638
Observations 12
ANOVA
df SS MS F Significance F
Regression 1 3589974066.59367 3589974066.59367 5.386556113 0.0427075643
Residual 10 6664692600.073 666469260.0073
Total 11 10254666666.6667
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 128319.046112666 59931.1254795651 2.1411085656 0.0579266085 -5215.8230116177 261853.915236949 -5215.8230116177 261853.915236949
Number of Dealers 457.8853875167 197.2882361127 2.3208955412 0.0427075643 18.2998035941 897.4709714393 18.2998035941 897.4709714393

Regression-1VarAdvertising

SUMMARY OUTPUT
Regression Statistics
Multiple R 0.897801058
R Square 0.8060467397
Adjusted R Square 0.7866514136
Standard Error 14102.9288931833
Observations 12
ANOVA
df SS MS F Significance F
Regression 1 8265740633.00482 8265740633.00482 41.5588136165 0.0000737964
Residual 10 1988926033.66185 198892603.366185
Total 11 10254666666.6667
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 114473.262999465 23905.7796636337 4.7885182835 0.0007362315 61207.866543899 167738.65945503 61207.866543899 167738.65945503
Advertising Expenditures 6.6561503543 1.0325035486 6.4466125691 0.0000737964 4.3555890828 8.9567116259 4.3555890828 8.9567116259