Question

S&P Enterprises has provided data from the first three months of the year. The Controller has asked you to prepare the Cash Budget and the related Schedules for Expected cash collections and Payments to suppliers. Use the information included in the Excel Simulation and the Excel functions described below to complete the task.

• Cell Reference: Allows you to refer to data from another cell in the worksheet. From the Excel Simulation below, if in a blank cell on the Budget Data tab, “=B5” was entered, the formula would output the result from cell B5, or 1,600 in this example.

• Multi-Tab Cell Reference: Allows you to refer to data from another cell in a separate tab in the worksheet. When using the multi-tab cell reference, type the equal sign first, then click on the other tab and then click on the cell you want to reference. The syntax of a multi-tab cell reference looks different then a normal cell reference, since it includes the tab name surrounded by apostrophes and also an exclamation point before the cell location. From the Excel Simulation below, if in a blank cell on the Schedules and Cash Budget tab “=’ Budget Data’!C7” was entered, the formula would output the result from cell C7 in the Budget Data tab, or 33,750 in this example.

• Basic Math functions: Allows you to use the basic math symbols to perform mathematical functions. You can use the following keys: + (plus sign to add), - (minus sign to subtract), * (asterisk sign to multiply), and / (forward slash to divide). From the Excel Simulation below, if in a blank cell on the Budget Data tab, “=B15+B16” was entered, the formula would add the values from those cells and output the result, or 34,000 in this example. If using the other math symbols the result would output an appropriate answer for its function.

• SUM function: Allows you to refer to multiple cells and adds all the values. You can add individual cell references or ranges to utilize this function. From the Excel Simulation below, if in a blank cell “=SUM(B10,B11,B12)” was entered, the formula would output the result of adding those three separate cells, or 97% in this example. Similarly, if in a blank cell “=SUM(B10:B12)” was entered, the formula would output the same result of adding those cells, except they are expressed as a range in the formula, and the result would be 97% in this example.

please help me I need excel formulas and answers for all of the yellow cells. thank you

Answer 1

Answer 2

Please hit LIKE button if this helped. For any further explanation, please put your query in comment, will get back to you.
		Jan	Feb	Mar
Cash Sale		$    1,600	$    3,750	$    5,100
Sales on Account		$ 25,000	$ 30,000	$ 40,000
Total Sale		$ 26,600	$ 33,750	$ 45,100
Accounts Receivable Collection:
Month of Sale		15%
Month of Following sale		60%
Secondo month following sale		22%
Uncollectible		3%
Accounts Payable for inventory purchase, Mar 1		$ 10,500
Budgeted inventory Purchase in March		$ 23,500
Inventory Payments:
Month of Purchase		60%
Month of following purchase		40%
Total Budgeted selling and admin expense in March		$ 12,500
Budgeted selling and admin Dep in March		$    3,200
Minimum Cash		$ 10,000
March 1 cash balance		$ 11,500
1. Schedule of expected cash collection for March
		March
March Cash Sale		$    5,100
March Credit Sale		$    6,000
Feb Sale		$ 18,000
Jan Sale		$    5,500
Expected Cash Collection in March		$ 34,600
2. Schedule of expected cash disbursment for Inventory:
		March
Accounts Payable, March 1		$ 10,500
March Purchase		$ 14,100
Expected Cash disbursment for Inventory purchase		$ 24,600
3. Cash Budget
		March
Beginning Balance		$ 11,500
Add: Cash Collection		$ 34,600
Total Cash Available		$ 46,100
Cash Disbursment for:
Inventory		$ 24,600
Selling and Admin Expense		$    9,300
Total cash Payment		$ 33,900
Excess/(Deficiency)		$ 12,200
Borrowing/Repayment		$          -
Ending Balance		$ 12,200