Question

In: Accounting

Please start from Manufacutring Overhead Budget and please include formulas on how you got that answer...

Please start from Manufacutring Overhead Budget and please include formulas on how you got that answer

This assignment asks you to set up an Excel budget spreadsheet file that automatically prepares the master budget for a company, given sales projections and information on beginning balances, production requirements, desired ending inventories, etc. Information on developing the budgets appears in Chapter 8 of your text, and examples of budget worksheets appear in the schedules throughout the chapter.

Data

Glamour Inc. produces and sells lady handbags. Below is information on its activities for the next few months.

Sales projections for the coming months are as follows:

Estimated Sales (in units)

April

May

June

July

Handbags

25,000

55,000

65,000

60,000

Actual sales in February were 30,000 units; actual sales in March were 45,000 units.

Each handbag’s selling price is $65/unit. Desired ending inventory of handbags is 5,000 units plus 10% of the following month’s projected sales. There are 7,500 units of handbags in inventory as of April 1.

2.         Estimated cash collections from sales of handbags to customers are as follows: 40% collected in the month of sale, 30% collected in the month following sale, 28% collected in the second month following sale, and 2% never collected.

3.         Three materials are used in the production of handbags: Rayon, Linen, and Leather. Materials requirements per unit of handbag are as follows: one unit of Rayon, 5 units of Linen, and 3 units of Leather.

Costs of materials are:

Rayon – $1.6/unit, Linen – $1.80/unit, Leather – $3.00/unit

Desired ending inventory of Rayon is 20% of the following month’s production need because it is sometimes in short supply. Desired ending inventory of Linen and Leather is 5% of the following month’s production need because they are easy to get. Inventories of materials as of April 1 are: 5,600 units of Rayon, 28,000 units of Linen, and 6,800 units of Leather.

4.         The company pays for materials purchases as follows: 60% in the month of purchase, 40% in the month following purchase. Total purchases of materials for the month of March were $350,000.

5.         There are two types of direct labor costs to incur before a handbag is completed. Direct labor costs are paid in cash as incurred.

            Department            Direct labor hours per unit of handbag          Cost per direct labor hour

            Cutting:                                   0.12 hour                                                     $20

            Sewing:                                     0.08 hour                                                    $25

6.         Total variable manufacturing overhead is estimated at $8/direct labor hour. Total fixed manufacturing overhead is estimated at $250,000/month, of which $80,000 is depreciation. Overhead costs are paid when incurred.

7.         Total variable selling and administrative costs are $1.50/unit of handbags sold. Total fixed selling and administrative costs are estimated at $350,000/month, of which $180,000 is depreciation. Selling and administrative costs are paid as the costs are incurred.

8.         The beginning balance of cash account on April 1 is $88,000. The company desires to maintain a minimum cash balance at the amount of $100,000.

9.         The company has an agreement with a local bank that would supply any amount of loan it needs to maintain the minimum cash balance. For simplicity, assume that interest is not compounded and that interest is paid each month but principal is paid at the year end. Annual interest rate is 12%.

10.       The company plans to purchase $5000 of equipment in April and $4,000 in May.

11.       Cash dividends is $1500 each month.

Preparation of Spreadsheet File

Create one Excel spreadsheet file consisting of the following six separate worksheets:

Sheet 1:           Data

This worksheet contains the data necessary to do all the other worksheets. List on this worksheet all of the data shown above, clearly labeled. None of the other worksheets should contain any numbers; they should contain ONLY FORMULAS – all cells on the other worksheets should be linked to cells in the data worksheet, cells within the same worksheet, or cells in the other worksheets. For example, the cell for direct labor cost for April should contain a formula that multiplies the production in units for April (from the production schedule) times the labor hours per unit (from the data worksheet) times the hourly wage rate (from the data worksheet). So if the sales estimate for handbags changes, you should be able to make the change only on the data worksheet; all the other worksheets should automatically adjust to the changes. The production amounts will change; the manufacturing costs will change, etc. The data worksheet can be in any format; just be sure to label each data item clearly.

Sheet 2:           Include the following two budgets on the second worksheet, clearly labeled:

                       

                                      Sales Budget: Prepare a schedule of sales revenue and cash receipts from sales for each of the months of April, May and June. List cash collections separately on lines as follows: cash collections from sales two month ago, cash collections from sales one month ago and cash collections from sales in the current month. Also, list total cash collections in April, May and June.

Production Budget: Prepare a production budget for handbags, in units, for each of the months of April, May and June.

Sheet 3:           Include the following one budget on the third worksheet, clearly labeled:

Direct Materials Budget: Prepare a direct materials purchases budget, in units and in total dollars, for April and May. List Rayon purchase costs, Linen purchase costs, Leather purchase costs and total material purchase costs separately. List payments for current month purchase, payments for prior month purchase, and total payments on separate lines.

Sheet 4:           Include the following one budget on the fourth worksheet, clearly labeled:

Direct Labor Budget: Prepare the budget for costs of direct labor used for April and May, in hours and in total dollars. List cutting cost, sewing cost, and total labor cost separately. Also, list total cash payments for each month.

Sheet 5:           Include the following two budgets on the fifth worksheet, clearly labeled:

Manufacturing Overhead Budget: Prepare a manufacturing overhead budget for April and May. Show variable manufacturing overhead, fixed manufacturing overhead and total manufacturing overhead costs separately for each month. Also, list total cash payments for each month.

Selling & Administrative Expense Budget: Prepare a selling and administrative expense budget for April and May. Show variable selling and administrative costs, fixed selling and administrative costs and total selling and administrative costs separately for each month. List total cash payments for each month.

