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

do that (put your sales budget first (and also the materials cost), and then you can absolute reference in the materials cost in the direct materials purchases budget). So start with sales budget at the top, and also list the materials cost at the top. Remember, I want to be able to change these numbers and have the impact be reflected in the rest of the budgetary numbers. So the production budget needs to be linked to the sales budget, the DM purchases needs to be linked to the production budget, and the cash payments budget needs to be linked to the DM purchases budget. Sales Then Production Then DM Purchases Then Cash Payments