Charlie wants to have raw materials available to meet 50% of the next month's production requirement. Production is equal to sales, so we have a production schedule that looks like the bottom row of the table below.

We are not given any information about the quantities of materials needed, such as board feet of lumber, quarts of stain or finish, etc. However, we do know that each chair takes $50.00 worth of lumber and $13.00 worth of supplies, or a total of $63.00 per chair. Multiplying the $63.00/chair by the production volumes above, we get the total value of materials and supplies needed to meet each month's production [row 1, below]:

Row 2 ["Required for ending inventory"] is equal to 50% of the production requirement for the next month. Note that January of year 2 is included, since we need to know how much material Charlie will want to have at the end of December of year 1. We have assumed that January X2 will look like January X1; the ending inventory for January X2 is blank because we have made to estimate for February X2 sales and production.

Row 3 ["Total required"] is the sum of rows 1 and 2.

Row 3 ["Less beginning invty"] takes into account the amount that we have on hand at the beginning of the month. Charlie's balance sheet had $200 in inventory. This is the source of the $200 in the January X1 column, and it is cell referenced to the balance sheet so that any changes in the information Charlie gives us can be immediately reflected in our budget calculations. The beginning inventory amounts for subsequent months are merely the ending balances for the corresponding prior month [January's ending balance is February's beginning balance, and so on].

Row 4 ["Purchases needed"] is the difference between the requirement in row 3 and the beginning inventory in row 4.

While we're at it, we can calculate Charlie's cash outlay for materials and supplies. Since he uses his debit card, purchases of inventory and the cash outlay for those purchases will be in the same month. We can add a row to our table for the cash disbursements for material purchases:

Replicate the table above, using cell referencing. Once you have this portion of Charlie's budget projections completed, you are ready to move on to a summary of cash disbursements. Click "next."

Copyright © 2004 Gerald M. Myers. All rights reserved. This site has been developed as aid to instructors and students in managerial accounting. The scenarios contained herein are not intended to reflect effective or ineffective handling of managerial situations. Any resemblance to existing organizations is purely coincidental.

Last modified: August 03, 2005