Sheet 6:           Include the following one budget on the sixth worksheet, clearly labeled:

Cash Budget: Prepare a cash budget for April and May. List total amount of beginning cash balance, cash receipts, cash disbursements, cash excess or deficiency, borrowing at the beginning of the month, and ending cash balance separately.

Solutions

Expert Solution

Budgeted Production = Budgeted Sales + Desired Ending Inventory - Estimated Beginning Inventory.

April May June
Budgeted unit sales 25,000 55,000 65,000
Add: Desired Ending Inventory 10,500 11,500 11,000
Total Inventory Needed 35,500 66,500 76,000
Less: Estimated Beginning Inventory 7,500 10,500 11,500
Budgeted production in units 28,000 56,000 64,500

Direct labor hours required per handbag = 0.12 hours + 0.08 hours = 0.20 hours.

Variable manufacturing overhead = Labor hours required in production x $ 8.

Manufacturing Overhead Budget:

April May June
Labor Hours Needed in Production 5,600 11,200 12,900
Variable Manufacturing Overhead $ 44,800 $ 89,600 $ 103,200
Fixed cash manufacturing overhead 170,000 170,000 170,000
Cash Manufacturing Overhead 214,800 259,600 273,200
Depreciation 80,000 80,000 80,000
Total Budgeted Manufacturing Overheads 294,800 339,600 353,200

Related Solutions

Please explain how you got the answer thank you. • Task 2.1: SQL Injection Attack from...
Please explain how you got the answer thank you. • Task 2.1: SQL Injection Attack from webpage. Your task is to log into the web application as the administrator from the login page, so you can see the information of all the employees. We assume that you do know the administrator’s account name which is admin, but you do not know the ID or the password. You need to decide what to type in the Employee ID and Password fields...
Please show how work on how you got this answer. Q1: What will be the pressure...
Please show how work on how you got this answer. Q1: What will be the pressure (in mmHg) inside of a 50.0 L container that holds 7.00 moles of hydrogen gas at 0.0 C? Q2: Oxygen Gass is produced for use in a small-scale experiment by the catalytic decomposition of hydrogen peroxide: 2 H2O2 (aq) -------------------> 2 H2O (l) + O2 (g) If 50.0 mL of a 1.00 M solution of H2O2 completely decomposes and what volume of dry oxygen...
There are 3 questions. You must show your work and how you got the answer. PLEASE...
There are 3 questions. You must show your work and how you got the answer. PLEASE DO NOT ANSWER IF YOU ARE NOT SURE You have been asked to look at production options for the Android01, since production methods and allocation of costs have implications for cost per unit. Two alternative methods of production are being considered. Begin by gathering data (using financial information in decision making), then determine the suitability of the project. The production of Android01 will share...
*Please show work on how you got the answer for any calclualtions in Income Statemnet and...
*Please show work on how you got the answer for any calclualtions in Income Statemnet and the Balance sheet. (Also I could not fit the entire Balance sheet chart on it, so it is suppose to be longer) Hillyard Company, an office supplies specialty store, prepares its master budget on a quarterly basis. The following data have been assembled to assist in preparing the master budget for the first quarter a. As of December 31 (the end of the prior...
(Please show work so I can understand how you got to the answer - Thank you...
(Please show work so I can understand how you got to the answer - Thank you very much ) Via Gelato is a popular neighborhood gelato shop. The company has provided the following data concerning its operations: Fixed Element per Month Variable Element per Liter Actual Total for June Revenue $ 13.00 $ 72,540 Raw materials $ 4.75 $ 30,330 Wages $ 5,700 $ 1.50 $ 14,560 Utilities $ 1,730 $ 0.30 $ 3,800 Rent $ 2,700 $ 2,700 Insurance...
Please do not just give the answer. Please also explain how you got them. Thanks! 1....
Please do not just give the answer. Please also explain how you got them. Thanks! 1. Which of the following is not a valid method of applying LCNRV: A. logical categories of inventory (i.e. product line) B. the entire inventory C. inventory items to be sold within the next year D. individual inventory items E. None of the answer choices are correct 2. Which of the following would not require the company to account for the change retrospectively? A. From...
Please slove showing steps and how you got the answer. A market has the following supply...
Please slove showing steps and how you got the answer. A market has the following supply and demand curves P = 10 + 3Q P = 200 - 7Q What is the deadweight loss of a $50 per unit tax on this market?
For each question, please explain how you got the answer. 1. The major function of RNA...
For each question, please explain how you got the answer. 1. The major function of RNA polymerase's sigma factor is A) recognition of the translational stop sequence B) recognition of the transcriptional start sequence C) recognition of the transcriptional stop sequence D) recognition of the translational start sequence E) None of these are correct 2. WHere is the amino acid attached to a tRNA molecule? A) 3′-hydroxyl of an adenine containing residue of 3’ end of tRNA B) 5′-hydroxyl of...
**please provide breakdown of how you got the answer. thanks The following information concerns production in...
**please provide breakdown of how you got the answer. thanks The following information concerns production in the Baking Department for March. All direct materials are placed in process at the beginning of production. ACCOUNT Work in Process—Baking Department ACCOUNT NO. Date Item Debit Credit Balance Debit Credit Mar. 1 Bal., 4,200 units, 4/5 completed 10,080 31 Direct materials, 75,600 units 136,080 146,160 31 Direct labor 38,800 184,960 31 Factory overhead 21,824 206,784 31 Goods finished, 76,500 units 198,732 8,052 31...
Please describe in detail how you got the answer. Give a regular expression which describes the...
Please describe in detail how you got the answer. Give a regular expression which describes the set of strings over {a, b, c} in which every a is immediately followed by at least one c.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT