these two seem like two questions but it's just one big question. Guidance Corp is preparing its
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Guidance Corp is preparing its Master Budget for 20XX. You need to prepare Guidance's Direct Materials Budget and Schedule of Cash Disbursements for Materials Purchases on the excel template provided and create a function that allows management to enter a month and get amount of materials purchased and cash spent for that month. The given information is already input into the spreadsheet. REQUIRED 1. 2. PREPARE A DIRECT MATERIALS BUDGET (3 POINTS) Prepare the Direct Materials Budget for the first three months of Guidance's fiscal year (January, February, and March), along with the full quarter. Relevant information Required Production o January 90,000 units February 70,000 units March = 125,000 units o o o April = 130,000 units Pounds of materials needed per unit = 3 pounds Beginning raw materials inventory on January 1, 20XX= 50,000 pounds. Percentage of the following month's production used to determine desired ending inventory = 20% Raw materials cost per pound = $2.5 per pound PREPARE A SCHEDULE OF CASH PAYMENTS FOR RAW MATERIALS (4 POINTS) Prepare the Schedule of Cash Payments for the first three months of Guidance's fiscal year (January, February, and March) and for the quarter, using the template in the Excel spreadsheet. HINT: cost of raw materials that Guidance is paying for comes from the last line of your direct materials budget. Guidance's payment schedule: o 20% of purchases are paid for in the month of purchase o 65% are paid for in the month after purchase 15% are paid for in the second month after purchase Beginning accounts payable = $600,000. Assume 65% of the beginning accounts payable will be paid in January and 15% in February. o Check Figures Because this problem has several areas that frequently pose problems for students, check figures are provided to give you feedback as to whether your budget/schedule has errors. January Cost of raw materials to be purchased $655,000 Total Cash Disbursements for Materials $521,000 February March $607,500 $945,000 $637,250 $682,125 Quarter $2,207,500 $1,840,375 3. Create a INDEX and MATCH nested function so that the user can enter a month ('January', 'February', 'March' or 'Quarter') and excel returns the amount Direct Materials purchases for that month in cell B4 and how much cash is being spent in C4 on sheet "3) Management Lookup". (2 POINTS) Guidance Corp is preparing its Master Budget for 20XX. You need to prepare Guidance's Direct Materials Budget and Schedule of Cash Disbursements for Materials Purchases on the excel template provided and create a function that allows management to enter a month and get amount of materials purchased and cash spent for that month. The given information is already input into the spreadsheet. REQUIRED 1. 2. PREPARE A DIRECT MATERIALS BUDGET (3 POINTS) Prepare the Direct Materials Budget for the first three months of Guidance's fiscal year (January, February, and March), along with the full quarter. Relevant information Required Production o January 90,000 units February 70,000 units March = 125,000 units o o o April = 130,000 units Pounds of materials needed per unit = 3 pounds Beginning raw materials inventory on January 1, 20XX= 50,000 pounds. Percentage of the following month's production used to determine desired ending inventory = 20% Raw materials cost per pound = $2.5 per pound PREPARE A SCHEDULE OF CASH PAYMENTS FOR RAW MATERIALS (4 POINTS) Prepare the Schedule of Cash Payments for the first three months of Guidance's fiscal year (January, February, and March) and for the quarter, using the template in the Excel spreadsheet. HINT: cost of raw materials that Guidance is paying for comes from the last line of your direct materials budget. Guidance's payment schedule: o 20% of purchases are paid for in the month of purchase o 65% are paid for in the month after purchase 15% are paid for in the second month after purchase Beginning accounts payable = $600,000. Assume 65% of the beginning accounts payable will be paid in January and 15% in February. o Check Figures Because this problem has several areas that frequently pose problems for students, check figures are provided to give you feedback as to whether your budget/schedule has errors. January Cost of raw materials to be purchased $655,000 Total Cash Disbursements for Materials $521,000 February March $607,500 $945,000 $637,250 $682,125 Quarter $2,207,500 $1,840,375 3. Create a INDEX and MATCH nested function so that the user can enter a month ('January', 'February', 'March' or 'Quarter') and excel returns the amount Direct Materials purchases for that month in cell B4 and how much cash is being spent in C4 on sheet "3) Management Lookup". (2 POINTS)
Expert Answer:
Answer rating: 100% (QA)
1 January Total pounds of raw materials needed 90000 units 3 pounds 270000 pounds Desired ending raw materials inventory 70000 units 3 pounds 20 42000 pounds Pounds of materials to be purchased 270000 ... View the full answer
Related Book For
Posted Date:
Students also viewed these accounting questions
-
The journal entry to record a return of merchandise purchased on account under a perpetual inventorysystem would credit a. Accounts Payable. b. Purchase Returns and Allowances. c. Sales Revenue. d....
-
Damon Manufacturing is preparing its master budget for the first quarter of the upcoming year. The following data pertain to Damon Manufacturing's operations: Current Assets as of December 31 (prior...
-
Presidio Manufacturing is preparing its master budget for the first quarter of the upcoming year. The following data pertain to Presidio Manufacturing's operations: Current assets as of December 31...
-
In subsurface pipe drainage, describe the different types and materials used for the drainage?
-
What is it that is missing from Joness allegations?
-
A library database records the authors and the publisher of each book. Normalize the following relation and indicate the primary and foreign key attribute types: R (ISBN, title, author(name,...
-
The numbers of extra classes taken per week by a sample of 32 students Use technology to draw a box-and-whisker plot that represents the data set. 561 2 4 8 7 6 5 1 3 1 5 8 28 1 9 1 3 27 34 2 3 2 1 2...
-
Madison Seniors Care Center is a nonprofit organization that provides a variety of health services to the elderly. The center is organized into a number of departments, one of which is the...
-
Task 1: Create a JavaBean Coffee Bean with the following: - Data members: int numSugar; double price; String typeCoffee; Methods: Constructor Accessor Mutator Task 2: Create a HTML CoffeeOrder.html...
-
Complete Form 941 for the 4th quarter for TCLH Industries (which is located at 202 Whitmore Avenue, Durham, NC 27701; Employer Identification #44-4444444). Assume that all necessary deposits were...
-
Use C++ language to develop an original implementation of a menu-driven program performing a number of tasks relating to students? details: displaying all students? details, adding new student to the...
-
1. Prepare a production budget for the first quarter of the year. Show the number of bottles that should be produced each month as well as for the quarter in total. Pets Plus, Inc. Production Budget...
-
In this programming project, you are to use Code 12 to write a game with specific given requirements. There is also room for creativity and adding extras, but most of your grade will be based on...
-
1. A 5-kg object moves around a circular track with a radius of 18 cm at a constant speed of 6 m/s. Find (a) The magnitude and direction of the acceleration of the object. (b) The net force acting...
-
His synagogue (by check) The Republican Party (by check) The American Red Cross (by credit card) $4,600 400 1,000 His fraternal organization for tickets to a holiday party 100 A baseball autographed...
-
A 1300 kg car has a speed of 24 m/s. If it takes 7 s to stop the car, what is the impulse and the average force acting on the car? impulse average force N-s N Additional Materials
-
It costs Camp, Inc. $65 per unit to manufacture 1,000 units per month of a product that it can sell for $103 each. Alternatively, Camp could process the units into a more complex product, which would...
-
(8%) Problem 6: A student attaches a f= 3.5 kHz oscillator to one end of a metal rail of length L = 25 m. The student turns on the oscillator and uses a piezoelectric gauge at the other end to...
-
Terry medical Supplies is a retailer of home medical equipment. Last year, Terrys sales revenues totaled $ 6,100,000. Total expenses were $ 2,590,000. Of this amount, approximately $ 1,342,000 were...
-
Gary McKnight is evaluating a business opportunity to sell grooming kits at dog shows. Gary can buy the grooming kits at a wholesale cost of $ 32 per set. He plans to sell the grooming kits for $ 62...
-
Sparkle Unlimited is a costume jewelry manufacturer located in the United States that uses electroplating. Electroplating is a process that involves applying a decorative metal coating to a base...
-
Explain the term Energy. Discuss its various forms.
-
Explain cyclic and quasi static process.
-
Explain the terms : state, phase, process and cyclic process.
Study smarter with the SolutionInn App