PROJECT REQUIREMENTS

A - INCOME STATEMENT

1. Prepare a budgeted income statement through operating income by quarter and for the year using the contribution margin format--use the format provided on the template as a reference. Then add interest expense to your income statement to determine income before tax. Interest expense can be calculated from the information provided for the cash budget. (For purposes of this project, we will ignore income taxes.)
2. Using your budgeted income statement for the year, answer the following questions at the bottom of your income statement sheet. You should use operating income in your calculations, not income before tax. Label your answers, but make sure that your answers contain formulas incorporating cell references.

• What is the budgeted break-even point in units? (Round to the nearest whole unit)
• What is the margin of safety percentage at the budgeted activity level for the year? (Round to the nearest one percent)
• What is operating leverage at the budgeted activity level for the year? (Round to one decimal place)
• If Crossley wants to earn a $6,500,000 operating profit next year, how many units must the company sell? (Round to the nearest whole unit)

B - CASH BUDGET

Given the following additional information, prepare a cash budget by quarter and for the year using the format provided. The first quarter has been done for youyou should make sure that you understand where these amounts come from before attempting the remaining quarters.

ADDITIONAL INFORMATION:

• Historically, 65% of sales have been collected in the quarter of sale and 35% in the following quarter; uncollectible accounts have been insignificant. Accounts receivable at 1/1/19 is expected to total $3,069,360.
• Crossley pays for materials in the quarter after purchase and accounts payable at 1/1/19 is projected to be $2,600,000.
• Crossley pays all other variable product costs, including direct labor, in the quarter in which they are incurred.
• Fixed operating costs are paid in the quarter incurred; sales commissions are paid in the quarter following the sale, and commissions payable at 1/1/19 is estimated at $187,425.
• Beginning cash $1,625 ,000
• Crossley plans to pay quarterly dividends of $250,000
• Crossley is planning to purchase additional production equipment requiring a cash outlay of $1,950,000 at the beginning of the third quarter.
• Crossley has a $1,500,000 8% note outstanding on which it will make payments of $200,000 principal plus interest on the outstanding balance on June 30 and December 31.

C - BALANCE SHEET

NOTE: If you cannot get your balance sheet to balance, add a line item in the appropriate section titled Plug for the amount needed.

Presented on the template provided is Crossleys projected balance sheet at 12/31/18. Prepare a budgeted balance sheet at 12/31/19 next to it, then answer the following questions in the space below your balance sheets. Label your answers, but the amounts themselves should be calculated by formulas that include cell references:

• What is the budgeted return on investment (round your answer to the nearest one percent)?
• If the company has established a minimum required rate of return of 22% for Crossley, what is budgeted residual income (round your answer to the nearest dollar)?
• If the CEO of Crossley has set a target ROI of 28%, how many units would the company have to sell for the year (round your answer to the nearest whole unit)?

Given the following additional information, prepare a cash budget by quarter and for the year using the format provided. The first quarter has been done for youyou should make sure that you understand where these amounts come from before attempting the remaining quarters.

Here is the Operating Budget that was provided -

Also, already have the Income Statement completed -

I need the Cash Budget and the Balance Sheet (If you can, please show your work and formulas) -

