Create a new What SUP Operating Cash Payments Budget
Using the ch6-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 cur-rent month, with the remaining paid in the following month. Selling expenses are expected to be $6,000, $7,000, and $8,000 in January, Feb-ruary, 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 ch6-03_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 cur-rent 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.