Tom Randall, President and founder of Randalls Rewriteable CDs, has a problem. The 2018 fiscal year begins on April 1st and Tom has to have the Quarter 1 2018 master budget ready to be presented to the Board of Directors at 9 a.m. on April 1st. Unfortunately, many of the numbers needed to prepare the budget will not be available until late in the day on March 31st.

It seemed like an impossible task, but Tom came up with a solution. He called you, his new intern, into his office and said, We need to start working on the Quarter 1 2018 master budget today. Some of the numbers wont be available until later, but we can do a lot of the work in advance. Ive got a really important assignment for you!

Tom explained the assignment as follows. I want you to create an Excel workbook so that, as soon as we enter the missing data, the workbook will calculate the Quarter 1 2018 budget on the spot. Then all I have to do on April 1st is enter the missing data and print the workbook for the Board. You looked a little bewildered, and asked, Just what is it that we dont know? Tom handed you a sheet of paper with the following list of data

Actual sales revenues, 4^th quarter 2017

Actual cost of ABS (plastic) purchased, 4^th quarter 2017

Cost formula for 2018 manufacturing overhead costs

Cost formula for 2018 selling and administrative costs Projected number of cases of CDs to be sold

Selling price of a case of CDs

Cost of ABS per pound

Direct labor rate per hour

Dont be too overwhelmed by this, Tom continued. I realize that there is a lot we dont know right now, but there is a lot more that we do know about our 2018 budget. Just take a look at the list of information I prepared for you. With that, Tom handed you yet another sheet of paper.

1)The sales manager will not have projections of 2018 sales ready until the last minute, but we always expect sales to follow this pattern:

April sales 40% of first quarter 2018 sales

May sales 35% of first quarter 2018 sales

June sales 25% of first quarter 2018 sales

July sales 110% of June 2018 sales

August sales 130% of July 2018 sales

In addition, it is safe to estimate first quarter 2018 sales at 120% of fourth quarter 2017 sales.

2) Last year, a case of CDs (10 CDs) sold for $35. However, because of the ever-increasing productivity and competition in this market, it is expected that we will have to reduce our selling price. This change in price will not be known until April 1^st.

3) All sales are on account. Typically, 60% of our customers pay their bills during the same month that they buy the CDs, 25% do not pay until the month after the purchase, and 12% pay two months after the purchase. The remaining 3% are uncollectible. The Accounts Receivable balance, net of uncollectible accounts, is expected to be $80,000 on April 1^st. (Assume that 62.5% of this balance will be collected in April with the remainder to be collected in May.)

4) Because our sales projections are not always perfect and production lead time, we want to maintain an ending inventory of CDs equal to 15% of the following months sales. You can assume that we will have sufficient CDs on hand on March 31st to meet this requirement.

5) The production department supervisor informs us that 0.75 pounds of ABS are needed to produce a case of CDs. Our purchasing agent will not know the cost of a pound of ABS until mid-March. Over the last 6 months, suppliers have been increasing their prices of ABS as a result of rising petroleum prices, a key ingredient in ABS. In addition, the only acceptable grade of ABS is often in short supply, so we need to make sure that enough ABS is on hand at the beginning of each month to meet 30% of that months production needs. You can assume that there will be enough ABS on hand on April 1st to meet this requirement.

6) Given our monthly purchasing patterns and credit terms with suppliers, historically we have paid for 40% of our ABS purchases in the month in which the purchase is made. The remaining 60% is paid for during the following month. The controller tells me that the April 1^st accounts payable balance should be $24,000.

7) We will know the direct labor rate per hour as soon as negotiations with the union are complete, but there is no way to predict when that might happen. However, the production department supervisor tells us that it takes 0.40 hours to make a case of CDs. 8) Our cash balance on April 1^st is not known at this point. However, the controller has informed us that in addition to the cash disbursements necessary to support normal operating expenses, the production department is in need of a new CD press. The Board of Directors has given approval for the purchase of the new press at the end of June. Also, the Board declares dividends of $15,000 in the first month of each quarter to be paid in the second month of that quarter. Depreciation expense for factory related assets totals $25,000 a month, and $8,000 a month for selling and administrative assets.

9) The Board of Directors of Randalls Rewriteable CDs does not want you to prepare all of the schedules and exhibits in Chapter 8 of your textbook for its meeting on April 1st. However, you should use them as a guide while you are completing each of the required elements of your 2018 master budget workbook.

Tom continued, I prepared a file so that you can get to work on the workbook right away. The file named Excel Project Budget. In this file, the first sheet (called DATA) is where I will enter the missing data on April 1st. I named all of the sheets so that you know where to put the master budget schedules and also typed the text into each schedule. All you have to do is write formulae to calculate the numbers and format the individual budgets. For control purposes, you must return the file to me when the project is complete. There is a secret code in the workbook so that I can be sure that you do your own work.

As you began to walk away, Tom stopped you and made one last comment. Remember, I wont know the missing data until the very last second. Im not going to have time to check your work before the Board sees it, so it is crucial that the budget is attractively prepared as well as computationally correct.