master budget .

Objective: The objective of this individual Master Budget Assignment is to motivate the students' interest in Management Accounting through hands-on experience. This assignment will allow you to work through an entire master budget using data that is simple but still resembles a real-life, manufacturing operation. Purpose: The purpose of using the Master Budget is to give you a better understanding of the process through which the master budget is integrated. This Assignment will help you to apply the fundamental of Management Accounting theories for the benefit of your career and to enrich your Accounting learning experience. Submission: An electronic Excel file must be submitted via Blackboard. Google sheets, Numbers, screenshot or PDF files won't be accepted. As a student, you have access to a free excel download. If you have questions about how to download excel, please contact Information Technology at it@trentu.ca Messy documents won't be graded. Benefits to use excel a. Link as many of the numbers as possible. b. Use Excel calculation tools as much as possible. c. Other types of files, e.g., pages, google sheets won't be accepted. d. Proper formatting should be used so the budgets will look professional. e. Look under the word "format" in the toolbar to find the correct way to underline accounting numbers (single and double). f. Do not include decimal places in your larger numbers but make allowances for rounding by the Excel program. Guitarra Inc Guitarra Inc is a worldwide wooden musical part manufacturing firm based in North America. After many years in the market, the Peterborough division, which produces one product called "Guitarra Stand" reaches a spike on the sales level. The Management wants to have a significant investment to expand the facility and increase production, but it is requesting from the division to prepare a budget for the second quarter of 2021. The actual sales from 2021 are February 92,000 units March 104,000 units The division manager projected the following sales: April 2021 112,000 units May 2021 117,000 units June 2021 122,000 units July 2021 137,000 units August 2021 162,000 units The selling price is $13 per unit 2 2 of 5 1. From previous experience, management has determined that finished goods ending inventory equal to 20% of the next month's unit sales is required to fit the buyer's demands. 2. The Guitarra Stand requires one types of raw materials: Plastic Each Guitarra Stand requires 1.2 kilograms of Plastic, at a cost of $1.5 per kilogram. The supplier of Plastic tends to be somewhat erratic so Guitarra Inc finds it necessary to maintain an inventory balance equal to 20% of the material needed for the next month as a precaution against stock-outs. The direct material on March 31, is 27,120 Kg. 3. The beginning accounts payable will consist of $125,400. 4. Guitarra Inc pays for 50% of a month's purchases in the month of purchase and 50% in the following month. 5. The manufacturing overhead in based on direct labor hours. The workers receive an average of $16.00 per hour including employee benefits. Each Guitarra Stand takes 15 minutes to complete. 6. Guitarra Inc allocate the manufacturing overhead based on direct labor hours; The variable manufacturing overhead is as follow: Maintenance $0.40; Utilities $0.60; Indirect Labor $0.60; Indirect materials $0.40 7. The Monthly Fixed manufacturing overhead costs are as follows: Janitorial $2,200 Insurance $1,450 Depreciation $14,200 Property Taxes $2,000 Salaries $44,000 8. The Monthly Selling and administrative expenses are Salaries $62,000 Other fixed cost $3,600 Insurance $1,100 Depreciation $2,800 Advertising $12,000 9. Sales are on account (credit), 50% of the sales are collected during the month of sales and 50% the following month. This was the same collection pattern as in previous years. 10. The company wants to maintain at the end of each month, a minimum bank balance of $500,000. In case the company has a deficiency of money or it is not able to reach this minimum bank balance, the company can borrow from a line of credit at the same bank at the rate of 3% per annum. All borrowing is considered to happen on the first day of the month, repayments are on the last day of the month. All borrowings and repayments from the bank should be in multiples of $1,000. #pays interest only on the portion of the loan that it repays the month. Interest is calculated on the balance at the beginning of the month, which includes any amounts borrowed that month (see video related to this calculation). The cash balance on March 31 is $145,000. 11. In May, $680,000 of new equipment to update operations will be purchased 12. Three months' insurance is prepaid on the first day of the first month of the quarter. Required: Prepare the budget for 2021, the second quarter, for Guitarra Inc for the including the following schedules: Prepare the following Schedules a) Prepare the Sales Budget for the second quarter (textbook Illustration 10.3) b) Prepare the Schedule of Expected Collection from Customers for the second quarter (Illustration 10.17) c) Prepare the Production Budget for the second quarter (Illustration 10.5) d) Prepare the Direct Materials Budget for the second quarter (Illustration 10.9) e) Prepare the expected Direct Material Cash Disbursements for the second quarter (Illustration 10.18) f) Prepare the Direct Labour Budget for the second quarter (Illustration 10.11) 8) Prepare the Manufacturing Overhead Budget for the second quarter (Illustration 10.12) h) Prepare the Selling and Administrative Expenses Budget for the second quarter (Illustration 10.13) i) Prepare the Cash Budget for the second quarter (Illustration 10.19) Note: Some of the textbook illustrations are incomplete. You need to be sure that your schedules are complete. See the example below, textbook illustration vs PowerPoint Incomplete schedules will be penalized. Review and follow the PowerPoint slides. Textbook illustration ENTRATION TRE Power Point slides HAYES COMPANY HAVES COMPANY You need to Complete the year column Production budget The master budget should be reporting using one excel tab, see below AutoSave Home Insert Draw Page Layout Formulas Data Default I keep Exit New 15 Options Picture 4 fx Normal Page Break Page Preview Layout ILTRATION 6:10 MATES COMPANY ILLUSTRATION IRID HAYES COMPANY De her ILLUSTRATION 10.12 - Maatatagreat huge HAYES COMPANY Mouring what Indir 12 11 Master budget + Objective: The objective of this individual Master Budget Assignment is to motivate the students' interest in Management Accounting through hands-on experience. This assignment will allow you to work through an entire master budget using data that is simple but still resembles a real-life, manufacturing operation. Purpose: The purpose of using the Master Budget is to give you a better understanding of the process through which the master budget is integrated. This Assignment will help you to apply the fundamental of Management Accounting theories for the benefit of your career and to enrich your Accounting learning experience. Submission: An electronic Excel file must be submitted via Blackboard. Google sheets, Numbers, screenshot or PDF files won't be accepted. As a student, you have access to a free excel download. If you have questions about how to download excel, please contact Information Technology at it@trentu.ca Messy documents won't be graded. Benefits to use excel a. Link as many of the numbers as possible. b. Use Excel calculation tools as much as possible. c. Other types of files, e.g., pages, google sheets won't be accepted. d. Proper formatting should be used so the budgets will look professional. e. Look under the word "format" in the toolbar to find the correct way to underline accounting numbers (single and double). f. Do not include decimal places in your larger numbers but make allowances for rounding by the Excel program. Guitarra Inc Guitarra Inc is a worldwide wooden musical part manufacturing firm based in North America. After many years in the market, the Peterborough division, which produces one product called "Guitarra Stand" reaches a spike on the sales level. The Management wants to have a significant investment to expand the facility and increase production, but it is requesting from the division to prepare a budget for the second quarter of 2021. The actual sales from 2021 are February 92,000 units March 104,000 units The division manager projected the following sales: April 2021 112,000 units May 2021 117,000 units June 2021 122,000 units July 2021 137,000 units August 2021 162,000 units The selling price is $13 per unit 2 2 of 5 1. From previous experience, management has determined that finished goods ending inventory equal to 20% of the next month's unit sales is required to fit the buyer's demands. 2. The Guitarra Stand requires one types of raw materials: Plastic Each Guitarra Stand requires 1.2 kilograms of Plastic, at a cost of $1.5 per kilogram. The supplier of Plastic tends to be somewhat erratic so Guitarra Inc finds it necessary to maintain an inventory balance equal to 20% of the material needed for the next month as a precaution against stock-outs. The direct material on March 31, is 27,120 Kg. 3. The beginning accounts payable will consist of $125,400. 4. Guitarra Inc pays for 50% of a month's purchases in the month of purchase and 50% in the following month. 5. The manufacturing overhead in based on direct labor hours. The workers receive an average of $16.00 per hour including employee benefits. Each Guitarra Stand takes 15 minutes to complete. 6. Guitarra Inc allocate the manufacturing overhead based on direct labor hours; The variable manufacturing overhead is as follow: Maintenance $0.40; Utilities $0.60; Indirect Labor $0.60; Indirect materials $0.40 7. The Monthly Fixed manufacturing overhead costs are as follows: Janitorial $2,200 Insurance $1,450 Depreciation $14,200 Property Taxes $2,000 Salaries $44,000 8. The Monthly Selling and administrative expenses are Salaries $62,000 Other fixed cost $3,600 Insurance $1,100 Depreciation $2,800 Advertising $12,000 9. Sales are on account (credit), 50% of the sales are collected during the month of sales and 50% the following month. This was the same collection pattern as in previous years. 10. The company wants to maintain at the end of each month, a minimum bank balance of $500,000. In case the company has a deficiency of money or it is not able to reach this minimum bank balance, the company can borrow from a line of credit at the same bank at the rate of 3% per annum. All borrowing is considered to happen on the first day of the month, repayments are on the last day of the month. All borrowings and repayments from the bank should be in multiples of $1,000. #pays interest only on the portion of the loan that it repays the month. Interest is calculated on the balance at the beginning of the month, which includes any amounts borrowed that month (see video related to this calculation). The cash balance on March 31 is $145,000. 11. In May, $680,000 of new equipment to update operations will be purchased 12. Three months' insurance is prepaid on the first day of the first month of the quarter. Required: Prepare the budget for 2021, the second quarter, for Guitarra Inc for the including the following schedules: Prepare the following Schedules a) Prepare the Sales Budget for the second quarter (textbook Illustration 10.3) b) Prepare the Schedule of Expected Collection from Customers for the second quarter (Illustration 10.17) c) Prepare the Production Budget for the second quarter (Illustration 10.5) d) Prepare the Direct Materials Budget for the second quarter (Illustration 10.9) e) Prepare the expected Direct Material Cash Disbursements for the second quarter (Illustration 10.18) f) Prepare the Direct Labour Budget for the second quarter (Illustration 10.11) 8) Prepare the Manufacturing Overhead Budget for the second quarter (Illustration 10.12) h) Prepare the Selling and Administrative Expenses Budget for the second quarter (Illustration 10.13) i) Prepare the Cash Budget for the second quarter (Illustration 10.19) Note: Some of the textbook illustrations are incomplete. You need to be sure that your schedules are complete. See the example below, textbook illustration vs PowerPoint Incomplete schedules will be penalized. Review and follow the PowerPoint slides. Textbook illustration ENTRATION TRE Power Point slides HAYES COMPANY HAVES COMPANY You need to Complete the year column Production budget The master budget should be reporting using one excel tab, see below AutoSave Home Insert Draw Page Layout Formulas Data Default I keep Exit New 15 Options Picture 4 fx Normal Page Break Page Preview Layout ILTRATION 6:10 MATES COMPANY ILLUSTRATION IRID HAYES COMPANY De her ILLUSTRATION 10.12 - Maatatagreat huge HAYES COMPANY Mouring what Indir 12 11 Master budget +