Sales drive the level of production. Production is also a function of the beginning finished goods inventory and the desired ending finished goods inventory. The budgeted units of production can be calculated as the number of units sold, plus the desired ending finished goods inventory, minus the beginning finished goods inventory. In planning production, one must give careful consideration to the productive capacity, availability of raw materials, and similar considerations.
Following is the production budget of Shehadeh Movie Screens. Shehadeh plans to end each quarter with sufficient inventory to cover 25% of the following quarter's planned sales. Shehadeh started the New Year with 525 units in stock, and planned to end the year with 700 units in stock. Below is a quarter-by-quarter determination of the necessary production. Carefully examine this information, paying very close attention to how each quarter's desired ending finished goods can be tied to the following quarter's planned sales. In case it is not obvious, the estimated units sold information was taken from the sales budget; utilizing the power of the spreadsheet, the values in the cells on row 7 of this "production" sheet were simply taken from the corresponding values in row 7 of the "Sales" sheet ("=Sales!C7", "=Sales!C8", etc.).
Direct Material Purchases Budget
Each movie screen requires 35 square feet of raw material. For example, the scheduled production of 1,875 units for the second quarter will require 65,625 square feet of raw material. Shehadeh maintains raw material inventory equal to 20% of the following quarter's production needs. Thus, Shehadeh plans to start the second quarter with 13,125 square feet (65,625 X 20%) and end the quarter with 19,950 square feet (99,750 X 20%). Budgeted purchases can be calculated as direct materials needed in planned production, plus the desired ending direct material inventory, minus the beginning direct materials inventory (65,625 + 19,950 - 13,125 = 72,450). This fundamental calculation is repeated for each quarter. The upper portion of the following "Materials" spreadsheet illustrates these calculations. Once again, the electronic spreadsheet draws data from preceding sheets via embedded links.
The direct material purchases budget provides the necessary framework to plan cash payments for materials. The lower portion of the above spreadsheet shows that the raw material is slated to cost $1.40 per square foot. Shehadeh pays for 80% of each quarter's purchases in the quarter of purchase. The remaining 20% is paid in the following period.
The direct materials budget also reveals a planned end of year inventory of 19,600 square feet, which has a cost of $27,440 (19,600 X $1.40). As you will later see, this value will be needed to prepare the budgeted ending balance sheet.
Direct Labor Budget
The direct labor budget provides the framework for planning staffing needs and costs. Each of Shehadeh's screens requires three direct labor hours to produce. As revealed by the "labor" sheet, the scheduled production is multiplied by the number of hours necessary to produce each unit. The resulting total direct labor hours are multiplied by the expected hourly cost of labor to produce the total direct labor cost. As is usually the case, there is very little lag time between incurring and paying labor costs. Thus, Shehadeh assumes that the cost of direct labor will be funded in the quarter incurred.
Factory Overhead Budget
Like many companies, Shehadeh applies overhead based on direct labor hours. Based on extensive analysis, the annual factory overhead is anticipated to include a fixed amount of $220,200, plus $5 per direct labor hour. The fixed portion includes depreciation of $3,000 per quarter for the first half of the year and $7,000 per quarter for the last half of the year (the increase is due to a planned purchase of factory equipment occurring at the end of the second quarter). Following is the factory overhead budget. Notice that the bottom portion of the budget reconciles the total factory overhead with the cash paid for overhead (depreciation is subtracted because it is a noncash expense). Both of these amounts will be needed to complete subsequent budget calculations.
Be mindful that the variable factory overhead rate shown in the spreadsheet is arrived at by very careful analysis. The budget process entails an assessment of variable overhead costs to determine this expected rate. As such, budgeting requires a great deal of study into the actual production process. There is much more to budgeting than just cranking numbers through a spreadsheet.
The direct labor hours used in the Factory Overhead sheet are drawn from the Direct Labor budget. Further, the sidebar notes also indicate that the average overhead rate (fixed and variable together, applied to the total labor hours for the year) is $13 per hour. This information is useful in assigning costs to ending inventory. Assuming an average-cost method, ending finished goods inventory can be valued as follows: