INSTRUCTIONS: Part 1

Use formulas, cell referencing, rounding function and the information on the "Given Info" worksheet to complete the "Schedule of Expected Collections from Customers" and the "Schedule of Expected Payments for DM purchases".

Make sure that you cell reference the collection percentages. Do not hardcode a percentage (i.e. =60%*A1).

When you need a "$0", please make sure that you cell reference the appropriate cell on the Given Info tab. Do NOT hardcode the $0.

The orange cells require the use of the =ROUND function in addition to normal cell referencing. Round these cells to 0 decimal places.

INSTRUCTIONS: PART 2

The Cash Budget is Automatical completed as the Cash Collections and Cash Payments are completed.

Complete Cells N37 and O37 with IF Statements

Use the =IF function in cell N37 to display "Repay" or "Borrow" based on the data in cells K33 and K39.

Use the =IF function in cell O37 to display "Repay" or "Borrow" based on the data in cells L33 and L39.

You must use cell referencing as part of your =IF statements. If you hardcode numbers, your =IF statement will be incorrect.

Do not use K36, K37, L36, and/or L37 as part of your IF statement.

SHOW WORK IN EACH CELL!

Given info:

Given info continued:

Fill in blue and orange cells:

SHOW FORMULAS

C31X Derby Company prepares monthly cash budgets. Relevant data from operating budgets for 2014 are 4 5 Sales 6 Direct Material Purchases 7 Direct Labor 8 Manufacturing Overhead November December January February $ 201,300292,375 351,240 401,213 $ 101,100$91,776 $ 111,375$119,773 90,000 S 85,000 115,000NOTE: All items 80,0009 (except DM purchases) are completely paid in 62,000$ 65,000 60,00075,000 9 Selling and Administrative Expenses 5 83,000 5 85,000575,000 580,000the month incurred 10 Depreciation 11 Interest Received 12 Sale of Investment Securities 13 Purchase of Land $80,00080,000 $80,00080,000 $ 6,200 6,000 6,300 $6,100 5,000 $ 5,2003,000 2,000 $ 10,000 15,000 40,000 $ 40,000 14 Required Ending Cash Balance$60,000 15 16 Reference for "$0" 17 18 19 Month of sale 20 First Month Following the Sale 21 Second Month Following the Sale 60,000 50 Sales Collection Percentages 60% 25% 1596| 23 24 Month of purchase 25 Month following the purchase DM Payment Percentages 30%