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 $150000, $165000, and $175000 is expected in January, February, and March, together with interest revenue of $15000, $12000, and $10000, 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 in the next month, and 10 percent in the following month. Product sales revenue last November was $180000 and last December was $190000. (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 Excel's grouping feature to group "revenues" rows together and to group the three monthly columns together. a) Print the newly completed worksheet in Value view b) Collapse all rows and columns and then print the worksheet in Value view. 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 percents 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. 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 $220000 in January. Print the resulting cash budget, no assumptions in Value view.

A1 XfWhat SUP 1 What SUP 2 Operating Cash Receipts Budget Feb Mar Total an 4 Operating cash receipts EsTimafed repair hours 6 Professional services revenue 7 Product sales revenue 8 Interest revenue 9 Total revenue 10 Current month collections 11 lst 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