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 $5,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/18 is expected to total $3,069,360.
• Crossley pays for materials in the quarter after purchase and accounts payable at 1/1/18 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/18 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/17. Prepare a budgeted balance sheet at 12/31/18 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)?

Operating Budget Template:

Income Statement Template:

Cash budget template:

Balance Sheet Template:

Group Project ACC, 2302 [1] . Fxrrl Insert Page Layout Fomulas Data Review View Developer HelpTell me what you want to do File 2Aut A Calibri11AA B 1 u . . 2 . . . Wrap Text General Copy . Paste t as Cell armattingTable Styles insert Delete Format Sort & Find & Filter Select = =--Fmerge &Center . $ . % , conditional Forma Format Painter Cipboerd Clear Cling N46 12017 SALES: 2 2018 SALES 3 2019 SALES 4 SALES PRICE 170000 204000 244800 S 210 6 SALES BUDGET QTR1 QTR2 QTR3QTR4YR 8 UNITS 9 SALES PRICE 10 Budgeted revenue 40800 61200 71400 30600 204000 210 8568000 12852000 14994000 6426000 42840000 210 210 210 210 12 PRODUCTION BUDGET 13 Budgeted sales 14 +El required 15 Total needs 16 -Bl on hand 17 Total production required 18 19 MATERIALS PURCHASES BUDGET 20 Budgeted production 21 Std feet per unit 22 Total feet for production 23 +El required 4 Total feet needed 25 BI 26 Total feet to be purchasec Q1-2019 02-2019 48960 73440 40800 61200 71400 30500 204000 4895 46920 68340 74460 35496 208895 7140 3060 3440 43480 62220 67320 32436 205456 10% 6120 7140 3060 4896 7344 56304 4896 51408 6120 3440 51408 10 514080 43480 62220 67320 32435 205455 10 434800 522200 573200 324360 2054560 1596 93330 100980 48654 771 12 771 12 528130 723180 721854 401472 2131672 55000 93330 10098048654 55000 473130 629850 620874 352818 2076672 10 10 10 10 Operating Budgets Income Statement h Budget | Balance sheet l + 110% Ready Group Project ACC 2302 (1) xcrl View Developer Help Insert Page Layout Formulas Data Review Tell me what you want to do a Copy | Conditional Format as Cell Insert Deletr Fommat Sort &Find A ilter Select $-% , to . B 1 u-1-0. --= = E-Merge &Con ter formatting , Table , Styles , , , Edrng Clipheard Aignmeet N46 A. 26 Total feet to be purchasecd 27 S5vft 28 Total cost of purchases 29 30 DIRECT LABOR BUDGET 31 Production 32 Std hours per unit 33 Total labor hours needed 34 $9.00/DLH 35 Total cost of direct labor 36 37 OVERHEAD BUDGET: 3 Total labor hours needed 9 $6.0O DLH 40 Total variable overhead 41 Fixed overhead-depreciation 42 Flxed overhead-other 43 Total budgeted overhead 473130 629850 620874 352818 2076672 2365650 3149250 3104370 1764090 10383350 43480 62220 67320 32436 205456 260880 373320 403920 194616 1232736 2347920 3359880 3635280 1751544 11094624 260880 373320 403920 194516 1232736 1565280 2239920 2423520 1167696 7396416 90000 90000 22500 122500 425000 524000 524000 524000 524000 2096000 2179280 2853920 3070020 1814196 9917416 45 SELLING & ADMIN BUDGET 6 Variable selling-commissions 4 Fixed selling 48 FIxed admin 49 Depreciation-selling 50 Depreciation-admin 51 Total budgeted selling & admin cost 3.50% 299880 449820 524790 224910 1499400 850000 850000 850000 850000 3400000 980000 980000 980000 980000 3920000 75000 75000 75000 75000 300000 120000 120000 120000 120000 480000 2324880 2474820 2549790 2249910 9599400 Cash Budget | Balance Sheet . Operating Budgets Income Slalement DGolay settings Ready Grnup Prnject AcC. 2202 (1) Fcrl Insert Page Layout Formulas Data Review View Developer Help Tell me what you want to do Cut Copy Format Painter BIU. wrap Text Arial - Carditional Format as Cell ormattingTable Styles Insert Deletr Fomat Sort & Find ilter Select -Clear Edrng Clipheard Aignmeet F12 CROSSLEY COMPANY INCOME STATEMENT YEAR ENDED DECEMBER 31, 2018 QTR 1 QTR 2 QTR3 QTR 4 YEAR 7 Sales revenue 8 LESS: Variable costs 9 Direct materials 10 Direct labor 11 Variable overhead 12 Variable selling 13 Total variable costs 14 Contribution margin 15 LESS: Fixed costs 16 Fixed overhead-depreciation 17 Fixed overhead-other 18 Fixed selling-depreciation 19 Fixed selling-other 20 Fixed admin-depreciation 21 Fixed admin-other 22 Total fixed costs 23 Operating income 24 Operating Budgets Income Statement Cash Budget Balance Sheet + - Ready + 119% Group Pmject ACC 2302 () Fxoel Sign i Insert Page Layout Formulas Data Review View Developer HelpTell me what you want to do Arial a Copy as Cell otmalting TableStyles insert Delete Format Sort & Find & ilter Select B 1 u-1.0. - = = Merge & der ter- $-% , c Conditional Format D20 CROSSLEY COMPANY CASH BUDGET YEAR ENDED DECEMBER 31. 2018 4 QTR 1 QTR 2 QTR 3 QTR 4 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 5569200 3069360 8638560 2600000 2365650 2347920 3359880 1565280 2239920 187425 299880 524000 524000 850000 850000 980000 980000 9054625 10619330 -416065 Other navments/rereints Operating BudgetsIncome Statement Cash Budget Balance Sheet FI- Ready + 13-1% Group Project ACC 2302 (1) xcrl View Developer HelpTell me what you want to do Insert Page Layout Formulas Data Review Arial Fill Pas- Copy . Conditional Format as Celi ormattingTable Styles insert Delcte Format Sort & Find & Filter Select Merge & Center- $ . % , 4,0 4 1 u . . . .-= = ,Format Pinter Clipheard . Clear , a, 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 8638560 2600000 2365650 2347920 3359880 1565280 2239920 187425 299880 524000 524000 850000 850000 980000 980000 9054625 10619330 -416065 250000 666065 1625000 958935 Operating Budgets Income Statement Cash BudgetBalance s Dislav Settings F11- Ready roup Project AcC2301 Fxcel - Insert Page Layout ormulas Data Review View Developer Help Tell me what you want to do General Fill Copy 40 Cnnditional Farmat as Cell omng Table Styles Insert Delete Format Sort& Find & Filter Select Format Painter : B 1 u .. -Clear Cipbaard Edr ng BALANCE SHEET AT DECEMBER 31 2018 2017 ASSETS 8 Cash 9 Accounts receivable 10 Inventory 11 Raw materials 12 Finished goods 13 Property, plant and equipment, net 14 Total Assets 15 16 LIABILITIES AND STOCKHOLDERS' EQUITY 17 Accounts payable 18 Commissions payable 19 Note payable 20 Common stock, no par 21 Retained earnings 22 Total Liabilities and Stockholders' Equity 1625000 3069360 275000 756600 481600 2939555 2600000 187425 1500000 2450000 Operating Budgets Income Statement | Cash Budget Balance Sheet | Group Project ACC, 2302 [1] . Fxrrl Insert Page Layout Fomulas Data Review View Developer HelpTell me what you want to do File 2Aut A Calibri11AA B 1 u . . 2 . . . Wrap Text General Copy . Paste t as Cell armattingTable Styles insert Delete Format Sort & Find & Filter Select = =--Fmerge &Center . $ . % , conditional Forma Format Painter Cipboerd Clear Cling N46 12017 SALES: 2 2018 SALES 3 2019 SALES 4 SALES PRICE 170000 204000 244800 S 210 6 SALES BUDGET QTR1 QTR2 QTR3QTR4YR 8 UNITS 9 SALES PRICE 10 Budgeted revenue 40800 61200 71400 30600 204000 210 8568000 12852000 14994000 6426000 42840000 210 210 210 210 12 PRODUCTION BUDGET 13 Budgeted sales 14 +El required 15 Total needs 16 -Bl on hand 17 Total production required 18 19 MATERIALS PURCHASES BUDGET 20 Budgeted production 21 Std feet per unit 22 Total feet for production 23 +El required 4 Total feet needed 25 BI 26 Total feet to be purchasec Q1-2019 02-2019 48960 73440 40800 61200 71400 30500 204000 4895 46920 68340 74460 35496 208895 7140 3060 3440 43480 62220 67320 32436 205456 10% 6120 7140 3060 4896 7344 56304 4896 51408 6120 3440 51408 10 514080 43480 62220 67320 32435 205455 10 434800 522200 573200 324360 2054560 1596 93330 100980 48654 771 12 771 12 528130 723180 721854 401472 2131672 55000 93330 10098048654 55000 473130 629850 620874 352818 2076672 10 10 10 10 Operating Budgets Income Statement h Budget | Balance sheet l + 110% Ready Group Project ACC 2302 (1) xcrl View Developer Help Insert Page Layout Formulas Data Review Tell me what you want to do a Copy | Conditional Format as Cell Insert Deletr Fommat Sort &Find A ilter Select $-% , to . B 1 u-1-0. --= = E-Merge &Con ter formatting , Table , Styles , , , Edrng Clipheard Aignmeet N46 A. 26 Total feet to be purchasecd 27 S5vft 28 Total cost of purchases 29 30 DIRECT LABOR BUDGET 31 Production 32 Std hours per unit 33 Total labor hours needed 34 $9.00/DLH 35 Total cost of direct labor 36 37 OVERHEAD BUDGET: 3 Total labor hours needed 9 $6.0O DLH 40 Total variable overhead 41 Fixed overhead-depreciation 42 Flxed overhead-other 43 Total budgeted overhead 473130 629850 620874 352818 2076672 2365650 3149250 3104370 1764090 10383350 43480 62220 67320 32436 205456 260880 373320 403920 194616 1232736 2347920 3359880 3635280 1751544 11094624 260880 373320 403920 194516 1232736 1565280 2239920 2423520 1167696 7396416 90000 90000 22500 122500 425000 524000 524000 524000 524000 2096000 2179280 2853920 3070020 1814196 9917416 45 SELLING & ADMIN BUDGET 6 Variable selling-commissions 4 Fixed selling 48 FIxed admin 49 Depreciation-selling 50 Depreciation-admin 51 Total budgeted selling & admin cost 3.50% 299880 449820 524790 224910 1499400 850000 850000 850000 850000 3400000 980000 980000 980000 980000 3920000 75000 75000 75000 75000 300000 120000 120000 120000 120000 480000 2324880 2474820 2549790 2249910 9599400 Cash Budget | Balance Sheet . Operating Budgets Income Slalement DGolay settings Ready Grnup Prnject AcC. 2202 (1) Fcrl Insert Page Layout Formulas Data Review View Developer Help Tell me what you want to do Cut Copy Format Painter BIU. wrap Text Arial - Carditional Format as Cell ormattingTable Styles Insert Deletr Fomat Sort & Find ilter Select -Clear Edrng Clipheard Aignmeet F12 CROSSLEY COMPANY INCOME STATEMENT YEAR ENDED DECEMBER 31, 2018 QTR 1 QTR 2 QTR3 QTR 4 YEAR 7 Sales revenue 8 LESS: Variable costs 9 Direct materials 10 Direct labor 11 Variable overhead 12 Variable selling 13 Total variable costs 14 Contribution margin 15 LESS: Fixed costs 16 Fixed overhead-depreciation 17 Fixed overhead-other 18 Fixed selling-depreciation 19 Fixed selling-other 20 Fixed admin-depreciation 21 Fixed admin-other 22 Total fixed costs 23 Operating income 24 Operating Budgets Income Statement Cash Budget Balance Sheet + - Ready + 119% Group Pmject ACC 2302 () Fxoel Sign i Insert Page Layout Formulas Data Review View Developer HelpTell me what you want to do Arial a Copy as Cell otmalting TableStyles insert Delete Format Sort & Find & ilter Select B 1 u-1.0. - = = Merge & der ter- $-% , c Conditional Format D20 CROSSLEY COMPANY CASH BUDGET YEAR ENDED DECEMBER 31. 2018 4 QTR 1 QTR 2 QTR 3 QTR 4 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 5569200 3069360 8638560 2600000 2365650 2347920 3359880 1565280 2239920 187425 299880 524000 524000 850000 850000 980000 980000 9054625 10619330 -416065 Other navments/rereints Operating BudgetsIncome Statement Cash Budget Balance Sheet FI- Ready + 13-1% Group Project ACC 2302 (1) xcrl View Developer HelpTell me what you want to do Insert Page Layout Formulas Data Review Arial Fill Pas- Copy . Conditional Format as Celi ormattingTable Styles insert Delcte Format Sort & Find & Filter Select Merge & Center- $ . % , 4,0 4 1 u . . . .-= = ,Format Pinter Clipheard . Clear , a, 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 8638560 2600000 2365650 2347920 3359880 1565280 2239920 187425 299880 524000 524000 850000 850000 980000 980000 9054625 10619330 -416065 250000 666065 1625000 958935 Operating Budgets Income Statement Cash BudgetBalance s Dislav Settings F11- Ready roup Project AcC2301 Fxcel - Insert Page Layout ormulas Data Review View Developer Help Tell me what you want to do General Fill Copy 40 Cnnditional Farmat as Cell omng Table Styles Insert Delete Format Sort& Find & Filter Select Format Painter : B 1 u .. -Clear Cipbaard Edr ng BALANCE SHEET AT DECEMBER 31 2018 2017 ASSETS 8 Cash 9 Accounts receivable 10 Inventory 11 Raw materials 12 Finished goods 13 Property, plant and equipment, net 14 Total Assets 15 16 LIABILITIES AND STOCKHOLDERS' EQUITY 17 Accounts payable 18 Commissions payable 19 Note payable 20 Common stock, no par 21 Retained earnings 22 Total Liabilities and Stockholders' Equity 1625000 3069360 275000 756600 481600 2939555 2600000 187425 1500000 2450000 Operating Budgets Income Statement | Cash Budget Balance Sheet |