Budgeting

1) Due to unanticipated and recent entrants of fierce competition in the market, Bell company management has decided that the selling price must be reduced from $150 per unit to $135 per unit.

2) Also due to this unanticipated fierce competition, the anticipated quarterly sales growth rate has been lowered from 25% to 19%.

3) Recent union contract negotiations resulted in an unanticipated required increase in the hourly rate to paid to be paid to Direct Labor, from the originally projected $10.70 per hour, to $12.00 per hour.

4) Further, union negotiations demanded additional direct labor hours be allowed for producing finished goods- Direct Labor Hours allowed per unit increased from the originally projected 5.00 hours to 5.35 hours.

5) Our supplier of our key direct material notified us that due to the increased demand for these materials from the above mentioned competition, they will be significantly raising our DM price from the anticipated $5.25 per unit to $6.15 per unit.This increase is due to their need to incur costs to increase production capacity to accommodate this increased demand.Note that this supplier is the only reputable one for this material.

6) The only "good" news is the recently enacted Tax Cut and Jobs Act reduced the tax rate to 21%.

Due to these changes, both projected annual profitability and cash flows significantly decreased.

Management feels the current projected Income BEFORE Tax drop from the original projection of $285,145 to now $12,723 is unacceptable, as is the projected year end cash deficit of $127,841. Accordingly, management has charged your group with developing a modified budget that will result in projections for Bell company's annual Income BEFORE Tax to be at least $140,000 while ending the year with a minimum balance of cash of $20,000. Accomplishing this level of profitability is felt to be critical for purposes of attracting capital in the longer term.

Make assumption changes in at least three different areas, but your changes must realistically incorporate all implications for their implementation.

Assumptions NOT allowed to be changed:

Variable MO Rate of 2.00/DLH

Tax Rate of 21%

Dividends of $5,000 per quarter

Common Stock

All Q1 Beginning Balances

Selling Price can be Decreased, Not Increased

Equipment Purchases can be Increased, Not Decreased

Assume Bell Company will borrow, via a short term line of credit, $200,000 at the beginning of Quarter 1.This must be paid back at the end of Quarter 4 along with 5% interest.All loan and interest payments will occur in Quarter 4, and not earlier.

The report must contain the following:

1.Your Proposed Assumption Change Summary table completed for those assumptions changed- see attached

2.The Excel file containing the entire set of budgets updated with the changes from 1.

Group Proposed Assumption Changes Summary

Changeable

Items

Current Assumption

DM lbs/unit

2 lbs

DM Price/lb

$6.15

DL Hours/unit

5.35 hours

DL Rate/hour

$12.00

Total Fixed MO

$80,000

Var. S&A/unit

$3.21

Current Collect.

40%

Previous Collect.

60%

Current Pymt

50%

Previous Pymt

50%

DM End Inv

25%

FG End Inv

10%

Selling Price

$135.00¹

Qtr. Growth

19%

Total Equip. Purchases

$42,000²

Total Fixed S&A

$265,625

Proposed Assumption?

Explanation how proposed assumption will be implemented?

¹Can only be decreased

²Can only be increased

