AC215-MANAGERIAL ACCOUNTING EXCEL BUDGETING PROJECT INSTRUCTIONS: SPRING 2018 Tom Randall, President and founder of Randall's Rewriteable CD's, has a problem. The 2018 fiscal year begins on April Ist 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 Aprl Ist. 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 won't be available until later, but we can do a lot of the work in advance. I've 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 don't know?" Tom handed you a sheet of paper with the following list of data Actual sales revenues, 4 quarter 2017 Actual cost of ABS (plastic) purchased, 4th 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 CD:s Cost of ABS per pound Direct labor rate per hour Don't be too overwhelmed by this," Tom continued. "I realize that there is a lot we don't 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 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: 40% of first quarter 201 8 sales 35% of first quarter 2018 sales 25% of first quarter 2018 sales 110% of June 2018 sales 130% of July 2018 sales June sales August sales In addition, it is safe to estimate first quarter 2018 sales at 120% of fourth quarter 2017 AC215-MANAGERIAL ACCOUNTING EXCEL BUDGETING PROJECT INSTRUCTIONS: SPRING 2018 Tom Randall, President and founder of Randall's Rewriteable CD's, has a problem. The 2018 fiscal year begins on April Ist 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 Aprl Ist. 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 won't be available until later, but we can do a lot of the work in advance. I've 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 don't know?" Tom handed you a sheet of paper with the following list of data Actual sales revenues, 4 quarter 2017 Actual cost of ABS (plastic) purchased, 4th 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 CD:s Cost of ABS per pound Direct labor rate per hour Don't be too overwhelmed by this," Tom continued. "I realize that there is a lot we don't 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 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: 40% of first quarter 201 8 sales 35% of first quarter 2018 sales 25% of first quarter 2018 sales 110% of June 2018 sales 130% of July 2018 sales June sales August sales In addition, it is safe to estimate first quarter 2018 sales at 120% of fourth quarter 2017