Pebbles has a question, if net income is projected to be $885,830, why has the ending cash balance gone from $120,000 at the beginning of the year to $1,273,960 by the end of 2016? She is sure you have screwed something up. To prove you are correct, complete a cash flow statement ? indirect method to show why cash increased so much

FINANCIAL MODELING PROJECT: FALL 2015 ACTG 352 - COST ACCOUNTING BOULDER BRICK, INC KEY: NAMES: Hannah Ford Sarah Wolf Orange = Green = Rose = Blue = Hard-coded item with a formula to include Blue Cells Formula required Hard-coded item put in as "0%" - these cells will be used for analysis Michael Pavasko INPUT INFORMATION - 2016 Master Budget Sales Data: Budgeted Sales Volume Quarter 1 2,500,000 Quarterly Sales Volume Sensitivity Budgeted Sales Price $ Overall Sales Price Sensitivity Quarter 4 2,500,000 0.00% Quarter 1 0.75 $ Quarter 2 0.75 $ Quarter 3 0.80 $ Quarter 4 0.80 Quarter 2 4,000,000 Quarter 3 2,000,000 Quarter 4 2,000,000 Quarter 2 600,000 Quarter 3 100,000 Quarter 4 100,000 6.00 pounds $ Direct Material Price Sensitivity 0.05000 0.00% Quarter 1 4,000,000 Desired ending inventory (in lbs.) Ending inventory at December 31, 2015 (in lbs.) 2,000,000 Finished Goods Inventory Info: Desired bricks in ending inventory Quarter 1 600,000 Ending inventory at December 31, 2015 (in bricks) 100,000 Direct Labor: Direct labor hours per brick DLH per brick sensitivity Direct labor rate per hour DLH per hour sensitivity Quarter 3 8,500,000 0.00% Direct Materials data: Direct material required per brick (in lbs.) Cost per lb. of direct material Quarter 2 9,500,000 0.01500 DLHs per brick 0.00% $ 14.00 0.00% $ 6.00 $ $ 80,000 200,000 280,000 $ 0.03 $ $ $ $ 30,000 20,000 2,000 3,000 Research & Development Expenses (per quarter): Salaries Prototype design & development $ $ 20,000 20,000 Administrative Expenses (per quarter): Salaries Insurance Depreciation Travel Quarter 1 Quarter 2 Quarter 3 Quarter 4 $ 50,000 $ 50,000 $ 50,000 $ 50,000 $ 20,000 $ 10,000 $ 10,000 $ 10,000 $ 10,000 $ 2,000 $ 2,000 $ 2,000 $ 2,000 Manufacturing Overhead: Variable Variable overhead rate per DLH Fixed Fixed overhead other than depreciation per quarter Fixed overhead depreciation per quarter Total fixed overhead Selling, General & Administrative: Sales and Marketing Expenses Variable marketing expense per brick SOLD Fixed Expenses (per quarter): Salaries Advertising Depreciation Travel Cash Information: Cash collections Percentage of sales collected in cash Percentage of sales on credit 50% 50% Total 23,000,000 Credit Sales Collection percentages Percent collected in the quarter of the sale Percent collected in the quarter following sale 70% 30% Cash payments: Material purchases Percent paid in the quarter of the sale Percent paid in the quarter following sale Taxes: 80% 20% (Paid in the fourth quarter) Tax rate 40% All other expenses paid in the quarter incurred Minimum cash balance required at end of quarter $ Interest rate on financing Capital Acquisitions Equipment purchases (1st Quarter) 8% $ Balance Sheet (December 31, 2015) ASSETS Current Assets: Cash $ Accounts receivable Materials inventory (2,000,000 lbs x $0.04 per lb) Finished goods inventory (100,000 bricks at $0.55/brick) Total current assets Property, Plant & Equipment Land Buidlings and equipment Accumulated depreciation Total PP&E TOTAL ASSETS LIABILITIES AND STOCKHOLDERS' EQUITY Current liabilities Accounts payable Line of credit, short-term Stockholders' equity Common stock, no par Retained earnings TOTAL LIABILITIES AND STOCKHOLDERS' EQUITY 100,000 600,000 120,000 300,000 80,000 55,000 $ 555,000 $ 7,000,000 7,555,000 2,500,000 9,000,000 (4,500,000) $ $ 100,000 - 600,000 6,855,000 $ 7,455,000 7,555,000 FINANCIAL MODELING PROJECT: FALL 2015 a. Sales Budget - DOLLARS Sales in Units (bricks) Sales price per unit (bricks) Total Sales Revenue Quarter 1 Quarter 2 Quarter 3 Quarter 4 2,500,000 9,500,000 8,500,000 2,500,000 $ 0.75 $ 0.75 $ 0.80 $ 0.80 $ 1,875,000 $ 7,125,000 $ 6,800,000 $ 2,000,000 $ b. Cash Receipts Budget Cash sales Credit Sales Collections in the current quarter Collections in the quarter following sale Total cash receipts Quarter 1 Quarter 2 Quarter 3 Quarter 4 $ 937,500 $ 3,562,500 $ 3,400,000 $ 1,000,000 $ $ 937,500 656,250 300,000 1,893,750 $ 3,562,500 2,493,750 281,250 6,337,500 $ 3,400,000 2,380,000 1,068,750 6,848,750 $ 1,000,000 700,000 1,020,000 2,720,000 $ Year 17,800,000 Year 8,900,000 8,900,000 6,230,000 2,670,000 A/R - Dec 31, 2016 17,800,000 $ 300,000 c. Production Budget - in bricks Quarter 1 2,500,000 600,000 3,100,000 100,000 3,000,000 Sales in units (bricks) Add: Desired ending inv of FG units (bricks) Total units required Less: expected beg inv of FG units (bricks) Units (bricks) to be produced Quarter 2 9,500,000 600,000 10,100,000 600,000 9,500,000 Quarter 3 8,500,000 100,000 8,600,000 600,000 8,000,000 Quarter 4 2,500,000 100,000 2,600,000 100,000 2,500,000 Year 23,000,000 d. Direct-Material Budget Units (bricks) to be produced Raw material required per brick (lbs per brick) Raw material required for production Add: Desired ending inventory of raw material Total raw materials required Less: Expected beg inv of raw material Raw material to be purchased Cost per pound of raw material Total cost of raw material purchases e. Cash Payments for Direct Material Payments in the current quarter Payments in the quarter after purchase Total cash payments for direct material f. Quarter 1 Quarter 2 Quarter 3 Quarter 4 3,000,000 9,500,000 8,000,000 2,500,000 6 6 6 6 18,000,000 57,000,000 48,000,000 15,000,000 4,000,000 4,000,000 2,000,000 2,000,000 22,000,000 61,000,000 50,000,000 17,000,000 2,000,000 4,000,000 4,000,000 2,000,000 20,000,000 57,000,000 46,000,000 15,000,000 $ 0.05 $ 0.05 $ 0.05 $ 0.05 $ 1,000,000 $ 2,850,000 $ 2,300,000 $ 750,000 $ $ $ 800,000 $ 100,000 900,000 $ 2,280,000 $ 200,000 2,480,000 $ 1,840,000 $ 570,000 2,410,000 $ 600,000 $ 460,000 1,060,000 $ Year 6,900,000 5,520,000 1,330,000 A/P - Dec 31, 2016 6,850,000 $ 150,000 Direct-Labor Budget - DOLLARS Units (bricks) to be produced Direct labor hours required per brick (unit) Budgeted direct labor hours Direct labor cost per hour Total direct labor costs Quarter 1 Quarter 2 Quarter 3 Quarter 4 3,000,000 9,500,000 8,000,000 2,500,000 0.015 0.015 0.015 0.015 45,000 142,500 120,000 37,500 $ 14.00 $ 14.00 $ 14.00 $ 14.00 $ $ 630,000 $ 1,995,000 $ 1,680,000 $ 525,000 $ Year 345,000 14.00 4,830,000 g. Manufacturing-Overhead Budget - DOLLARS Quarter 1 Budgeted direct labor hours Variable overhead rate per DLH Budgeted variable overhead Budgeted fixed overhead - total Total budgeted overhead h. SG&A Expense Budget Marketing expenses: Variable Portion Fixed Portion: Salaries Advertising Depreciation Travel TOTAL MARKETING EXPENSES Research and development expenses Salaries Prototype design & development TOTAL RESEARCH AND DEVELOPMENT EXP $ $ 45,000 6.00 $ 270,000 280,000 550,000 $ Quarter 1 Quarter 2 142,500 6.00 $ 855,000 280,000 1,135,000 $ Quarter 2 Quarter 3 120,000 6.00 $ 720,000 280,000 1,000,000 $ Quarter 3 Quarter 4 37,500 6.00 225,000 280,000 505,000 $ Quarter 4 Year 2,070,000 1,120,000 3,190,000 Year $ 75,000 $ 285,000 $ 255,000 $ 75,000 $ 690,000 $ 30,000 20,000 2,000 3,000 130,000 $ 30,000 20,000 2,000 3,000 340,000 $ 30,000 20,000 2,000 3,000 310,000 $ 30,000 20,000 2,000 3,000 130,000 $ 120,000 80,000 8,000 12,000 910,000 $ 20,000 20,000 40,000 $ 20,000 20,000 40,000 $ 20,000 20,000 40,000 $ 20,000 20,000 40,000 $ 80,000 80,000 160,000 Administrative Expenses Salaries Insurance Depreciation Travel TOTAL ADMINISTRATIVE EXPENSES i. 50,000 $ 50,000 10,000 2,000 62,000 $ 10,000 2,000 62,000 $ 50,000 20,000 10,000 2,000 82,000 $ 50,000 10,000 2,000 62,000 $ 200,000 20,000 40,000 8,000 268,000 Budgeted Schedule of Cost of Goods Manufactured and COGS (including Cost per brick computation) Direct materials Quarter 1 Quarter 2 Quarter 3 Quarter 4 Raw material inventory, beginning $ 80,000 $ 200,000 $ 200,000 $ 100,000 $ Add: Purchases of raw materials 1,000,000 2,850,000 2,300,000 750,000 Raw material available for use 1,080,000 3,050,000 2,500,000 850,000 Deduct: raw-material inventory, ending 200,000 200,000 100,000 100,000 Direct material used 880,000 2,850,000 2,400,000 750,000 Direct labor 630,000 1,995,000 1,680,000 525,000 Manufacturing overhead 550,000 1,135,000 1,000,000 505,000 Cost of goods manufactured 2,060,000 5,980,000 5,080,000 1,780,000 Add: Finished goods inventory, beginning 55,000 389,217 389,217 64,870 Cost of goods available for sale 2,115,000 6,369,217 5,469,217 1,844,870 Less: Finished goods, ending (use unit cost below) 389,217 389,217 64,870 64,870 Cost of goods sold $ 1,725,783 $ 5,980,000 $ 5,404,348 $ 1,780,000 $ Unit Cost Computation: (For ending finished goods inventory) Direct material per brick: $ 0.3000 Direct labor per brick 0.2100 Overhead Variable overhead per brick 0.0900 Fixed overhead per brick 0.0487 COST PER brick $ 0.6487 Year 80,000 6,900,000 6,980,000 100,000 6,880,000 4,830,000 3,190,000 14,900,000 55,000 14,955,000 64,870 COGS Check 14,890,130 $ 14,890,130 The computation at left is critical to determining your value for ending finished goods inventory and ultimately Cost of Goods sold FINANCIAL MODELING PROJECT: FALL 2015 j. Income Statement Boulder Bricks, Inc. Income Statement For the quarters and year ended December 31, 2016 Sales Less: Cost of goods sold Gross Profit Selling, General & Administrative expenses Marketing expenses Research & Development expenses Administrative expenses Operating Income Less: Interest expense (will calculate for you) Income before income taxes Income taxes Net income k. Quarter 1 Quarter 2 Quarter 3 Quarter 4 $ 1,875,000 $ 7,125,000 $ 6,800,000 $ 2,000,000 $ 1,725,783 5,980,000 5,404,348 1,780,000 149,217 1,145,000 1,395,652 220,000 $ 130,000 40,000 62,000 (82,783) 15,725 (98,508) (98,508) $ 340,000 40,000 62,000 703,000 6,090 696,911 278,764 418,146 $ 310,000 40,000 82,000 963,652 963,652 385,461 578,191 $ 130,000 40,000 62,000 (12,000) (12,000) (12,000) $ Year 17,800,000 14,890,130 2,909,870 910,000 160,000 268,000 1,571,870 21,815 1,550,055 664,225 N.I. Check 885,830 $ 885,830 Cash Budget Boulder Bricks, Inc. Cash Budget For the quarters and year ended December 31, 2016 Quarter 1 Quarter 2 Quarter 3 Quarter 4 $ 120,000 $ 100,000 $ 100,000 $ 1,049,421 $ 1,893,750 6,337,500 6,848,750 2,720,000 2,013,750 6,437,500 6,948,750 3,769,421 Beginning cash balance Add: Budgeted cash receipts Total cash available Less: disbursements Direct materials Direct labor Manufacturing Overhead Marketing expenses R&D expenses Administrative expenses Income taxes Interest paid Capital Acquisitions Total disbursements Minimum cash balance Total cash needs Excess (deficiency) of cash available over needs Financing: (don't touch, will calculate) Borrowings Repayments Total financing Plus: minimum cash balance Ending cash balance $ Line of credit balance (will calculate for you) l. $ 900,000 630,000 350,000 128,000 40,000 52,000 2,480,000 1,995,000 935,000 338,000 40,000 52,000 15,725 1,060,000 525,000 305,000 128,000 40,000 52,000 385,461 - 5,855,725 100,000 5,955,725 481,775 600,000 2,700,000 100,000 2,800,000 (786,250) 2,410,000 1,680,000 800,000 308,000 40,000 72,000 278,764 6,090 5,594,854 100,000 5,694,854 1,253,896 2,495,461 100,000 2,595,461 1,173,960 786,250 786,250 100,000 100,000 $ (481,775) (481,775) 100,000 100,000 $ 786,250 $ 304,475 $ (304,475) (304,475) 100,000 1,049,421 $ - $ 6,850,000 4,830,000 2,390,000 902,000 160,000 228,000 664,225 21,815 600,000 16,646,040 100,000 16,746,040 1,173,960 100,000 1,273,960 $ - Year 120,000 17,800,000 17,920,000 $ 786,250 (786,250) 100,000 Cash Bal Check 1,273,960 $ 1,273,960 - Balance Sheet Boulder Bricks, Inc. Budgeted Balance Sheet December 31, 2016 ASSETS Current Assets: Cash Accounts receivable Materials inventory Finished goods inventory Total current assets Property, Plant & Equipment Land Buidlings and equipment Accumulated depreciation Total PP&E $ 1,273,960 300,000 100,000 64,870 $ 1,738,830 2,500,000 9,600,000 (5,348,000) 6,752,000 Total Assets Check Figure TOTAL ASSETS LIABILITIES AND STOCKHOLDERS' EQUITY Current liabilities Accounts payable Interest Payable Line of credit, short-term Stockholders' equity Common stock, no par Retained earnings $ $ $ 8,490,830 150,000 - 600,000 7,740,830 TOTAL LIABILITIES AND STOCKHOLDERS' EQUITY $ In Balance? 8,340,830 8,490,830 YES!!! If the above says $0, then you're still in Balance. Rounding Issue $ 8,490,830 FINANCIAL MODELING PROJECT: FALL 2015 PART 2: WHAT-IF ANALYSIS WRITE-UP SHEET. Complete this sheet as part of your project submission. Required: ANSWER PART 2 QUESTIONS FROM THE FINANCIAL MODELING PROJECT BELOW. Click on the text box below the question to write in your solution/recommendations. Expand the text box if needed. Be sure the type clearly, professionally, and grammatically correct, without spelling errors. Spelling errors and poor grammar will be penalized. 1. Answer below: No, increasing the sales volume each quarter did not help increase the income to 1 million dollars. The increase of 2% sales voiume increased the income from $885,830 to $931,358, but it is not the 1 millions that Fred wants. 2. Answer below: The quarterly sales volume would have to increase by 5.19%. 3. Answer below: 4. Answer below: 5. Answer below: 6. Answer below