Question

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

Answer 1

Step-1 To Find Product Sales Revenue

PsaducGeles Revenue c0co 3300g 103000 41500 122500 6560

Step-2 To Find Operating Cash Receipt

Operating Cash Receipts Budget What SUP Operating Cash Receipts Estimated Repair Hours Professional Service Revenue Product Sales Revenue Interest Revenue Total Revenue Current Month Collection 1st Subsequent month collection 2nd subsequent month collection Operating Casg Receipt Jan 220,000 800 44,800 161,000 15,000 220,800 231005 38000 18000 220800 Feb 220800 900 63200 164500 12000 239700 177900 42800 19000 239700 Mar 239700 950 74000 170500 10000 254500 185700 47400 21400 254500

Step-3 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, The calculation of Product Sales Revenue is follows

Psoduct Sales Revenue Nov