Background:

As the owner of a local CPA firm, you have been hired to help a new small business,

Thunderduck Construction. The company has been in business for one year and reports

financial information on a quarterly basis. The company uses the accrual-basis method of

accounting and follows US GAAP.

Budget Assignment - Part 1 (75 points)

• Create a quarterly budgeted income statement for Year 2 based on prior year data and

discussions with management, using the excel template provided.

• Prepare a quarterly cash budget (including a schedule of cash receipts and disbursements)

for Year 2. Use the separate tab created in the excel template provided.

Use the following assumptions for the budgeted income statement in Year 2 based on historical

results and discussions with management:

New construction and Remodel revenue	- Calculate the average for Year 1. This will be the amount of revenue for Q1 and Q4 of Year 2. - Q2 and Q3 of Year 2 will be 15% higher than the Year 1 average due to high demand for homes in the spring.
Cost of goods sold	- 61% of total revenue in Q1 and Q3 in Year 2. - 59% of total revenue in Q2 and Q4 in Year 2.
Salaries expense	- Mixed expense with a fixed portion of $4,600 per quarter for Q1, Q2, Q3. The variable portion is $5 per project. - Fixed portion will be 15% higher in Q4 due to year-end bonuses. Variable will be the same as $5 per project. - 22 projects expected in Q1 and Q2 in Year 2. - 32 projects expected in Q3 and Q4 in Year 2.
Marketing expense	- Equal to the same quarter in Year 1.
Legal expense	- Calculate the average for Year 1. This will be the amount for every quarter in Year 2.
Business development expense	- $3,100 in Q1 and Q2 of Year 2. - Due to an increase in projects, increase to $4,000 in Q3 and Q4 of Year 2.
Depreciation expense	- $675 in Q1 and Q2 of Year 2. - Will increase to $825 in Q3 and Q4 of Year 2 due to new equipment.
Office supplies expense	- Calculate the average for Year 1. This will be the amount for Q1 and Q4 of Year 2. - Q2 and Q3 of Year 2 will be 25% higher with expected increased business.
Miscellaneous expense	- 4% of COGS each quarter of Year 2.

Use the following assumptions for the cash budget based on historical results and

discussions with management:

New construction and Remodel revenue	All sales on made on account and have the following collection pattern: - 55% will be collected in the quarter the sale is made - 35% will be collected in the quarter following the sale - 5% will be collected two quarters following the sale
Cost of goods sold	All purchases of cost of goods sold are on account and have the following payment pattern: - 50% of purchases are paid for in the quarter of purchase - The remainder are paid in the following quarter
Selling and administrative expenses	- Cash is paid for all expenses in the quarter they are incurred except for depreciation expense (which is a non-cash expense).
Purchase of equipment	- Equipment costing $13,400 was purchased at the beginning of Q3 of Year 2.
Borrow from Bank	- The company signed a promissory note to borrow $14,600 at the beginning of Q2 of Year 2. - Interest of $180 is due at the end of each quarter, beginning in Q3 of Year 2. - No loans will be repaid in Year 2.
Beginning cash balance	- Started Year 2 with a $10,800 cash balance.

File Share Comments A X Home Insert Verdana [G BI U Page Layout 10 = AA Formulas == ab 3 Data Review Accounting $ % View Help QuickBooks Conditional Formatting Insert Format as Table 2. Delete Cell Styles Format Editing Ideas Sensitivity Clipboard Font Alignment Number Styles Cells Ideas Sensitivity B43 X for =0.04*B34 F G H Thunderduck Construction Income Statement Actual Quarterly for Year 1 8 Year 1 Actual Provided: Q1 Year 1 Q2 Year 1 Q3 Year 1 24 Year 1 9 New construction revenue 35,605 42,790 48,326 43,098 10 Remodel revenue 24,568 29,482 35,378 31,133 11 Total revenue 60,173 72,272 83,704 74,231 12 Less: Cost of goods sold 39,112 46,977 53,571 47,508 13 Gross profit 21,061 25,295 30,133 26,723 14 Less: Selling and administrative expenses 15 Salaries expense 3,400 3,650 3,900 4,400 16 Marketing expense 1,568 2,250 2,646 1,624 17 Legal expense 3,009 3,614 4,185 3,712 18 Business development expense 2,600 2,600 3,520 3,520 19 Depreciation expense 600 700 750 750 20 Office supplies expense 447 870 1,242 793 21 Miscellaneous expense 1,956 2,349 2,679 2,375 22 Total selling and admin expenses 13,579 16,032 18,922 17,174 23 Net operating income 7,481 9,263 11,212 9,549 Thunderduck Construction Income Statement Budget Quarterly for Year 2 30 Year 2 Budget: 01 Year 2 02 Year 2 Q3 Year 204 Year 2 31 New construction revenue 42,455 48,823 48,823 42,455 32 Remodel revenue 30,140 34,661 34,661 30,140 33 Total revenue 72,595 83,484 83,484 72,595 34 Less: Cost of goods sold 44,283 49,256 50,925 42,831 35 Gross profit 28,312 34,228 32,559 29,764 36 Less: Selling and administrative expenses 37 Salaries expense 4,710 4,710 4,760 5,450 38 Marketing expense 1,568 2,250 2,646 1,624 39 Legal expense 3,630 3,630 3,630 3,630 40 Business development expense 3,100 3,100 4,000 4,000 41 Depreciation expense 675 675 825 825 42 Office supplies expense 838 1,048 1,048 838 43 Miscellaneous expense 1,771 ! 1,970 2,037 1,713 44 Total selling and admin expenses 16,292 17,382 18,945 18,080 45 Net operating income 12,020 16,846 13,614 11,684 46 47 48 Budgeted Income Statement Cash Budget ... 1 Ready @ J- - + 100% File Review Share Comments X Insert Verdana BIU 3. Font 10 Page Layout Formulas 10 - = = = abe A A 3 A Alignment Data General $ % View Help QuickBooks Conditional Formatting Insert Format as Table 9X Delete Cell Styles Format Styles Cells Paste Editing Ideas Sensitivity Clipboard Number Ideas Sensitivity A28 1 Part 1 2 In 000's C DE D Thunderduck Construction Cash Budget Quarterly for Year 2 4 01 Year 2 Q2 Year 203 Year 2 04 Year 2 Year 2 6 Cash balance, beginning 7 Plus: Cash receipts 8 9 Cash available for use 10 Less: Cash disbursements 11 12 13 14 Excess or (Deficiency) of cash 15 Financing: 16 17 18 Cash balance, ending 19 20 Cash Budget - Schedule of Cash Receipts 21 22 01 Year 2 Q2 Year 2 Q3 Year 2 04 Year 2 Year 2 23 cash sale 33,095 39,749 46,037 40,827 159,708 24 Collection from credit sale( quarter following the sale) 21,061 25,295 29,296 75,652 25 Collection from credit sale( two quarter following the sale) 3,009 3,614 6,622 26 Total cash collected 33,095 60,810 74,341 73,737 241,983 27 28 Cash Budget - Schedule of Cash Disbursements 29 30 31 Inventory Purchase 01 Year 2 02 Year 2 Q3 Year 2 04 Year 2 13,400 Year 2 32 33 Total cash paid 34 35 43 44 45 Budgeted Income Statement Cash Budget ... + Ready @ - + 100%