90.2K

Verified Solution

Link Copied!

$1$ What SUP

$2$ Operating Cash Payments Budget

$\backslash$table$[[$Jan$,$Feb,Mar,Total$],[,,,],[150,000,165,000,175,000,490,000]]$

Purchases

Cost of expected sales

Required ending inventory

Beginning inventory

$(75,000)$

Purchases

Current monthly payments

Subsequent monthly payments

Cash payments for purchases

Expenses

Selling expenses

General and administrative expenses

$6,000$

$7,000$

$8,000$

$21,000$

$40,000,40,000,40,000,120,000$

Expenses

Operating cash payments

Assumptions

Beginning inventory

$75,000$

Required ending inventory

$50\%$

Cost of expected sales

$24$ Product sales expected in April

$180,000$

Sheet$1$

Sheet?

Sheet $3$

$2$ Create a new What SUP Operating Cash Payments Budget

Using the ch$6-03$ file to start your work, create a worksheet similar to the one created in this chapter to budget operating cash payments by month for $3$ months. Place assumption information in the cells provided, and define and use names extensively. Product sales revenue of $$150,000,$$$165,000,$$$175,000,$ and $$180,000$ is expected in January, February, March, and April respectively. Purchases cost $60$ percent of product sales. The company would like to maintain an ending inventory equal to $50$ percent of the next month's cost of sales. At the beginning of January, the company had $$75,000$ in inventory and $$15,000$ in accounts payable; $90$ percent of a month's purchases are paid in the current month, with the remaining paid in the following month. Selling expenses are expected to be $$6,000,$$$7,000,$ and $$8,000$ in January, February, and March $($respectively$),$ while general and administrative expenses are expected to remain constant at $$40,000$ per month. All expenses are paid in the month incurred. Use Excel's grouping feature to group purchases, payments for purchases, and expense rows together and to group the three monthly columns together. Save your file as ch$603\_$student$\_$name $($replacing student$\_$name with your name$).$

a$.$ Print the newly completed worksheet in Value view, with your name and date printed in the lower left footer and the file name in the lower right footer.

b$.$ Collapse all rows and columns and then print the worksheet, no assumptions in Value view, with your name and date printed in the lower left footer and the file name in the lower right footer.

c$.$ Expand all rows and columns. Use what$-$if analysis to calculate operating cash payments if $70$ percent of purchases are paid in the current month and $30$ percent are paid in the following month. Print the resulting worksheet in Value view, with your name and date printed in the lower left footer and the file name in the lower right footer.

d$.$ Reset the payments expectations to the original $90$ percent and $10$ percent values. Use goal seek to determine what cost of expected sales percentage would cause the operating cash payments to be $$500,000$ for the quarter. Print the resulting worksheet in Value view, with your name and date printed in the lower left footer and the file name in the lower right footer.

Answer & Explanation Solved by verified expert

See Answer