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. Professio-nal 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. 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. 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.