Question: The Excel worksheet form that appears below
The Excel worksheet form that appears below is to be used to recreate the Review Problem related to Mynor Corporation. Download the workbook containing this form from Connect, where you will also receive instructions about how to use this worksheet form.
You should proceed to the requirements below only after completing your worksheet.
Required:
1. Check your worksheet by changing the budgeted unit sales in Quarter 2 of Year 2 in cell C5 to 75,000 units. The total expected cash collections for the year should now be $2,085,000. If you do not get this answer, find the errors in your worksheet and correct them. Have the total cash disbursements for the year changed? Why or why not?
2. The company has just hired a new marketing manager who insists that unit sales can be dramatically increased by dropping the selling price from $8 to $7. The marketing manager would like to use the following projections in the budget:
a. What are the total expected cash collections for the year under this revised budget?
b. What is the total required production for the year under this revised budget?
c. What is the total cost of raw materials to be purchased for the year under this revised budget?
d. What are the total expected cash disbursements for raw materials for the year under this revised budget?
e. After seeing this revised budget, the production manager cautioned that due to the limited availability of a complex milling machine, the plant can produce no more than 90,000 units in any one quarter. Is this a potential problem? If so, what can be done about it?
Transcribed Image Text:
A 1 Chapter E Applying Eacel 3 Dota Year 2Qarter 2 60.000 Vear a Quarter 3 Budgeted unit sales 40.000 1.000 50.000 78.000 80.00 7Selling priee per nt OAccouts ecevable, beginning balance 9 Sales colected in the quater ales a made 10 - Sales collected in the quaner ater sas ae made 11- Desind ending trished geads inertery is 12 Finished goods vertory, beginning 13 - Raw materials regaed ta preduca one unit 14 - Desired onding inventary ef a mateias is 15 Raw materiala imentery. begienin 16 Raw material conta 17 - Raw materials purchasen a paid 18 and 19 - Accouts paable far u naterals, begiming balance 20 21 Ertera formula into each of the cele marked wh a? below 2 Review Preblem. Budget Schedles 23 24 Construct the sules budger 75% 25% 2% of the budgeted unt sales of the net ater 12.000 te 5 pounds 10% af the net quaters prduction seed 23 000 pounde S00 p peand 0as in the quater the purchanen a made 40% n the quater folewing pcha Year 2 Quarter Year Quarter 26 Budgeted unt alen 27 Seling price pe nt 28 Total e 30 Construct the achedvie of especind canh collecfene Vear 2 Quarter 32 Beginning talence ccounts recevable 38 Feater sales 34 Secendquarte sas 35 Third-quater sales 36 Fauthquter sals 37 Total cah colectione 29 Conetruct the prodvctlon budyet 40 41 dgeted unt ses 42 Add desied ending feished goode inentay 4 Total seedo 44 Lens beginning feishet goode ivertay Required predeonint 46 47 Constuct the wmaeriala purchaes batget 48 40 Rpied produchon te 50 Raw materale recuired te produce one unt pend SI Production reede (peunde 52 Add desied ending invemory of raw materials (peunds) 53 Total eeds pnds) 54 Less begining ivetory of raw materials peanda) 58 Raw mateias to be pchaed pound) 56 Cost of raw material per pound 57 Cont of raw materiata ta be purchamed Year 2 Quarter Year 3 Quarter Vear Yeur 2 Ouerter Year Quater Year 50 Construet the chedule of pecind canh paymen Year 2 Quarter 60 Year 61 Beginning balance accounts payable Fat-ater purchases 68 Secend-quarter pechanes 64 Thirdater prchases 65 Fauth-quarter pechases 06 Total cah disbusements 67 harterem ntom Year 2 Quarter Year 3 Quarter Data 1 2 4 1 Budgeted unit sales Selling price per unit. 50,000 70,000 120,000 80,000 90,000 100,000 $7
> Fitness Fanatics is a regional chain of health clubs. The managers of the clubs, who have authority to make investments as needed, are evaluated based largely on return on investment (ROI). The company’s Springfield Club reported the following results fo
> Miller Toy Company manufactures a plastic swimming pool at its Westwood Plant. The plant has been experiencing problems as shown by its June contribution format income statement below: Janet Dunn, who has just been appointed general manager of the Westw
> Koontz Company manufactures a number of products. The standards relating to one of these products are shown below, along with actual cost data for May. The production superintendent was pleased when he saw this report and commented: “T
> Mickley Corporation produces two products, Alpha6s and Zeta7s, which pass through two operations, Sintering and Finishing. Each of the products uses two raw materials—X442 and Y661. The company uses a standard cost system, with the foll
> Wallis Company manufactures only one product and uses a standard cost system.The company uses a predetermined plantwide overhead rate that relies on direct labor-hours as the allocation base. All of the company’s manufacturing overhead
> Refer to the data in Exercise 10–6. Assume that instead of producing 4,000 units during the month, the company produced only 3,000 units, using 14,750 pounds of material. (The rest of the material purchased remained in raw materials inv
> Erie Company manufactures a mobile fitness device called the Jogging Mate. The company uses standards to control its costs. The labor standards that have been set for one Jogging Mate are as follows: During August, 5,750 hours of direct labor time were
> Refer to the data for Lavage Rapide in Exercises 9–10 and 9–13. Data given in Exercise 9-10: Lavage Rapide is a Canadian company that owns and operates a large automatic car wash facility near Montreal. The following table provides data
> Refer to the data for Lavage Rapide in Exercise 9–10. Also assume that the company’s actual operating results for August are as follows: Lavage Rapide Income Statement For the Month Ended August 31 Actual cars washed……………………………………………………………………..8,800 Rev
> Refer to the data for Lavage Rapide in Exercise 9–10. The company actually washed 8,800 cars in August. Exercise 9–10: Lavage Rapide is a Canadian company that owns and operates a large automatic car wash facility nea
> Refer to the data for Lavage Rapide in Exercise 9–10. The company actually washed 8,800 cars in August. Data given in Exercise 9-10: Lavage Rapide is a Canadian company that owns and operates a large automatic car wash facility near Mo
> Refer to the data for The Terminator Inc. in Exercise 9–6. A management intern has suggested that the budgeted revenues and costs should be adjusted for the actual level of activity in April before they are compared to the actual revenu
> Flight Café prepares in-flight meals for airlines in its kitchen located next to a local airport. The company’s planning budget for July appears below: Flight Café Planning Budget For the Month Ended July 31 Budgeted meals (q) ..........................
> Wolfpack Company is a merchandising company that is preparing a budget for the month of July. It has provided the following information: Wolfpack Company Balance Sheet June 30 Assets Cash..................................................................
> Wheeling Company is a merchandiser that provided a balance sheet as of September 30 as shown below: Wheeling Company Balance Sheet September 30 Assets Cash.......................................................................................$ 59,000 Ac
> Hartwell Company manufactures one product, it does not maintain any beginning or ending inventories, and its uses a standard cost system. Its predetermined overhead rate includes $1,760,000 of fixed manufacturing overhead in the numerator and 44,000 dire
> The production department of Zan Corporation has submitted the following forecast of units to be produced by quarter for the upcoming fiscal year: In addition, 6,000 grams of raw materials inventory is on hand at the start of the 1st Quarter and the be
> Refer to the data for Beech Corporation in Exercise 2. The company is considering making the following changes to the assumptions underlying its master budget: 1. Each month’s credit sales are collected 45% in the month of sale and 55% in the month follo
> Beech Corporation is a merchandising company that is preparing a master budget for the third quarter of the calendar year. The company’s balance sheet as of June 30th is shown below: Beech Corporation Balance Sheet June 30 Assets Cash...................
> Weller Company’s budgeted unit sales for the upcoming fiscal year are provided below: The company’s variable selling and administrative expense per unit is $2.50. Fixed selling and administrative expenses include adve
> Fogerty Company makes two products—titanium Hubs and Sprockets. Data regarding the two products follow: Additional information about the company follows: a. Hubs require $32 in direct materials per unit, and Sprockets require $18. b. T
> Worley Company buys surgical supplies from a variety of manufacturers and then resells and delivers these supplies to hundreds of hospitals. Worley sets its prices for all hospitals by marking up its cost of goods sold to those hospitals by 5%. For examp
> SecuriCorp operates a fleet of armored cars that make scheduled pickups and deliveries in the Los Angeles area. The company is implementing an activity-based costing system that has four activity cost pools: Travel, Pickup and Delivery, Customer Service,
> The following activities occur at Greenwich Corporation, a company that manufactures a variety of products. a. Receive raw materials from suppliers. b. Manage parts inventories. c. Do rough milling work on products. d. Interview and process new employees
> Refer to the data in Exercise 8. Assume that Minneapolis’ sales by major market are: The company would like to initiate an intensive advertising campaign in one of the two market segments during the next month. The campaign would cost
> Raner, Harris & Chan is a consulting firm that specializes in information systems for medical and dental clinics. The firm has two offices—one in Chicago and one in Minneapolis. The firm classifies the direct costs of consulting job
> Athens Company is conducting a time-driven activity-based costing study in its Engineering Department. To aid the study, the company provided the following data regarding its Engineering Department and the customers served by the department: Required:
> Refer to the data in Exercise 6 for Chuck Wagon Grills. Assume in this exercise that the company uses absorption costing. Data from Exercise 6: Sierra Company incurs the following costs to produce and sell its only product. Variable costs per unit: Dir
> Sierra Company incurs the following costs to produce and sell its only product. Variable costs per unit: Direct materials ................................................................$9 Direct labor ...................................................
> Crossfire Company segments its business into two regions—East and West. The company prepared a contribution format segmented income statement as shown below: Required: 1. Compute the companywide break-even point in dollar sales. 2. Co
> Walsh Company manufactures and sells one product. The following information pertains to each of the company’s first two years of operations: Variable costs per unit: Manufacturing: Direct materials…………………………. .............................$25 Direct labo
> Parker Products, Inc. is a manufacturer whose absorption costing income statement reported sales of $123 million and a net operating loss of $18 million. According to a CVP analysis prepared for management, the company’s break-even point is $115 million
> Shannon Company segments its income statement into its North and South Divisions. The company’s overall sales, contribution margin ratio, and net operating income are $500,000, 46%, and $10,000, respectively. The North Division’s contribution margin and
> Piedmont Company segments its business into two regions—North and South. The company prepared the contribution format segmented income statement as shown below: Required: 1. Compute the companywide break-even point in dollar sales. 2.
> Vitex, Inc. manufactures a popular consumer product and it has provided the following data excerpts from its standard cost system: The company’s manufacturing overhead cost is applied to production on the basis of direct labor hours. A
> Vitex, Inc. manufactures a popular consumer product and it has provided the following data excerpts from its standard cost system: The company’s manufacturing overhead cost is applied to production on the basis of direct labor hours. A
> Matheson Electronics has just developed a new electronic device it believes will have broad market appeal. The company has performed marketing and cost studies that revealed the following information: a. New equipment would have to be acquired to produce
> Bracey Company manufactures and sells one product. The following information pertains to the company’s first year of operations: Variable cost per unit: Direct materials ..................................................................$19 Fixed costs p
> Vitex, Inc. manufactures a popular consumer product and it has provided the following data excerpts from its standard cost system: The company’s manufacturing overhead cost is applied to production on the basis of direct labor hours. A
> Tom Emory and Jim Morris strolled back to their plant from the administrative offices of Ferguson & Son Manufacturing Company. Tom is manager of the machine shop in the company’s factory; Jim is manager of the equipment maintenance department. The men ha
> O’Brien Company manufactures and sells one product. The following information pertains to each of the company’s first three years of operations: Variable costs per unit: Manufacturing: Direct materials ...................................................
> O’Brien Company manufactures and sells one product. The following information pertains to each of the company’s first three years of operations: Variable costs per unit: Manufacturing: Direct materials ...................................................
> The Excel worksheet form that appears below is to be used to recreate the main example in the text pertaining to Colonial Pewter Company. Download the workbook containing this form from Connect, where you will also receive instructions about how to use t
> The Excel worksheet form that appears below is to be used to recreate Example E and Exhibit 13–8. Download the workbook containing this form from Connect, where you will also receive instructions about how to use this worksheet form. Y
> The Excel worksheet form that appears below is to be used to recreate the example in the text related to Santa Maria Wool Cooperative. Download the workbook containing this form from Connect, where you will also receive instructions about how to use this
> The Excel worksheet form that appears below is to be used to recreate the Review Problem pertaining to the Magnetic Imaging Division of Medical Diagnostics, Inc.Download the workbook containing this form from Connect, where you will also receive instruct
> The Excel worksheet form that appears below is to be used to recreate the main example in the text pertaining to Colonial Pewter Company. Download the workbook containing this form from Connect, where you will also receive instructions about how to use t
> The Excel worksheet form that appears below is to be used to recreate the Review Problem relating to Harrald’s Fish House. Download the workbook containing this form from Connect, where you will also receive instructions about how to us
> Ogilvy Company manufactures and sells one product. The following information pertains to each of the company’s first three years of operations: Variable cost per unit: Direct materials ............................................................$16 Fixe
> The Excel worksheet form that appears below is to be used to recreate the Review Problem pertaining to Ferris Corporation. Download the workbook containing this form from Connect, where you will also receive instructions about how to use this worksheet f
> The Excel worksheet form that appears below is to be used to recreate portions of Review Problem 1 relating to Dexter Corporation. Download the workbook containing this form from Connect, where you will also receive instructions about how to use this wo
> Refer to the financial statements for Rusco Company in Problem 14–13. Because the Cash account decreased so dramatically during this year, the company’s executive committee is anxious to see how the income statement wo
> Refer.to.the.data.for.Carmono.Company.in.Exercise.14–6. Data.given.in.Exercise.14-6: Comparative.financial.statement.data.for.Carmono.Company.follow: For this year the company reported net income as follows: Sales…
> Refer to the data for Pavolik Company in Exercise 14–4. Data given in Exercise 14–4: The following changes took place last year in Pavolik Company’s balance sheet accounts: Long-term investments th
> Refer to the financial statements for Rusco Company in Problem 14–13. Because the Cash account decreased so dramatically during this year, the company’s executive committee is anxious to see how the income statement wo
> Refer to the data for Hanna Company in Exercise 14–2.The company’s income statement for the year appears below: Sales ...............................................................$350,000 Cost of goods sold ........
> Refer to the data for Carmono Company in Exercise 14–6. Data.given.in.Exercise.14-6: Comparative.financial.statement.data.for.Carmono.Company.follow: For this year the company reported net income as follows: Sales…
> Kelly Company manufactures and sells one product. The following information pertains to each of the company’s first two years of operations: Variable cost per unit: Direct materials .....................................................................$1
> Refer to the data for Pavolik Company in Exercise 14–4. Data given in Exercise 14–4: The following changes took place last year in Pavolik Company’s balance sheet accounts: Long-term investments tha
> Shimano Company has an opportunity to manufacture and sell one of two new products for a five year period. The company’s tax rate is 30% and its after-tax cost of capital is 14%. The cost and revenue estimates for each product are as fo
> Rosman Company has an opportunity to pursue a capital budgeting project with a five-year time horizon. After careful study, Rosman estimated the following costs and revenues for the project: Cost of new equipment needed ………………………………………. $420,000 Sale of
> Lander Company has an opportunity to pursue a capital budgeting project with a five-year time horizon. After careful study, Lander estimated the following costs and revenues for the project: Cost of equipment needed ……………………………………………….. $250,000 Working
> Winthrop Company has an opportunity to manufacture and sell a new product for a five-year period. To pursue this opportunity, the company would need to purchase a piece of equipment for $130,000. The equipment would have a useful life of five years and z
> Gaston Company is considering a capital budgeting project that would require a $2,000,000 investment in equipment with a useful life of five years and no salvage value. The company’s tax rate is 30% and its after-tax cost of capital is
> Aldean Company wants to use absorption cost-plus pricing to set the selling price on a new product. The company plans to invest $200,000 in operating assets to produce and sell 16,000 units. Its required return on investment (ROI) in its operating assets
> Wilderness Products, Inc., has designed a self-inflating sleeping pad for use by backpackers and campers. The following information is available about the new product: a. An investment of $1,350,000 will be necessary to carry inventories and accounts rec
> The postal service of St. Vincent, an island in the West Indies, obtains a significant portion of its revenues from sales of special souvenir sheets to stamp collectors. The postal service purchases the souvenir sheets from a supplier for $0.80 each. St.
> Valmont Company has developed a new industrial piece of equipment called the XP–200. The company is considering two methods of establishing a selling price for the XP–200—absorption cost-plus pricing and value-based pricing. Valmont’s cost accounting sys
> Lyons Company manufactures and sells one product. The following information pertains to the company’s first year of operations: Variable cost per unit: Direct materials ......................................................................$13 Fixed cost
> Northport Company manufactures numerous products, one of which is called Sea Breeze Skin Cleanser. The company has provided the following data regarding this product: Unit sales (a) .......................................................................
> McDermott Company has developed a new industrial component called IC–75. The company is excited about IC–75 because it offers superior performance relative to the comparable component sold by McDermott’s primary competitor. The competing part sells for $
> Maria Lorenzi owns an ice cream stand that she operates during the summer months in West Yellowstone, Montana. She is unsure how to price her ice cream cones and has experimented with two prices in successive weeks during the busy August season. The numb
> The managers of Midwest Whitetails magazine (a magazine dedicated to deer hunters) want to establish a price for customers wishing to place a full-page advertisement in their magazine for one month. To help with the price setting decision, the managers i
> Messina Company wants to use absorption cost-plus pricing to establish the selling price for a new product. The company plans to invest $650,000 in operating assets that provide the capacity to make 30,000 units. Its required return on investment (ROI) i
> National Restaurant Supply, Inc., sells restaurant equipment and supplies throughout most of the United States. Management is considering adding a machine that makes sorbet to its line of ice cream making machines. Management will negotiate the purchase
> Currington Company wants to use absorption cost-plus pricing to set the selling price on a newly remodeled product. The company plans to invest $150,000 in operating assets to produce and sell 12,000 units. Its required return on investment (ROI) in its
> Weller Industries is a decentralized organization with six divisions. The company’s Electrical Division produces a variety of electrical items, including an X52 electrical fitting. The Electrical Division (which is operating at capacity) sells this fitti
> Alpha and Beta are divisions within the same company. The managers of both divisions are evaluated based on their own division’s return on investment (ROI). Assume the following information relative to the two divisions: Managers are f
> Stavos Company’s Screen Division manufactures a standard screen for high-definition televisions (HDTVs). The cost per screen is: Variable cost per screen………â€&brvba
> Norwall Company’s budgeted variable manufacturing overhead cost is $3.00 per machine-hour and its budgeted fixed manufacturing overhead is $300,000 per month. The following information is available for a recent month: a. The denominator activity of 60,00
> Hrubec Products, Inc., operates a Pulp Division that manufactures wood pulp for use in the production of various paper goods. Revenue and costs associated with a ton of pulp follow: Hrubec Products has just acquired a small company that manufactures pap
> In each of the cases below, assume Division X has a product that can be sold either to outside customers or to Division Y of the same company for use in its production process. The managers of the divisions are evaluated based on their divisional profits
> Division A manufactures electronic circuit boards. The boards can be sold either to Division B of the same company or to outside customers. Last year, the following activity occurred in Division A: Selling price per circuit board …………………………………………………..$1
> What does a manufacturing cycle efficiency (MCE) of less than 1 mean? How would you interpret an MCE of 0.40?
> Why is it a good idea to create a “Budgeting Assumptions” tab when creating a master budget in Microsoft Excel?
> What are the two stages of allocation in activity-based costing?
> If the units produced equals the units sold, which method would you expect to show the higher net operating income, variable costing or absorption costing? Why?
> Why aren’t transactions involving accounts payable considered to be financing activities?
> Should a company allocate its common fixed costs to business segments when computing the break-even point for those segments? Why?
> What general guidelines can you provide for interpreting the statement of cash flows?
> Sunk costs are easy to spot—they’re the fixed costs associated with a decision.” Do you agree? Explain.
> Stahl Company is conducting a time-driven activity-based costing study in its Shipping Department. To aid the study, the company provided the following data regarding its Shipping Department and the customers served by the department: Required: 1. Usin
> Refer to the data in Exercises 7A–1 and 7A–2. Now assume that Saratoga Company would like to answer the following “what if” question using its time-driven activity-based costing syst
> Sako Company’s Audio Division produces a speaker that is used by manufacturers of various audio products. Sales and cost data on the speaker follow: Selling price per unit on the intermediate market ……………………………….$60 Variable costs per unit……………………………………
> Refer to the data in Exercise 7A–1. In addition, assume that Saratoga Company provided the following activity data for all jobs produced during the year: Data given in Exercise 7A–1: Saratoga Company manufactures job
> Saratoga Company manufactures jobs to customer specifications. The company is conducting a time-driven activity-based costing study in its Purchasing Department to better understand how Purchasing Department labor costs are consumed by individual jobs. T
> Markus Company’s common stock sold for $2.75 per share at the end of this year. The company paid a common stock dividend of $0.55 per share this year. It also provided the following data excerpts from this year’s finan
> Ravenna Company is a merchandiser that uses the indirect method to prepare the operating activities section of its statement of cash flows. Its balance sheet for this year is as follows: During the year, Ravenna paid a $6,000 cash dividend and it sold a
> Cardinal Company is considering a five-year project that would require a $2,975,000 investment in equipment with a useful life of five years and no salvage value. The company’s discount rate is 14%. The project would provide net operati
> Cane Company manufactures two products called Alpha and Beta that sell for $120 and $80, respectively. Each product uses only one type of raw material that costs $6 per pound. The company has the capacity to annually produce 100,000 units of each product