M N P R Other Assumptions by Quarter 01 13 04 Fixed S&A Expenses (no depreciation] |$ 60,000.00 $ 72,600.00 $ 63,000.00 70,025.00 Equipment Purchases $ 30,000.00 $ $ 12,000.00 Dividends Paid $ 5,000.00 5,000.00 5,000.00 5,000.00 Q1. Beg Cash Balance $ 19,000.00 Q1. Accts. Receivable Collected $ 100,000.00 Q1. DM Accts. Payable Paid $ 6,275.00 01. Beg. Net Property Plant Equipment | $ 206,000.00 Q1. Beg. Common Stock |$ 200,000.00 01 Bes. Retained Earnings 137.575.00 Financial Budgets Total Year Budgeted Income Statement Sales 1,071,480 COGS (767,655) Gross Profit 303,825 S&A Expenses Income Before Tax 291,102 12,723 21% | Taxes (2,672) Net Income 10.051 Cash Budgets Receipts Collections Current Qtr $ 81,000 96,390 04 $ 114,704 136,498 Total Year Collections Prior Qtr |$ 121,500 $ 428,592 100,000 144,585 172,056 $ Borrowing 538,141 200,000 Total Collections 181.DO0 217.890 259.289 308.554 966.733 Disbursements DM Pymt Current Otr $ 01 02 03 04 10,383 Total Year DM Pymt Prior Qtr $ 11,718 13,943 13,476 6.275 49,519 10.383 Total DM Payments. 11.718 13.943 16.658 42,318 22.100 25.660 27.419 91.837 DL 94,920 16,774 138,961 MO 31,820 515,968 35,462 165,313 39,160 S&A 43,552 64,815 78,330 149,995 Taxes $ 69,819 78,139 668 291,102 668 668 668 Equipment Purchases 2,672 30,000 Dividends $ 12,000 42,000 5,000 5,000 5,000 5.000 20,000 Loan 200,000 5% Interest 200,000 Total Disbursements 243.880 270.335 279.269 520 091 1.313.574 Cash Budget Summary Q1 22 Q4 Total Year 156,120 PLUS Beginning Balance |$ Q3 19,000 Total Collections 381,000 $ 103,675 83,695 $ 362,490 MINUS Total Disbursements 217,890 259,289 308,554 243.880 270.335 1,166,733 279.269 Ending Balance 156.120 520.091 103.675 83.695 1313,574 1127 841) (127 841) Quarter 4 Ending Budgeted Balance Sheet Original Values Assets: Cash (127,841) Accounts Receivable | $ (35,9971 204,747 DM Inventory $ 263,672 3,180 FG Inventory 29,016 2,726 24,932 Net Plant & Equipment 232.000 232,000 Total Assets 341.102 487,333 Liabilities and Equity: Accounts Payable |$ 13,476 Common Stock $ 12,928 200,000 Retained Earnings 200,000 127,626 274,405 Total Liabilities & Equity 341.102 487,333G Manufacturing Budgeting Exercise Master Budget Template General Assumptions DM Ibs/unit 2.00 DM Price/1b 6.15 DL hours/unit 5.35 DL Rate/hour 12.00 Var MO Rate 2.00 Fixed MO $ Ver S& A Bate /unit 20,000.00 (includes $4,000 depreciation) 3.21 Current Qur Collect 409% Previous Qur Colect 60% Current Qur DM Pyit 509% Previous Our DM Punt 500 OM End Inv. Policy FG End Inv. Policy 25% of following quarter's production need in Ibs Selling Price/unit $ 135.00 10% of following quarter's sales units Tax Rate 219% Quarterly Sales Growth 19% Operating Budgets Sales Forecast 01 02 03 Sales Units 1,500 94 $ 1,785 Total Year Sales Dollars 2,124 202,500 2528 240,975 7.937 286,760 $ 341,245 $ 1,071,480 Production Budget 02 Units Total Year Sales Need 1,500 PLUS Ending Inventory 1,785 2,124 2 528 7937 MINUS 179 212 253 Beginning Inventory 300 300 200 Production Need Units 179 212 1479 253 200 39 1819 2 165 2 575 40 41 DM Purchases Budget 42 43 44 Q1 Q2 45 Pounds (Ibs] 03 Q4 Total Year 46 Production Need 2,957 PLUS Ending Inventory 3,638 4.329 5,150 47 1,082 16,074 MINUS 909 Beginning Inventor 1,287 490 520 48 909 520 3.376 1.082 49 Purchases Need Ibs 4534 1287 490 1382 16.104 50 51 Purchases Need Dollars ($) |$ 20,765 $ 23,435 $ 27,886 $ 26,952 $ 99,038 52 53 DL Budget 54 55 Q1 02 56 DL Need Hours 7,910 9,731 04 Total Year 11,580 13,776 42,997 DL Need Dollars ($) $ 94,920 $ 116,774 $ 138,961 $ 165,313 $ 515,968 MO Budget Variable MO $ 91 92 03 04 15,820 Fixed MO 20,000 $ 19,462 Total Year 23,160 17,552 20,000 20,000 85,995 Total MO 20,000 35.820 80,000 39.462 13.160 17.552 65.995 S&A Expense Budget Variable S&A $ 01 92 03 4,815 94 Total Year Fixed S&A 60.000 5,730 $ 6,819 $ 8,114 Total S&A 72,600 25,477 63,000 64.815 78.330 70.025 69.819 265.625 78.139 291.102 Weighted Average Production Costs / Unit DM Pounds Total Cost /Unit Beginning Inventory 490 2,450 5.00 Annual Purchases 16.104 Total Available 99.038 16.594 6.15 101488 6.12 FG Units Total Cost /Unit Beginnging Inventory 200 16,400 Annual Production 82.00 8.037 Total Available 8 237 780.271 97.09 796.671 96.72 97