I am really bad at excel and would really appreciate any help on this assignment; this is a budgeting problem that has to be interactive across the cell sheets.

ACCT 389 Cost Accounting Homework 5 Due Thursday November 3 Using an excel workbook, create the 2017 budget for Pies R Us. You will create quarterly budgets that add up to an annual budget. See the in chapter example of the quarterly cash budget on page 606 for an example. Use a separate sheet in your workbook for each budget. You should have the following sheets: 1. Input 2. Revenues - Revenues Budget 3. Production - Production Budget in units 4. Direct Materials - Direct materials usage budget in quantity and dollars and Direct materials purchases budget 5. Direct Labor - Direct Manufacturing Labor Costs Budget 6. Overhead - Manufacturing overhead costs budget 7. Inventory - Unit costs of ending finished goods inventory and Ending inventories budget 8. Cost of Goods Sold - Cost of Goods Sold Budget 9. Selling and Administrative Costs - Selling and Administrative Costs Budget 10. Income Statement - Budgeted Income Statement 11. Cash Budget - Cash Budget 12. Balance Sheet - Budgeted Balance Sheet December 31, 2017 (end of year only - not quarterly) Sheets 2-12 should all be formulas using information from your input sheet and prior budgets. Perform sensitivity analyses for the following two scenarios: A. A new bakery opened in town. We may have to reduce our prices to $13.50 for Apple and $9.50 for Cream pies. B. A new supplier has approached us. They will sell us Apple filling for $0.80 a pound but it will take 3.5 pounds to make an Apple pie with the new filling. Use sheet 13 Sensitivity to summarize the impact of each scenario on operating income and short term borrowings and repayments. See Page 230 for an example. Save the file as lastname.xls. Submit the excel spreadsheet on Moodle. On the following pages you will find all the detailed information to prepare the budgets. Pies R Us 2017 Budget Pies R Us sells two types of pies: Double Crust Fruit Pies and Single Crust Cream Pies. Revenue unrelated to sales is $0. Work in process inventory is negligible and is ignored. Direct materials inventory and finished goods inventory are costed using the first in first out method. The unit costs of direct materials purchased and unit costs of finished goods sold remain unchanged throughout each budget year but can change from year to year. There are five kinds of direct materials: fruit filling, cream filling, crust, foil pan, cardboard box. The direct material costs are variable with respect to units of output - pies. Direct manufacturing labor workers are hired on an hourly basis; no overtime is worked. There are two cost drivers for manufacturing overhead: direct manufacturing labor hours and baking hours. There are two manufacturing overhead cost pools: manufacturing operations and oven operations. Direct manufacturing hours is the cost driver for the variable portion of manufacturing operations overhead. The fixed component of manufacturing operations overhead is tied to the budgeted direct manufacturing labor hours Pies R Us has planned for 2017. Baking hours are the cost driver for the variable portion of baking overhead. The fixed component of baking overhead is tied to the budgeted baking hours Pies R Us has planned for 2017. For computing inventoriable costs, Pies R Us allocates all variable and fixed manufacturing overhead costs using direct manufacturing labor hours and baking overhead costs using baking hours. There is one oven that can bake 20 pies at a time. Fruit pies are baked at 350 degrees and cream pies at 325 degrees. Only one type of pie can be baked in the oven at one time. Nonmanufacturing costs consist of fixed selling and administrative costs and are incurred evenly throughout the year. The following data are available for the 2017 budget: Direct materials Fruit filling $1.00 per pound Cream filling $1.60 per pound Crust $0.40 per pound Tin pan $0.15 per pan Cardboard box $0.10 per box Direct manufacturing labor $15 per hour Overhead Costs Variable manufacturing operations Variable baking Fixed manufacturing operations Fixed baking $0.75 per direct labor hour $2.00 per baking hour $5,000 $12,000 Selling and administrative costs $60,000 Content per pie: Filling Crust Tin pan Box Direct manufacturing labor Baking hour Fruit 3 lbs 1 lb 1 1 25 minutes 1 hour Cream 2 lbs 0.5 lbs 1 1 15 minutes 1 hour Selling price $15 $10 Beginning finished goods inventory units 200 300 Beginning finished goods inventory costs $2,100 $2,250 Ending finished goods inventory target units 10% of next quarter's sales, use Q1 2017 as the estimate for Q1 2018 Fruit filling beginning inventory units 3,000 lbs Fruit filling beginning inventory costs $3,000 Cream filling beginning inventory units 3,000 lbs Cream filling beginning inventory costs $4,800 Crust beginning inventory units 1,600 lbs Crust beginning inventory costs $800 Tin pan beginning inventory units 2,500 pans Tin pan beginning inventory costs $375 Cardboard box beginning inventory units 2,500 boxes Cardboard box beginning inventory costs $250 Ending direct materials inventory target units is 50% of next quarter's production needs, use Q1 2017 as the estimate for Q1 2018 Sales units: Fruit Cream Total Q1 2,000 3,000 5,000 Q2 1,000 1,000 2,000 Q3 5,000 2,000 7,000 Q4 10,000 5,000 15,000 Total 18,000 11,000 29,000 Information for Cash Budget: Minimum cash balance required at the end of each quarter is $15,000 Collections of revenues are 80% in the quarter of sale and 20% in the following quarter Payments to suppliers are 67% in the quarter of purchase and 33% in the following quarter Direct labor is paid in the quarter incurred Fixed manufacturing operating and baking overhead is 20% depreciation All variable overhead and non depreciation overhead are paid in the quarter incurred Selling and Administrative costs are 10% depreciation All S&A non depreciation costs are paid in the quarter incurred The income tax rate is 34% Income tax expense is calculated for the full year only income taxes paid in Q1 is the payable from 2016, income taxes paid in Q2 through Q4 are quarterly estimated payments of $2,500 each All borrowings occur at the beginning of the quarter All debt repayments occur at the end of the quarter Interest is 10% a year and paid when debt is repaid at the end of a quarter Pies R US Budgeted Balance Sheet 12/31/16 Cash Accounts Receivable Materials inventory Finished goods inventory PPE Accumulated Depreciation Total assets Accounts Payable Income Taxes Payable Note Payable Total liabilities Common Stock Retained Earnings Total Stockholders Equity Total L & SE $ $ $ $ 30,000 35,000 9,225 4,350 30,000 (9,000) 99,575 30,000 1,000 20,000 51,000 25,000 23,575 48,575 99,575 Sales units: Fruit Cream Total Q1 2,000 3,000 5,000 Fruit Selling price Content per pie: Filling Crust Tin pan Box $15 Fruit 3 lbs 1 lb 1 1 Quarter one Beginning Balance Fruit filling (pounds) Cream filling(pounds) Crust (pounds) Tin Pan (pans) Cardboard Box (boxes) 3000 3000 1600 2500 2500 Direct manufacturing labor hours per unit Cost per direct labor hour Direct manufacturing labor Baking hour Fixed manufacturing operations overhead per direct labor hour Total budgeted baking hours fixed baking overhead per baking hour Q2 Q3 1,000 1,000 2,000 Q4 5,000 2,000 7,000 Total 10,000 5,000 15,000 18,000 11,000 29,000 Cream $10 Cream 2 lbs 0.5 lbs Direct materials Fruit filling Cream filling Crust Tin pan Cardboard box 1 1 Ending Balance Quarter two Beginning Balance Ending Balance 2100 2100 1100 1100 975 975 1250 1250 1250 1250 Fruit $1.00 per pound $1.60 per pound $0.40 per pound $0.15 per pan $0.10 per box Quarter Three Beginning Balance 8250 8250 2300 2300 3325 3325 3900 3900 3900 3900 Cream 0.4166666667 15 25 minutes 1 hour 0.487804878 29000 0.4137931034 0.25 15 15 minutes 1 hour Overhead Costs Variable manufacturing operations $0.75 per direct labor hour Variable baking $2.00 per baking hour Fixed manufacturing operations $5,000 Fixed baking $12,000 Quarter one Fruit Beginning finished goods Ending finished goods inv hree Ending Balance 13800 4800 5800 7000 7000 r direct labor hour r baking hour Quarter four Beginning Balance Quarter two Fruit Cream Cream Quarter Three Fruit 200 300 100 100 500 100 100 500 200 1000 Ending Balance 13800 2850 4800 2800 5800 1650 7000 2350 7000 2350 Quarter Three Cream Quarter Four Fruit Cream 200 1,000 500 500 200 300 Fruit Forecasted Unit sales Price Per unit Sales revenue Revenues budget Quarter Two Fruit Cream Quarter one Cream 2,000 3,000 1,000 1,000 $15 $30,000 $10 $30,000 $15 $15,000 $10 $10,000 venues budget Fruit Quarter Three Cream Fruit Quarter Four Cream Fruit Total Cream 5,000 2,000 10,000 5,000 18,000 11,000 $15 $75,000 $10 $20,000 $15 $150,000 $10 $50,000 $15 $270,000 $10 $110,000 Fruit Forecasted unit sales Add: planned ending Finished inventory in units Total production required Less: Beginning finished inventory in units Units to be manufactured Quarter One Cream 2,000 3,000 100 100 2,100 3,100 200 300 1,900 2,800 Fruit Quarter Two Cream Fruit Production Budget in units Quarter Three Cream Fruit Quarter Four Cream Total Fruit 1,000 1,000 5,000 2,000 10,000 5,000 500 200 1,000 500 200 300 1,500 1,200 6,000 2,500 10,200 5,300 100 100 500 200 1,000 500 1,400 1,100 5,500 2,300 9,200 4,800 18,000 18,000 Total Cream 11,000 11,000 Fruit Units to be manufactured Quarter One Cream 1900 2800 Filling (pounds) 3 2 Crust (pounds) 1 0.5 Tin pan 1 1 Box 1 1 Filling (pounds) 5700 5600 Crust (pounds) 1900 1400 Tin pan 1900 2800 Box 1900 2800 Direct materials Per unit: Manufacturing Needs(usage): Direct materials usage in Dollars Manufacturing usage (raw materials) Price Per raw material Usage in dollars Quarter one Fruit filling Cream filling 5700 5600 $1 $1.60 $5,700 $8,960.00 Manufacturing usage (raw materials) Add: ending inventory of raw materials Total needs Less: beginning inventory of raw materials Raw materilas to be purchased Price Per raw material Purchases in dollars Quarter one Fruit filling Cream filling 5700 5600 2100 1100 7800 6700 3000 3000 4800 3700 $1 $1.60 $4,800 $5,920.00 Direct Materials usage budget in Quantity Quarter Two Quarter Three Fruit Cream Fruit Cream Fruit Quarter Four Cream Total Fruit 1400 1100 5500 2300 9200 4800 18000 3 2 3 2 3 2 3 1 0.5 1 0.5 1 0.5 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 4200 2200 16500 4600 27600 9600 54000 1400 550 5500 1150 9200 2400 18000 1400 1100 5500 2300 9200 4800 18000 1400 1100 5500 2300 9200 4800 18000 ct materials usage in Dollars Quarter one Quarter two Crust Tin pan Box Fruit filling Cream filling Crust Tin pan 3300 4700 4700 4200 2200 1950 2500 $0.40 $0.15 $0.10 $1 $1.60 $0.40 $0.15 $1,320.00 $705.00 $470.00 $4,200 $3,520.00 $780.00 $375.00 Direct materials purcha Quarter one Quarter two Crust Tin pan Box Fruit filling Cream filling Crust Tin pan 3300 4700 4700 4200 2200 1950 2500 975 1250 1250 8250 2300 3325 3900 4275 5950 5950 12450 4500 5275 6400 1600 2500 2500 2100 1100 975 1250 2675 3450 3450 10350 3400 4300 5150 $0.40 $0.15 $0.10 $1 $1.60 $0.40 $0.15 $1,070.00 $517.50 $345.00 $10,350 $5,440.00 $1,720.00 $772.50 Total Cream 11000 2 0.5 1 1 22000 5500 11000 11000 Quarter Three Box Fruit filling Cream filling Crust Tin Pan Box Fruit filling 2500 16500 4600 6650 7800 7800 27600 $0.10 $1 $1.60 $0.40 $0.15 $0.10 $1 $250.00 $16,500 $7,360.00 $2,660.00 $1,170.00 $780.00 $27,600 Quar Direct materials purchases Budget Quarter Three Box Fruit filling Cream filling Crust Tin Pan Box Fruit filling 2500 16500 4600 6650 7800 7800 27600 3900 13800 4800 5800 7000 7000 2850 6400 30300 9400 12450 14800 14800 30450 1250 8250 2300 3325 3900 3900 13800 5150 22050 7100 9125 10900 10900 16650 $0.10 $1 $1.60 $0.40 $0.15 $0.10 $1 $515.00 $22,050 $11,360.00 $3,650.00 $1,635.00 $1,090.00 $16,650 Quar Quarter four Total Cream filling Crust Tin pan Box Fruit filling Cream filling Crust 9600 11600 14000 14000 54000 22000 23500 $1.60 $0.40 $0.15 $0.10 $1 $1.60 $0.40 $15,360.00 $4,640.00 $2,100.00 $1,400.00 $54,000 $35,200.00 $9,400.00 Quarter four Cream filling Crust Tin pan Box 9600 11600 14000 14000 2800 1650 2350 2350 12400 13250 16350 16350 4800 5800 7000 7000 7600 7450 9350 9350 $1.60 $0.40 $0.15 $0.10 $12,160.00 $2,980.00 $1,402.50 $935.00 Total Tin Pan Box 29000 29000 $0.15 $0.10 $4,350.00 $2,900.00 Fruit Units to be manufactured Direct manufacturing labor hours per unit Total direct labor hours required Cost per direct labor Cost of manufacturing direct labor Quarter One Cream 1900 0.4166666667 791.6666666667 15 11875 Quarter Two Fruit 2800 1400 0.25 0.4166666667 700 583.33333333 15 15 10500 8750 Direct Manufacturing Labor costs Budget Quarter Two Cream Fruit 1100 Quarter Three Cream 5500 0.25 0.4166666667 275 2291.6666667 15 15 4125 34375 Fruit 2300 Quarter Four Cream 9200 0.25 0.4166666667 575 3833.3333333 15 15 8625 57500 Fruit 4800 Total Cream 18000 11000 0.25 0.416666667 1200 7500 15 15 18000 112500 0.25 2750 15 41250 s Budget Quarter One Fruit Units to be manufactured Direct manufacturing labor hours per unit Baking hours per unit Total direct labor hours required Total baking hours Variable manufacturing operations overhead ($0.75per direct labour) Variable baking overhead ($2 per bakinghour) Fixed manufacturing overhead ($5000/10250 per direct labour) Fixed baking overhead ($12000/29000 per baking hour) 1900 0.4166666667 1 791.6666666667 1900 $593.75 $3,800 386.1788617886 Manufacturing Overheads Cost budget Quarter One Cream Fruit 2800 Quarter Two Cream 1400 Fruit 1100 Quarter Three Cream 5500 0.25 0.4166666667 1 1 0.25 1 0.4166666667 1 700 583.333333333 275 2291.666666667 Quarter Four Fruit 2300 9200 0.25 1 0.4166666667 1 575 3833.333333333 2800 1400 1100 5500 2300 9200 $525.00 $437.50 $206.25 $1,718.75 $431.25 $2,875.00 $5,600 $2,800 $2,200 $11,000 $4,600 $18,400 get Quarter Four Cream Fruit 4800 Total Cream 18000 11000 0.25 0.4166666667 1 1 0.25 1 1200 7500 2750 4800 18000 11000 $900.00 $5,625.00 $2,062.50 $9,600 $36,000 $22,000