G H 2 3 4 2018 SALES: 2019 SALES 2020 SALES SALES PRICE: 170000 204000 244800 $ 210 6 SALES BUDGET 7 9 QTR1 QTR 2 QTR3 QTR4 YR 406006 1200 71400 30600 204000 210 210 210 8568000 12852000 14994000 6426000 42840000 210 8 UNITS SALES PRICE 10 Budgeted revenue 11 12 PRODUCTION BUDGET 13 Budgeted sales 14 +El required 15 Total needs 16 -BI on hand 17 Total production required 406006 1200 6120 7140 46920 68340 3440 6120 43480 62220 71400 3060 74460 7140 67320 30600 4896 35496 3060 32436 204000 4896 208896 Q1-2020 Q2-2020 48960 73440 7344 56304 4896 51408 205456 51408 10 514080 19 MATERIALS PURCHASES BUDGET 20 Budgeted production 21 Std feet per unit 22 Total feet for production 23 +El required 24 Total feet needed 25 -B1 Total feet to be purchased 27 $5/ft 28 Total cost of purchases 15% 43480 10 434800 93330 528130 55000 473130 62220 10 622200 100980 723180 93330 629850 5 3149250 67320 10 673200 48654 721854 100980 620874 5 3104370 32436 10 324360 77112 401472 48654 352818 2054560 77112 2131672 55000 2076672 26 2365650 1764090 10383360 29 30 DIRECT LABOR BUDGET 31 Production 32 Std hours per unit 33 Total labor hours needed 34 $9.00/DLH Taal Operating Budgets 43480 62220 67320 6 6 6 260880 373320 403920 9 9 9 Income Statement Cash Budget 32436 205456 6 6 194616 1232736 9 9 Balance Sheet + 24 Total feet needed 25 -BI 26 Total feet to be purchased 27 $5/t 28 Total cost of purchases 528130 55000 473130 723180 93330 629850 721854 100980 620874 401472 2131672 48654 55000 352818 2076672 2365650 3149250 3104370 1764090 10383360 29 43480 62220 67320 32436 205456 260880 373320 403920 194616 1232736 2347920 3359880 3635280 1751544 11094624 30 DIRECT LABOR BUDGET 31 Production 32 Std hours per unit 33 Total labor hours needed $9.00/DLH 35 Total cost of direct labor 36 37 OVERHEAD BUDGET: 38 Total labor hours needed 39 $6.00/DLH 40 Total variable overhead 41 Fixed overhead-depreciation 42 Fixed overhead-other 43 Total budgeted overhead 44 45 SELLING & ADMIN BUDGET 46 Variable selling-commissions 47 Fixed selling 48 Fixed admin 49 Depreciation-selling 50 Depreciation-admin 51 Total budgeted selling & admin cost 52 260880 6 1565280 90000 524000 2179280 373320 6 2239920 90000 524000 2853920 403920 6 2423520 122500 524000 3070020 194616 1232736 6 6 1167696 7396416 122500 425000 524000 2096000 1814196 9917416 3.5096 299880 850000 980000 75000 120000 2324880 449820 850000 980000 75000 120000 2474820 524790 850000 980000 75000 120000 2549790 224910 850000 980000 75000 120000 2249910 1499400 3400000 3920000 300000 480000 9599400 53 55 56 57 mm Operating Budgets Income Statement Cash Budget Balance Sheet + A budgeted Income statement GTR1 GTR2 QTR 3 QTR 4 YEAR Sales revenue 8568000 12852000 24994000 642600042840000 LESS. Variable costs Direct materials 20400003060000 3570000 153000020200000 Direct labor 2347920 3559880 3635280 1751544 11294624 Variable overhead 1565280 2239920 2423520 1167696 7396416 Variable selling 299880 449820 524790 224910 2499400 Total variable costs 62530809309620 10153590 4674150 30390440 Contribution margin 2314920 3542380 4840410 1751850 12449560 LESS: Fixed costs Fixed overhead-depreciation 90000 90000 122500 122500 425000 Fixed overhead-other 524000 524000 524000 24000 2096000 Fixed selling-depreciation 75000 75000 75000 75000 300000 Fixed selling-other 850000 850000 850000 850000 3400000 Fixed admin-depreciation 120000 120000 120000 120000 480000 Fixed admin-other 980000 980000 980000 980000 3920000 Total fixed costs 2639000 2639000 2671500 267150010621000 Operating Income -324080 903380 2168910 -919650 1828560 Less: Interest Expense Profit before taxes -324080 903380 2158910 -919650 1828560 Direct Material cost GTR1 40800 10 QTR 2 6.200 10 GTR 3 71400 10 QTR 4 30600 10 YEAR 204000 10 Budgeted sales units * Std Feet per unit * $5 per feet Direct Material cost 2 0400003060000 3570000 153000020200000 B C D H E F G CROSSLEY COMPANY CASH BUDGET YEAR ENDED DECEMBER 31, 2019 WN OTR 1 5569200 3069360 8638560 6 Cash Receipts-operating: 7 Current quarter 8 Previous quarter 9 Total cash collections 10 11 Cash payments-operating: 12 Materials purchases 13 Labor 14 Variable overhead 15 Variable sales commissions 16 Fixed overhead 17 Fixed selling 18 Fixed admin 19 Total operating payments 20 Total cash provided/ 21 used by operations 22 Other payments/receipts: 23 Capital expenditure 24 Dividends 25 Note payment (p&l) 26 Total cash provided/used 27 Add: beginning cash balance 28 Ending cash balance 29 30 2600000 2347920 1565280 187425 524000 850000 980000 9054625 -416065 -250000 -666065 1625000 958935 CROSSLEY COMPANY BALANCE SHEET AT DECEMBER 31 2019 2018 ASSETS 1625000 3069360 Cash Accounts receivable Inventory Raw materials Finished goods Property, plant and equipment, net Total Assets 275000 481600 756600 2939555 8220515 LIABILITIES AND STOCKHOLDERS' EQUITY Accounts payable Commissions payable Note payable Common stock, no par Retained earnings Total Liabilities and Stockholders' Equity 2600000 187425 1500000 2450000 1653090 8290515