xcel Project #2

Dallas Company is preparing a master budget for the 1st quarter of 2023. Given the following information, you are to prepare in Excel using formulas the following: (A) Production Budget, (B) Direct Materials Budget, and (C) Cash Payments for Direct Materials Purchases Budget for the 1st quarter of 2023.

For the budgets, you need to make them adaptable so that if I change the sales budget and/or materials cost per unit, the budgets will automatically update for these changes (for a 9 or better score). For a score of 8 or better, I need to see the months of January, February, March, and the Quarter total. Facts: Projected Sales: Jan 1,100 units at $10 per unit Feb 1,150 units at $10 per unit March 1,200 units at $10 per unit April 1,150 units at $10 per unit May 1,100 units at $10 per unit Finished

Goods Inventory: Policy requires ending inventory to be 10% of this months projected sales. Beginning Inventory as of Q1 was 100 units. Direct Materials Inventory: Each unit produced requires 3 pounds of Direct materials. Direct Materials costs $3 per pound. Policy requires ending Direct materials to be 30% of next months production needs. They currently have 1,000 pounds of Direct materials on hand as of the Beginning of Q1.

Materials Purchases: All Direct Materials all purchased on account, with terms of n/15. Because inventory is purchased evenly throughout the month, of the purchases in a month are paid in the month of purchase and the other are paid in the following month. There are currently $600 owed as of the beginning of the Q1 (all of that will be paid in Jan.).

Worksheet Hints: Hint You will want to use reference cells when you are constructing your budgets. If you use reference cells for the first month, then you can copy and paste for the remaining months. There are relative and absolute reference cells that need to be used. Here is a link for a web description of these types http://web.pdx.edu/~stipakb/CellRefs.htm Also, when I physically change the sales budget (or materials cost) in your worksheet, the other budgets should change (appropriately) for the different level of sales. Absolute reference cells will allow you to