Team Project #2 (20 points) Budgeting Directions: Each team will be responsible for completing the case detailed at the end of these instructions. The project needs to be done using a spreadsheet program. Each team will submit the finished product via Canvas as an upload by midnight on the due date. There is an Excel template on my website that you can use to complete the project. It will save you loads of time. The project due date is noted on your class timeline. Note: You need to use formulas and cell references whenever possible. If you do not use formulas or cell references, I will deduct points. Part 1a. This part requires you to create a sales budget by month and the total for the 2nd quarter. Use formulas wherever possible. Part 1b. This part requires you to create a schedule for budgeted cash collections from sales and accounts receivable. Use formulas wherever possible and link information from Part la. Part 1c. This part requires you to create a purchases budget in units and dollars. Note: this company is a merchandiser, so no production budget is needed. Instead, a purchases budget will be used (since the company will be buying inventory instead of manufacturing it). Obviously, you will not need any direct materials, direct labor etc. budgets. Use formulas wherever possible. Link information wherever possible. Part 1d. This part requires you to create a cash disbursements budget for purchases by month and the total for the quarter. Use formulas wherever possible and link information. Part 2. This part requires you to create a cash budget by month and the total for the quarter. Use formulas wherever possible and link information. Also use formulas when you calculate numbers that aren't given, like interest expense. Use formulas whenever you calculate numbers (i.e. interest expense). Part 3. This part requires you to create a budgeted income statement from the previous parts and additional information given in the case. Use formulas wherever possible, including any derived numbers (ie. COGS, commissions). Use formulas whenever you calculate numbers. Part 4. This part requires you to create a budgeted balance sheet from the previous parts and additional information given in the case. Use formulas wherever possible, including any derived numbers (i.e. unexpired insurance, fixed assets, net of depreciation, retained earnings, dividends payable, inventory). Use formulas whenever you calculate numbers. $ 14,000 Assets Cash Accounts receivable ($19,440 February sales; S158,760 March sales) Inventory (24,750 units) Prepaid insurance Fixed assets, net of depreciation 178,200 120,037.50 14,400 172,700 Total assets $ 499,337.50 Liabilities and Stockholders' Equity Accounts payable $ Dividends payable Capital stock Retained earnings 76,993.75 12,000 300,000 110,343.75 Total liabilities and stockholders' equity $ 499,337.50 The company has an agreement with a bank that allows it to borrow in increments of $1,000 at the beginning of each month, up to a total loan balance of $300,000. The interest rate on these loans is 1% per month, and for simplicity, we will assume that interest is not compounded. At he end of the quarter, the company would pay the bank all of the accumulated interest on the loan and as much of the loan as possible in increments of $1,000), while still retaining at least $12,000 in cash.. CRAVAT SALES COMPANY SARE ERS F April May June Quarter 33,000 S 8.10 $ S 267 300'S Correct! 41,000 8.10 332 100 91a. Sales budget: 10 Budgeted sales in units 11 Selling price per unit 12 Total sales 13 14 1b. Schedule of expected cash collections: 15 February sales 16 March sales 17 April sales 18 May sales 19 June sales 20 Total cash collections 22 1c. Merchandise purchases budget: 23 Budgeted sales in units 24 Add budgeted ending inventory 25 Total needs 26 Less beginning inventory 27 Required unit purchases 28 Unit cost 29 Required dollar purchases 30 31 1d. Budgeted cash disbursements for inventory purchases: 32 March purchases 33 April purchases 34 May purchases 35 June purchases 36 Total cash payments 38 CRAVAT SALES COMPANY Cash Budget For the Three Months Ending June 30 April May June Quarter 45 Cash balance, beginning 46 Add receipts from customers 47 Total cash available 48 Less disbursements: 49 Purchase of inventory 50 Sales commissions 51 Salaries and wages 52 Utilities 53 Miscellaneous 54 Dividends paid 55 Land purchases 56 Total disbursements 57 Excess (deficiency) of receipts over disbursements 59 Financing 60 Borrowings Repayments 62 Interest 63 Total financing 64 Cash balance, ending CRAVAT SALES COMPANY Budgeted Income Statement For the Three Months Ended June 30 72 Sales in units 73 Sales 74 Variable expenses. 75 Cost of goods sold 76 Commissions 77 Contribution margin 78 Fixed expenses 79 Salaries and wages 80 Utilities 81 Insurance expired 82 Depreciation 83 Miscellaneous 84 Net operating income 85 Less interest expense 86 Net income CRAVAT SALES COMPANY Budgeted Balance Sheet June 30 Assets 95 Cash 96 Accounts receivable 97 Inventory 98 Unexpired insurance 99 Fixed assets, net of depreciation 100 Total assets 101 102 Liabilities and Stockholders' Equity 103 Accounts payable, purchases 104 Dividends payable 105 Loans payable, bank 106 Capital stock, no par 107 Retained earnings 108 Total abilities and equity 109 110 Accounts receivable at June 30 111 May sales 112 June sales 113 Total 114 115 Retained earnings at June 30: 116 Balance, March 31 117 Add net income 118 Total 119 Less dividends declared 120 Balance, June 30 121