ACC 202 University of Illinois Managerial Accounting Cash Budget Worksheet

1. Prepare a sales budget, including a schedule of expected cash collections.
2. Prepare a direct materials budget, including a schedule of expected cash disbursements for purchases of materials.
3. Prepare a cash budget.

You have two excel sheets, Budget Data & Schedules & Cash Budget. You will be required to use data in the Budget Data sheet when developing formulas to create information in the Schedules & Cash Budget.

In order to use data in one sheet and transfer the result into another sheet, your formula will need to be modified to include the sheet reference.

Let’s assume you want to use the information in the Budget Data sheet (take B6*B12 in the Budget Data sheet and include the result in B4 in the Schedules & Cash Budget sheet) the formula to do that in cell B4 in the Schedules & Cash Budget sheet is:

=’Budget Data’!B6*’Budget Data’!B12.

If you don’t use the sheet reference, your answer will be marked wrong.

1 S&P Enterprises needs a cash budget for March. The following information is available.
2
3 Data
January February March
4 Actual January and February and expected March sales:
5 Cash sales
$ 1,600 $ 3,750 $ 5,100
6 Sales on account
25,000 30,000 40,000
7 Total Sales
$ 26,600 $ 33.750 $45.100
8
9 Accounts Receivable Collections:
10
Month of sale
15%
11 Month following sale
60%
12
Second month following sale
22%
13
Uncollectible
3%
14
15 Accounts payable for inventory purchases, March 1 balance
$10,500
16 Budgeted inventory purchases in March
$23,500
17 Inventory payments:
Month of purchase
60%
19 Month following purchase
40%
20
21 Total budgeted selling & administrative expenses in March
$12,500
22 Budgeted selling & administrative depreciation in March
$3,200
23
24 Other hudoeted cash dichursements in March
Budget Data Schedules and Cash Budget …
18
+
READY
间
+
100%
$12,500
$3,200
20
21 Total budgeted selling & administrative expenses in March
22 Budgeted selling & administrative depreciation in March
23
24 Other budgeted cash disbursements in March
25
Equipment purchases
26 Dividends to be paid
$14,000
$2,000
27
28 Minimum cash balance to be maintained
$10,000
29 March 1 cash balance
$11,500
30 March 1 outstanding borrowings
$0
31 March 1 interest due
$0
32
33 The company has a line of credit available to bolster the cash balance as needed.
34
35 When preparing budgets, the company maintains their data on a separate sheet from the actual budget and schedules.
36
37 Click the Schedules and Cash Budget tab to prepare the following:
38 1. Schedule of expected cash collections for March.
39 2. Schedule of expected cash disbursements for inventory purchases for March.
40
3. Cash budget for March. Indicate in the financing section any borrowing that will be needed in March. Assume that
41 any interest will not be paid until the following month.
42
43
Budget Data
Schedules and Cash Budget …
READY
+
100%
Attemptis)
Hint
Chả Ma
AS
?
El
x
Cash Budget with Supporting Cash Collections and Disbursements Schedules – Excel
INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW
FILE
HOME
Sign In
i
Calibri
– 11
Å
Paste
B
I u
А
%
Alignment Number Conditional Format as Cell
Formatting Table Styles
Styles
Cells
Editing
Clipboard
Font
< A1 fx Expected cash collections: A B с D E 4 1 Expected cash collections: 2 March cash collections 3 March collections on account: January sales 5 February sales 6 March sales 7 Total cash collections 8 9 Payments to suppliers: 10 Accounts payable for inventory purchases, beginning balance 11 March purchases 12 Total cash payments 13 S & P Enterprises Cash Budget For the Month of March 17 18 Cash balance, March 1 19 Add cash receipts: 20 Collections from customers 21 Total cash available 22 Less disbursements: 23 Payments to suppliers 24 Selling and administrative expenses 14 15 16 Calibri 11 1 A A = % IM Paste B I U .. Cells Editing Clipboard Font Alignment Number Conditional Format as Cell Formatting Table Styles Styles for Expected cash collections: < A1 A B с D E 18 Cash balance, March 1 19 Add cash receipts: 20 Collections from customers 21 Total cash available 22 Less disbursements: 23 Payments to suppliers 24 Selling and administrative expenses 25 Equipment purchases 26 Dividends paid 27 Total disbursements 28 Preliminary cash balance 29 Financing: 30 Additional loan 31 Loan repayments 32 Interest 33 Total financing 34 Cash balance, March 31 35 36 37 38 39 40 41 Budget Data Schedules and Cash Budget + 100% READY Attempt(s) Hint Show Me