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?
-
A parallel-plate capacitor consists of square plates of edge length that are separated by a distance d, where d < < . A potential difference V is maintained between the plates. A material of...
-
The external auditors at Ernst and Whinney succumbed to which part of the fraud triangle? a. Opportunity b. Incentive pressure c. Rationalization d. A and C
-
Boswell Enterprises Ltd is reviewing its trade credit policy. The business, which sells all of its goods on credit, has estimated that sales revenue for the forthcoming year will be 3 million under...
-
Nate Smith and Darla Jones have written a computer program for a video game that may rival Playstation and Xbox. They need additional capital to market the product, and they plan to incorporate their...
-
70 99 a) Enter the exterior angles for this quadrilateral in the table and calculate the sum. b) Drag the points to create two new quadrilaterals and complete the table for both of these. c) Move to...
-
For the partially submerged backfill in Problem 13.13 (Figure 13.37), determine the Rankine's passive force per unit length of the wall and the location of the resultant In problem 13 Problem HH Y1 ...
-
Call center costs: Call-related Call minute-related Total call center costs Total $ 222,200 111,300 $ 333,500 The call center at Westover uses a two-stage cost allocation system: It uses the number...
-
How do matrix organizational structures integrate cross-functional collaboration and manage the complexities inherent in dual reporting relationships ?
-
How are the traditional competitors positioned against these market forces? What are their strengths? What are there vulnerabilities?
-
how might distance learning be designed and used to avoid some of the learning and transfer of training problems of the traditional lecture methods?
-
For JP Morgan Chase explain the companies current competitive position in the industry of retail banking. Along with 3 strategic issues the company must address and why they are strategic issues.
-
November December January February March Sales $9,500 $10,000 $11,000 $12,100 $13,310 All sales are for credit, with 80% collected the following month and 20% collected during the second month....
-
Harmon Recycling Services (HRS), a not-for-profit organization,has two drop-off centers, Westside and Eastside. Data for theexpected operation in the next quarter follow.EastsideWestsideTotal 2...
-
Refrigerant R-12 at 30C, 0.75 MPa enters a steady flow device and exits at 30C, 100 kPa. Assume the process is isothermal and reversible. Find the change in availability of the refrigerant.
-
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...
-
The standard costs for a manufacturing business are \($12\) per unit for direct materials, \($8\) per unit for direct labour, and \($5\) per unit for manufacturing overhead. The sales projection is...
-
What is Munchs expected profit for April? a. $192,500 b. $56,000 c. $66,000 d. $59,000 Munch Enterprises makes a small toy car that is voice activated. Projected sales for the next four months are...
-
Bobby Jones Inc. is planning sales of 55,000 units for the next three months. The company has a beginning inventory of 10,000 units and would like to have an ending inventory of 15,000 at the end of...
Study smarter with the SolutionInn App