1 Create a new What SUP Operating Cash Receipts Budget Using the ch6-02 file to start your work, create a worksheet similar to the one created in this chapter to budget operating cash receipts by month for 3 months. Place assumption information in the cells provided, and define and use names extensively. The company expects to incur repair hours of 800, 900, and 950 in January, February, and March (respectively) at an expected hourly rate of $80. Product sales revenue of $150,000, $165,000, and $175,000 is expected in January, February, and March, together with interest revenue of $15,000, $12,000, and $10,000, respectively. Professional service revenue and interest revenue are all collected in the month earned. It is anticipated that 70 percent of product sales revenue will be collected in the same month as earned, 20 percent collected in the next month, and 10 percent in the following month. Product sales revenue last November was $180,000 and last December was $190,000. (Hint: Use these amounts in your budget to determine cash collections in the current 3 months of January, February, and March as a function of prior product sales. Use Excels grouping feature to group revenues rows together and to group the three monthly columns together. Save your file as ch6-02_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 in Value view, with your name and date printed in the lower left footer and the file name in the lower right footer. Print only the total cash budget, no assumptions. c. Expand all rows and columns. Use what-if analysis to calculate operating cash receipts if 85 percent of product sales revenue were collected in the same month as earned, 10 percent in the next month, and 5 percent 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 collection expectations to their original values (of 70, 20, and 10 percent). Use goal seek to determine what number of repair hours must be worked in January in order to achieve operating cash receipts of $220,000 in January. Print the resulting cash budget, 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.

PLEASE FILL OUT THE REST. Thank you!

E15 X fx B B D E F Jan Feb Mar Total 1 What SUP 2 Operating Cash Receipts Budget 3 4 Operating cash receipts 5 Estimated repair hours 6 Professional services revenue 7 Product sales revenue 8 Interest revenue 9 Total revenue 10 Current month collections 11 ist subsequent month collections 12 2nd subsequent month collections 13 Operating cash receipts 14 15 Assumptions 16 Pct. collections in current month 17 Pct. collections in 1st subsequent month 18 Pct. collections in 2nd subsequent month 19 Product sales revenue in November 20 Product sales revenue in December 21 Hourly repair rate 22 220,000 220,800 239,700 800 900 950 44,800 63,200 74,000 161,000 164,500 170,500 15,000 12,000 10,000 220,800 239,700 254,500 231,005 177,900 185,700 38,000 42,800 47,400 18,000 19,000 21,400 220,800 239,700 254,500 680,500 2,650 182,000 496,000 37,000 715,000 594,605 128,200 58,400 715,000 22