Horngren's Accounting (12th Edition)
12th Edition
ISBN: 9780134486444
Author: Tracie L. Miller-Nobles, Brenda L. Mattison, Ella Mae Matsumura
Publisher: PEARSON
expand_more
expand_more
format_list_bulleted
Concept explainers
Videos
Textbook Question
Chapter 22, Problem P22.56CT
Using Excel for to prepare an operating budget (manufacturing company)
Download an Excel template for this problem online in MyAccountingLab or at http://www.pearsonhighered.com/Horngren
Thunder Creek Company is preparing budgets for the first quarter of 2O18. All relevant information is presented on the Excel template.
Requirements
1 Prepare a Sales Budget.
2 Prepare a Production Budget.
3. Prepare a Direct Materials Budget.
4. Prepare a Direct Labor Budget.
5. Prepare a Manufacturing
6. Prepare a Cost of Goods Sold Budget.
7. Prepare a Selling and Administrative Expense Budget.
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
The Bakery Department of Culbert Dessert Corporation has submitted the following forecast of fruit pies to be produced by quarter for
the upcoming fiscal year.
Units to be produced
First
Quarter
9,300
Second Third
Quarter Quarter
12,300 10,300
Units to be produced
Direct labour time per unit (hours)
Total direct labour-hours needed
Direct labour cost per hour
Total direct labour cost
Each unit requires 0.40 direct labour-hours, and direct labour-hour workers are paid $9.00 per hour.
In addition, the variable manufacturing overhead rate is $1.00 per direct labour-hour. The fixed manufacturing overhead is $26,250 per
quarter. The only non-cash element of manufacturing overhead is depreciation, which is $7,650 per quarter.
Required:
1. Prepare the company's direct labour budget for the upcoming fiscal year, assuming that the direct labour workforce is adjusted each
quarter to match the number of hours required to produce the forecast number of units produced.
Fourth
Quarter
14,300
Culbert…
Howard Cooper, the president of Campbell Computer Services, needs your help. He wonders about the potential effects on the firm's
net income if he changes the service rate that the firm charges its customers. The following basic data pertain to fiscal Year 3.
Standard rate and variable costs
Service rate per hour
$
85.00
Labor cost
37.00
Overhead cost
7.10
Selling, general, and administrative cost
Expected fixed costs
Facility maintenance
Selling, general, and administrative
3.80
$522,000
142,000
Required:
a. Prepare the pro forma income statement that would appear in the master budget if the firm expects to provide 45,000 hours of
services in Year 3.
b. A marketing consultant suggests to Mr. Cooper that the service rate may affect the number of service hours that the firm can
achieve. According to the consultant's analysis, if Campbell charges customers $80 per hour, the firm can achieve 50,000 hours of
services. Prepare a flexible budget using the consultant's assumption.
c. The same…
The Bakery Department of Culbert Dessert Corporation has submitted the following forecast of fruit pies to be produced by quarter for
the upcoming fiscal year.
First
Second
Third
Fourth
Quarter
8,300
Quarter
Quarter
9,300
Quarter
13,300
Units to be produced
11,300
Each unit requires 0.60 direct labour-hours, and direct labour-hour workers are paid $9.00 per hour.
In addition, the variable manufacturing overhead rate is $1.50 per direct labour-hour. The fixed manufacturing overhead is $23,750
per quarter. The only non-cash element of manufacturing overhead is depreciation, which is $7,150 per quarter.
Required:
1. Prepare the company's direct labour budget for the upcoming fiscal year, assuming that the direct labour workforce is adjusted each
quarter to match the number of hours required to produce the forecast number of units produced.
Chapter 22 Solutions
Horngren's Accounting (12th Edition)
Ch. 22 - Prob. 1QCCh. 22 - A company prepares a five-year budget. This budget...Ch. 22 - Which of the following is the cornerstone of the...Ch. 22 - Suppose Iron City manufactures cast iron skillets....Ch. 22 - Suppose Iron City manufactures cast iron skillets....Ch. 22 - Suppose Iron City manufactures cast iron skillets....Ch. 22 - Suppose Iron City manufactures cast iron skillets....Ch. 22 - The budgeted income statement is part of which...Ch. 22 - Prob. 9QCCh. 22 - Suppose Mallcentral sells 1,000 hardcover books...
Ch. 22 - Suppose Mallcentral sells 1,000 hardcover books...Ch. 22 - The budgeted balance sheet is part of which...Ch. 22 - Information technology has made it easier for...Ch. 22 - Prob. 1RQCh. 22 - Prob. 2RQCh. 22 - How is benchmarking beneficial?Ch. 22 - What is budgetary slack? Why might managers try to...Ch. 22 - Explain the difference between strategic and...Ch. 22 - Explain the difference between static and flexible...Ch. 22 - What is a master budget?Ch. 22 - In a manufacturing company, what are the three...Ch. 22 - Why is the sales budget considered the cornerstone...Ch. 22 - Prob. 10RQCh. 22 - What is the formula used to determine the amount...Ch. 22 - What are the two types of manufacturing overhead?...Ch. 22 - How is the predetermined overhead allocation rate...Ch. 22 - What is the capital expenditures budget?Ch. 22 - What are the three sections of the cash budget?Ch. 22 - Prob. 16RQCh. 22 - How does the master budget for a merchandising...Ch. 22 - Prob. 18RQCh. 22 - Prob. 19RQCh. 22 - Prob. 20RQCh. 22 - Budgeting benefits Learning Objective 1 List the...Ch. 22 - S22-2 Budgeting types
Learning Objective...Ch. 22 - Prob. S22.3SECh. 22 - S22-4 Preparing an operating budget—production...Ch. 22 - Prob. S22.5SECh. 22 - Prob. S22.6SECh. 22 - Prob. S22.7SECh. 22 - Prob. S22.8SECh. 22 - Prob. S22.9SECh. 22 - Prob. S22.10SECh. 22 - Prob. S22.11SECh. 22 - S22-12 Understanding the components of the master...Ch. 22 - S22-13 Preparing an operating budget—sales...Ch. 22 - Prob. S22.14SECh. 22 - Prob. S22.15SECh. 22 - Prob. S22.16SECh. 22 - Prob. S22.17SECh. 22 - Prob. S22.18SECh. 22 - Prob. S22.19SECh. 22 - Prob. S22.20SECh. 22 - Prob. E22.21ECh. 22 - Prob. E22.22ECh. 22 - Prob. E22.23ECh. 22 - Preparing an operating budget—direct materials,...Ch. 22 - E22-25 Preparing an operating budget—cost of goods...Ch. 22 - Prob. E22.26ECh. 22 - Preparing a financial budget—schedule of cash...Ch. 22 - Prob. E22.28ECh. 22 - E22-29 Preparing the financial budget-cash...Ch. 22 - Preparing the financial budget—budgeted balance...Ch. 22 - Prob. E22.31ECh. 22 - Prob. E22.32ECh. 22 - Prob. E22.33ECh. 22 - Prob. E22.34ECh. 22 - E22-35 Preparing a financial budget—cash...Ch. 22 - Prob. E22.36ECh. 22 - Using sensitivity analysis Learning Objective 7 1....Ch. 22 - Preparing an operating budget—sales, production,...Ch. 22 - Prob. P22.39APGACh. 22 - Preparing a financial budgetbudgeted income...Ch. 22 - Completing a comprehensive budgeting...Ch. 22 - Prob. P22.42APGACh. 22 - Prob. P22.43APGACh. 22 - P22-44A Preparing a financial budget—budgeted...Ch. 22 - Prob. P22.45APGACh. 22 - Prob. P22.46APGACh. 22 - Prob. P22.47BPGBCh. 22 - Prob. P22.48BPGBCh. 22 - Prob. P22.49BPGBCh. 22 - Prob. P22.50BPGBCh. 22 - Prob. P22.51BPGBCh. 22 - Prob. P22.52BPGBCh. 22 - Prob. P22.53BPGBCh. 22 - Preparing a comprehensive budgeting...Ch. 22 - Prob. P22.55BPGBCh. 22 - Using Excel for to prepare an operating budget...Ch. 22 - Prob. P22.57CPCh. 22 - Prob. 22.1TIATCCh. 22 - Prob. 22.1DCCh. 22 - Prob. 22.1EICh. 22 - Prob. 22.1FC
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, accounting and related others by exploring similar questions and additional content below.Similar questions
- Refer to Cornerstone Exercise 8.6. Required: 1. Calculate the total budgeted cost of units produced for Play-Disc for the coming year. Show the cost of direct materials, direct labor, and overhead. 2. Prepare a cost of goods sold budget for Play-Disc for the year. 3. What if the beginning inventory of finished goods was 75,200 (for 16,000 units)? How would that affect the cost of goods sold budget? (Assume Play-Disc uses the FIFO method.) Play-Disc makes Frisbee-type plastic discs. Each 12-inch diameter plastic disc has the following manufacturing costs: For the coming year, Play-Disc expects to make 300,000 plastic discs, and to sell 285,000 of them. Budgeted beginning inventory in units is 16,000 with unit cost of 4.75. (There are no beginning or ending inventories of work in process.) Required: 1. Prepare an ending finished goods inventory budget for Play-Disc for the coming year. 2. What if sales increased to 290,000 discs? How would that affect the ending finished goods inventory budget? Calculate the value of budgeted ending finished goods inventory.arrow_forwardA company has prepared the operating budget and the cash budget. It is now preparing the budgeted balance sheet. Identify the document that contains each of these balances. A. cash B. accounts receivable C. finished goods inventory D. accounts payable E. equipment purchasesarrow_forwardRamos Co. provides the following sales forecast and production budget for the next four months. July 660 600 Sales (units) Budgeted production (units) April 560 500 The company plans for finished goods inventory of 180 units at the end of June. In addition, each finished unit requires 6 pounds of direct materials and the company wants to end each month with direct materials inventory equal to 25% of next month's production needs. Beginning direct materials inventory for April was 750 pounds. Direct materials cost $3 per pound. Each finished unit requires 0.20 hours of direct labor at the rate of $22 per hour. The company budgets variable overhead at the rate of $26 per direct labor hour and budgets fixed overhead of $8,600 per month. 1. Prepare a direct labor budget for April, May, and June. 2. Prepare a factory overhead budget for April, May, and June. Required 1 Required 2 May 640 630 Complete this question by entering your answers in the tabs below. RAMOS CO. June 590 600 Prepare a…arrow_forward
- 3. Using the format demonstrated in your textbook - Prepare the following budgets (be sure to show/demonstrate ALL calculations - include quarterly AND yearly totals) in excel or google drive: a. production budget b. direct materials budget c. direct labor budget d. manufacturing overhead budget e. selling and administrative budgetarrow_forwardQ4: Use the following terms in preparing the budget: Insurance Maintenance Utilities Depreciation Expense Sales Commissions Depreciation Indirect Material Maintenance Salaries Indirect Labor Supervisory Salaries Sales Salaries Insurance Expense Spree Party Lights overhead expenses are: PLEASE NOTE: All dollar amounts are rounded to whole dollars and shown with "$" and commas as needed (i.e. $12,345). Using the terms and the information above, prepare a manufacturing overhead budget if the number of units to produce for January, February, and March are 2,500, 3,000, and 2,700, respectively (use the format shown in the textbook): Spree Party Lights Manufacturing Overhead Budget For the Quarter Ending March, 2019 Jan. Feb. Mar. Units to Produce 2,500 3,000 2,700 Variable Costs: Total Variable Manufacturing Costs Fixed Costs: Total Fixed Manufacturing Costs…arrow_forwardHoward Cooper, the president of Finch Computer Services, needs your help. He wonders about the potential effects on the firm's net income if he changes the service rate that the firm charges its customers. The following basic data pertain to fiscal Year 1: Standard rate and variable costs Service rate per hour Labor cost Overhead cost Selling, general, and administrative cost. Expected fixed costs Facility maintenance Selling, general, and administrative Required: a. Prepare the pro forma income statement that would appear in the master budget if the firm expects to provide 28,000 hours of services in Year 1. b. A marketing consultant suggests to Mr. Cooper that the service rate may affect the number of service hours that the firm can achieve. According to the consultant's analysis, if Finch charges customers $57 per hour, the firm can achieve 31,000 hours of services. Prepare a flexible budget using the consultant's assumption. c. The same consultant also suggests that if the firm…arrow_forward
- A company has completed the operating budget and the cash budget. It is now preparing the budgeted balance sheet. Please use the below image as your guide for the next 5 questions. Identify the document that contains finished goods inventory. A B C D Earrow_forwardPanther Corporation appeared to be experiencing a good year. Sales in the first quarter were one-third ahead of last year, and the sales department predicted that this rate would continue throughout the entire year. The controller asked Janet Nomura, a summer accounting intern, to prepare a draft forecast for the year and to analyze the differences from last year's results. She based the forecast on actual results obtained in the first quarter plus the expected costs of production to be completed in the remainder of the year. She worked with various department heads (production, sales, and so on) to get the necessary information. The results of these efforts follow: PANTHER CORPORATION Expected Account Balances for December 31, Year 2 Cash 6,500 Accounts receivable 337,000 Inventory (January 1, Year 2) Plant and equipment Accumulated depreciation Accounts payable Notes payable (due within one year) Accrued payables 240,000 605,000 $ 181,000 197,000 217,000 110,000 Common stock 450,000…arrow_forwardA manufacturing company has prepared the operating budget and the cash budget and is now preparing the budgeted balance sheet. While doing so, the Cash balance can be taken from the OA budgeted income statement OB operational budget OC. sales budget OD. cash budgetarrow_forward
- A company has completed the operating budget and the cash budget. It is now preparing the budgeted balance sheet. Please use the below image as your guide for the next 5 questions. Identify the document that contains equipment purchases A B C D Earrow_forwardPearl Products Limited of Shenzhen, China, manufactures and distributes toys throughout South East Asia. Three cubic centimeters (cc) of solvent H300 are required to manufacture each unit of Supermix, one of the company's products. The company is planning its raw materials needs for the third quarter, the quarter in which peak sales of Supermix occur. To keep production and sales moving smoothly, the company has the following inventory requirements: a. The finished goods inventory on hand at the end of each month must equal 3,000 units of Supermix plus 25% of the next month's sales. The finished goods inventory on June 30 is budgeted to be 13,250 units. b. The raw materials inventory on hand at the end of each month must equal one-half of the following month's production needs for raw materials. The raw materials inventory on June 30 is budgeted to be 63,375 cc of solvent H300. c. The company maintains no work in process inventories. A monthly sales budget for Supermix for the third…arrow_forwardThe controller of the Stump Company is preparing the budget for 2021 and needs to estimate a cost function for delivery costs. Information regarding delivery costs incurred in the prior 2 months are: LOADING... (Click the icon to view the information.) Requirements 1. Estimate the cost function for delivery. 2. Can the constant in the cost function be used as an estimate of fixed delivery cost per month? Explain. Requirement 1. Estimate the cost function for delivery. Delivery costs = + ( × ) Delivery costs = + ( × ) Requirement 2. Can the constant in the cost function be used as an estimate of fixed delivery cost per month? Explain. The cost function in requirement 1 is an estimate of how costs behave ▼ outside of within the relevant range, not at cost levels ▼ outside within the relevant range. If there are no months with zero miles driven represented in the delivery cost…arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Excel Applications for Accounting PrinciplesAccountingISBN:9781111581565Author:Gaylord N. SmithPublisher:Cengage Learning
Cornerstones of Cost Management (Cornerstones Ser...AccountingISBN:9781305970663Author:Don R. Hansen, Maryanne M. MowenPublisher:Cengage Learning
Principles of Accounting Volume 2AccountingISBN:9781947172609Author:OpenStaxPublisher:OpenStax College
Excel Applications for Accounting Principles
Accounting
ISBN:9781111581565
Author:Gaylord N. Smith
Publisher:Cengage Learning
Cornerstones of Cost Management (Cornerstones Ser...
Accounting
ISBN:9781305970663
Author:Don R. Hansen, Maryanne M. Mowen
Publisher:Cengage Learning
Principles of Accounting Volume 2
Accounting
ISBN:9781947172609
Author:OpenStax
Publisher:OpenStax College
Responsibility Accounting| Responsibility Centers and Segments| US CMA Part 1| US CMA course; Master Budget and Responsibility Accounting-Intro to Managerial Accounting- Su. 2013-Prof. Gershberg; Author: Mera Skill; Rutgers Accounting Web;https://www.youtube.com/watch?v=SYQ4u1BP24g;License: Standard YouTube License, CC-BY