Using Excel create a:
1. Sales revenue budget
2. Production budget (Note: Beginning and ending inventory for the year correspond with the beginning inventory for Q1 and ending inventory for Q4.)
3. Direct materials usage budget (there should be two of these, one for each DM)
4. Direct materials purchases budget (there should be two of these, one for each
DM) (Note: Beginning and ending inventory for the year correspond with the
beginning inventory for Q1 and ending inventory for Q4.)
5. Direct labor budget
Using the given information in the photo provided.
Problem: Quicky-Co. is a small, high speed golf-cart manufacturer. Production of carts occurs year round, but sales volume is significantly higher during spring and summer months The CFO projects sales volume to increase by 10% in each of the next two years. The sales volume in carts for the year just ended (2020) and the expected sales for each of the next two years (2021 and 2022) are given below. 2020 2021. (expected) 2022 (expected) Golf Carts 3,000 3.300 3.630 Each quarter's sales are anticipated to follow the pattern below, as a percent of annual unit sales: Quarter 1 (Jan. to March) 20% Quarter 3 (July to Sept.) 30% Quarter 2 (Apr. to June) 40% Quarter 4 (Od to Dec.) 10% The average selling price per golf cart was in 2020. and is expected to be during 2021-2022. $9,000 The following data pertain to production policies and manufacturing specifications followed by Quicky-Co: ., The desired ending finished goods inventory for each quarter is 10% of next quarter's expected sales in units. The ending finished goods inventory for 2020 followed this pattern. The cost per finished golf cart at Dec 31, 2020, the fiscal year end, was $3,941.04 b. Each golf cart produced requires two primary direct materials. Other materials used in production of these carts are insignificant and are treated as indirect materials. Each machine produced requires one motor. The desired ending inventory of motors is 20% of next quarter's expected production needs, and the same pattern was followed at the end of both 2019 and 2020. The anticipated purchase price for a golf cart motor over each of the next two years is $1200 per motor, which is the same price as in 2020. The other primary direct material is plastic. Each golf cart produced requires 200 pounds of plastic. This type of plastic cost $3 per pound in each of 2019 and 2020, and the same price per pound is expected in 2021 and 2022. The desired ending raw material c. d. $2.10 Inventory for plastic is 40% of next quarter's expected production needs, and the same pattern was followed at the end of both 2019 and 2020. Each golf cart produced also requires 16 hours of direct labor time. Quicky's direct labor costs in 2020 averaged $21 per hour. The same rate is expected in 2021 and 2022 Manufacturing Overhead cost per quarter is estimated as follows. Variable manufacturing overhead varies with direct labor costs incurred (DLS). Fixed Overhead Variable Overhead.(per DLS) Indirect materials Utilities $70,000 $0.80 Maintenance $120,000 $0.20 Supervision $170,000 Depreciation $145.000 Other $15,000 Quarterly selling and administrative expenses are estimated as follows. Variable selling and administrative expenses vary with the number of golf carts sold. Fixed S & A Variable S & A (per cart sold) Salaries $240,000 Commissions $150.00 Depreciation $40,000 Shipping $75,000 $130.00 Other $30,000 $20.00 $0.40 e. REQUIRED: Prepare operating budgets for 2021 by quarter and for the year (so you'll need at least 5 columns of numeric data for each budget schedule) (Hint: For some of the following budget schedules you will also need to complete the schedules for some combination of Q4 of 2020, and Q1 of 2022 and 2 of 2022, so it would be wise to compute those values wherever you can, as some of this data will be necessary for later calculations) with the following schemos
