You are the accountant at Wesleyan Technology Company (WTC) in the Fall of 2020. Your boss,...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
You are the accountant at Wesleyan Technology Company (WTC) in the Fall of 2020. Your boss, the controller, has asked you to take another pass at the 3d Quarter Budget for 2021 as she is not sure the numbers are correct. She sends you the attached Excel file, providing the necessary information to construct the budget from scratch. Your job is to take the information provided on the Info tab in Excel file and put together the Budget for 3Q, 2021 on the Input tab. You have until May 12t to get this done. Assumptions for Wesleyan Technology Company (WTC) on preparing a budget for 3rd Quarter, 2021 Budgeted Sales (in units) Luly September 60,000 October November 20,000 August 40,000 45,000 35,000 Per Unit Selling Price - $1200 Cash Collections WTC sales are all on account with the following pattern of collection is: 65% Collected in the month of the sale 30% Collected in the month following the sale 5% Uncollectible June's sales were $360,000 Production Budget Ending inventory is to be equal to 15% of the following month's budgeted sales. Direct Materials 10 pounds of material is required per unit of product 10% of the following month's production for Materials on hand at month end $0.25 cost per pound of material Cash Dishursement for Materials WTC paid for purchases as follows: 55% is paid in the month the materials are purchased 45% is paid in the following month June 30 Accounts Payable balance of $33,187.50 is to be paid in full in July. Direct Labor Part of an hour of direct labor to produce one unit of product WTC has a "no layoff" policy so all employees are paid for 40 hours of work each week The hourly rate per hour for regular time employees The minimum number of hours to be paid each month 0.1 (or 6 minutes) $15 3,000 Manufacturing Overhead Manufac turing overhead is applied to units of product based on direct labor hours The viable overhead rate per direct hour Fixed Manufacturing Overhead per month Non-cash costs included in Fixed Manaufacturing Overthead $25 $75,000 $25,000 Selling and Administrative Espenses WTC has both fixed and variable selling and administrative expenses Rate per unit for Variable Selling and Administrative expenses Fixed Selling and Administranve expenses per month Non-cash costs included in Fixed Selling and Administranve expenses $0.25 $75,000 $15,000 Cash Budget Maintain a minimam month end cash balance of $4000 Maintains a 15% open line of credit for $125000 Borrowing happens on the first day of the month Repayments are made on the last day of the month Cash dividend paid in August Equipment purchases made in Q3 2021 Equipment purchased with cash in July Equipment purchased with cash in Septembert June 30 ending cash balance was $40000 $60000 $55,000 $150,000 Budgeted Financial Stamenta WTC Balance Sheet as of June 30 showed the following balances Accounts Receivable $137627.00 Equipment Land $195,000.00 $500000 Common Stock $000000 Retained Eamings $240,567,00 CH8 Budget Worksheets Assumptions for Wesleyan Technology Company (WTC) on preparing a budget for 3rd Quarter, 2021 Construct the sales budget Quarter 3, 2021 August July September Total Budgeted unit sales Selling price per unit Total sales Construct Expected Cash Collections Budget Quarter 3, 2021 August July September Toul A/R - 30Jun Total Cash Collections Construct Production Budget Quarter 3, 2021 August September July Quarter Budgeted Sales Add: Desired Ending Inventory Total Needs Less Beginning Inventory Required Production Construct Direct Materials Budget Quarter 3, 2021 August July September Quarter Production Materials per units (bs.) Production Needs Add: Desired ending inventory Total Needed Less Beginning Inventory Matenals to be purchased Cost Ibs per Materials to be purchased - $ CH8 Budget Worksheets Construct Expected Cash Disbursements Budget Quarter 3, 2021 September July August Total A/P - 30Jun Total Cash Disbursements Construct Direct Labor Budget Quarter 3, 2021 August July September Total Units of Production Direct Labor per unit Labor hours required Guarantmd Hours Labor Hours Paid Hourly Rate Total Direct Labot Costs Construct Manufacturing Overhead Budget Quarter 3, 2021 September July August Total Budgeted DLH Variable MOH rate Variable MOH coss Fixed MOH costs Total MOH costs Predetermined Overhead Rate (POHR) Total MOH costs Less noncash costs Cash disbursements for MOH Construct Ending Finished Goods Inventory Budget Quarter 3, 2021 Production costs per unit Quantiry Cost Total Direct Materials Ibs. Direct Labor hrs hrs Manufachunng Overhead Product Cost per Unit Budgeted Finished Goods Inventory Ending inventory in units Unit product cost Ending finished goods inventory CH8 Budget Worksheets Construct Selling and Administrative Expense Budget Quarter 3, 2021 September July August Total Budgeted Sales Variable S & A rate Variable expenses Fixed S & A costs Total S & A costs Less: noncash costs Cash S & A Expenses Construct Cash Budget Quarter 3, 2021 July August September Total Beginning Cash Balance Add: Cash collections Total cash available Less: Cash disbursements Materials Direct labor Manufacturing 0/H Selling and Admin Equipment purchase Dividend Total disbursements Excess (Deficiency) Financing Borrowing Repayments Interest Total financing Ending Cash Balance CH8 Budget Worksheets Budgeted Income Statement Sales Cost of Goods Sold Gross Margin Selling and Administrative expenses Operating Income Interest expense Net Income Construct Budgeted Income Statement Budgeted Balance Sheet ASSETS: Cash Accounts receivable Raw materials inventory Finished goods inventory Land Equipment Total Assets LIABILITIES AND STOCKHOLDERS' EQUITY: Accounts Payable Notes Payable Common Stock Retained earnings Total Liabilities and Stockholders' Equity You are the accountant at Wesleyan Technology Company (WTC) in the Fall of 2020. Your boss, the controller, has asked you to take another pass at the 3d Quarter Budget for 2021 as she is not sure the numbers are correct. She sends you the attached Excel file, providing the necessary information to construct the budget from scratch. Your job is to take the information provided on the Info tab in Excel file and put together the Budget for 3Q, 2021 on the Input tab. You have until May 12t to get this done. Assumptions for Wesleyan Technology Company (WTC) on preparing a budget for 3rd Quarter, 2021 Budgeted Sales (in units) Luly September 60,000 October November 20,000 August 40,000 45,000 35,000 Per Unit Selling Price - $1200 Cash Collections WTC sales are all on account with the following pattern of collection is: 65% Collected in the month of the sale 30% Collected in the month following the sale 5% Uncollectible June's sales were $360,000 Production Budget Ending inventory is to be equal to 15% of the following month's budgeted sales. Direct Materials 10 pounds of material is required per unit of product 10% of the following month's production for Materials on hand at month end $0.25 cost per pound of material Cash Dishursement for Materials WTC paid for purchases as follows: 55% is paid in the month the materials are purchased 45% is paid in the following month June 30 Accounts Payable balance of $33,187.50 is to be paid in full in July. Direct Labor Part of an hour of direct labor to produce one unit of product WTC has a "no layoff" policy so all employees are paid for 40 hours of work each week The hourly rate per hour for regular time employees The minimum number of hours to be paid each month 0.1 (or 6 minutes) $15 3,000 Manufacturing Overhead Manufac turing overhead is applied to units of product based on direct labor hours The viable overhead rate per direct hour Fixed Manufacturing Overhead per month Non-cash costs included in Fixed Manaufacturing Overthead $25 $75,000 $25,000 Selling and Administrative Espenses WTC has both fixed and variable selling and administrative expenses Rate per unit for Variable Selling and Administrative expenses Fixed Selling and Administranve expenses per month Non-cash costs included in Fixed Selling and Administranve expenses $0.25 $75,000 $15,000 Cash Budget Maintain a minimam month end cash balance of $4000 Maintains a 15% open line of credit for $125000 Borrowing happens on the first day of the month Repayments are made on the last day of the month Cash dividend paid in August Equipment purchases made in Q3 2021 Equipment purchased with cash in July Equipment purchased with cash in Septembert June 30 ending cash balance was $40000 $60000 $55,000 $150,000 Budgeted Financial Stamenta WTC Balance Sheet as of June 30 showed the following balances Accounts Receivable $137627.00 Equipment Land $195,000.00 $500000 Common Stock $000000 Retained Eamings $240,567,00 CH8 Budget Worksheets Assumptions for Wesleyan Technology Company (WTC) on preparing a budget for 3rd Quarter, 2021 Construct the sales budget Quarter 3, 2021 August July September Total Budgeted unit sales Selling price per unit Total sales Construct Expected Cash Collections Budget Quarter 3, 2021 August July September Toul A/R - 30Jun Total Cash Collections Construct Production Budget Quarter 3, 2021 August September July Quarter Budgeted Sales Add: Desired Ending Inventory Total Needs Less Beginning Inventory Required Production Construct Direct Materials Budget Quarter 3, 2021 August July September Quarter Production Materials per units (bs.) Production Needs Add: Desired ending inventory Total Needed Less Beginning Inventory Matenals to be purchased Cost Ibs per Materials to be purchased - $ CH8 Budget Worksheets Construct Expected Cash Disbursements Budget Quarter 3, 2021 September July August Total A/P - 30Jun Total Cash Disbursements Construct Direct Labor Budget Quarter 3, 2021 August July September Total Units of Production Direct Labor per unit Labor hours required Guarantmd Hours Labor Hours Paid Hourly Rate Total Direct Labot Costs Construct Manufacturing Overhead Budget Quarter 3, 2021 September July August Total Budgeted DLH Variable MOH rate Variable MOH coss Fixed MOH costs Total MOH costs Predetermined Overhead Rate (POHR) Total MOH costs Less noncash costs Cash disbursements for MOH Construct Ending Finished Goods Inventory Budget Quarter 3, 2021 Production costs per unit Quantiry Cost Total Direct Materials Ibs. Direct Labor hrs hrs Manufachunng Overhead Product Cost per Unit Budgeted Finished Goods Inventory Ending inventory in units Unit product cost Ending finished goods inventory CH8 Budget Worksheets Construct Selling and Administrative Expense Budget Quarter 3, 2021 September July August Total Budgeted Sales Variable S & A rate Variable expenses Fixed S & A costs Total S & A costs Less: noncash costs Cash S & A Expenses Construct Cash Budget Quarter 3, 2021 July August September Total Beginning Cash Balance Add: Cash collections Total cash available Less: Cash disbursements Materials Direct labor Manufacturing 0/H Selling and Admin Equipment purchase Dividend Total disbursements Excess (Deficiency) Financing Borrowing Repayments Interest Total financing Ending Cash Balance CH8 Budget Worksheets Budgeted Income Statement Sales Cost of Goods Sold Gross Margin Selling and Administrative expenses Operating Income Interest expense Net Income Construct Budgeted Income Statement Budgeted Balance Sheet ASSETS: Cash Accounts receivable Raw materials inventory Finished goods inventory Land Equipment Total Assets LIABILITIES AND STOCKHOLDERS' EQUITY: Accounts Payable Notes Payable Common Stock Retained earnings Total Liabilities and Stockholders' Equity
Expert Answer:
Related Book For
Fundamentals of Financial Management
ISBN: 9780273713630
13th Revised edition
Authors: James van Horne, John Wachowicz
Posted Date:
Students also viewed these accounting questions
-
What would be the price per bond in Problem 1 if interest payments were made semiannually?
-
What would be the best choice in Worked-Out Problem 3.1 (on page 70) if you are already committed to paying an additional $1,500 for parts your mechanic has ordered? What would your net benefit be?
-
What would be the biggest problem in implementing an ERP in your department?
-
(1 point) Solve each equation for x: (A) Solve 4(x+4) = 8 for x. X = 1 (B) Solve In x + ln(x 4) = 2 for x. X =
-
Raw R2 for the regression-through-the-origin model. As noted earlier, for the regression-through-the-origin regression model the conventionally computed R2 may not be meaningful. One suggested...
-
Shane Durbin wanted to have a recording studio custom-built in his home. He sent invitations to a number of local contractors to submit bids on the project. Rory Amstel submitted the lowest bid,...
-
Estimate the overall odds ratio of the set of tables in Problem3.6 and test whether the odds ratios are the same across the tables. Problem3.6 is: 3.6 Use the DOS data to test whether there is gender...
-
Project Evaluation this is a comprehensive project evaluation problem bringing together much of what you have learned in this and previous chapters. Suppose you have been hired as a financial...
-
Consider the given vector field. F(x, y, z) = (x + yz)i + (y + xz)j + (z + xy)k (a) Find the curl of the vector field. curl F = (b) Find the divergence of the vector field. div F =
-
You work in the human resources department of your company helping new employees fill out the necessary paperwork to get their first paycheck. There are a number of decisions that employees must make...
-
The market portfolio of a certain country has a mean return of 10% and a standard deviation of the return of 20%. Fund X in this market earns a mean return of 6% and fund Y earns a mean return of...
-
What are the major functions performed by the OE/S process? Explain each function.
-
Go to the 2010 annual report for Kelloggs Company at http://investor.kelloggs.com/annuals.cfm and find out how inventories are valued. How much inventory did Kelloggs have in 2010?
-
What characterizes a valid shipping notice and invoice?
-
Name and explain four different types of batch totals that could be calculated in a batch processing system.
-
What are the differences between a post-billing system and a pre-billing system?
-
(d) Write a function named void readDurianDetails (Durian durian[], int &noDurian) that read the information from the text file of durianDetails.txt and display the types of durians with the taste...
-
What mass of H2 will be produced when 122 g of Zn are reacted? Zn(s) + 2HCl(aq) ( ZnCl2(aq) + H2(g)
-
In general, what are the principles on which the Modified Accelerated Cost Recovery System (MACRS) is based?
-
How did "Check 21" in the US create the opportunity for banks to save both time and money?
-
The Wannabee Company and the Gottahave Company are identical in every respect except that the Wannabee Company is not financially levered, whereas the Gottahave Company has $2 million in 12 percent...
-
In the Application A Beer Manufacturer's ShortRun Cost Curves," the short-run variable cost function for Japanese beer is \(V C=0.55 q^{1.67}\). If the fixed cost is 600 and the firm produces 550...
-
A firm's production function is \(q=K^{0.4} L^{0.6}\). What is the firm's cost-minimizing input bundle when \(w=3\) and \(r=4\) ? Draw a diagram depicting the firm's long-run expansion path. What is...
-
If a competitive firm's cost increases due to an increase in the price of a factor of production or a tax, the firm's manager can quickly determine by how much to adjust output by calculating how the...
Study smarter with the SolutionInn App