Please see attached. Create an EXcel
KSB 600 Business Fundamentals Name Comprehensive Final Examination (60 points) 2021 T3 AUID# This examination is a “take – home” examination. You must complete the examination on an individual basis. You may use your text, your notes, and the materials in the course Learning Management System in completing the examination requirements. No other sources may be used. You may not consult another person on this exam. You may not consult with any other resources, including any online resources. Your instructor has the right to process examination papers through SafeAssign which is a software program used by American University to detect potential plagiarism in assignments submitted for credit. By completing and submitting this exam, you have attested that you have completed this exam in accordance with the above instructions and have abided by the American University Academic Integrity Code. The completed examination is due not later than 11:59 pm EST on the date specified by your instructor. Your completed examination must include a cover page with your name and AUID# and all supporting documents in Word and/or Excel and must be submitted via the Learning Management System no later than the due date. Excel spreadsheets are required. You may include all documents in one file, properly formatted for printing. This is particularly important for Excel spreadsheets that spread over several tabs and pages unless properly formatted. All work must be fully shown to receive partial to full credit. Unsupported answers will receive zero credit. 1 Problem 1 (6 points) JOHN & HEATHER TUTORING, INC. is an online tutoring service provider that is particularly popular with college students. The company is interested in estimating the fixed and variable components of its tutoring services costs. John and Heather believe that these costs are driven by the number of hours of tutoring services provided. The following information was gathered for the last six months of business: Month Number of Hours Tutoring costs January 25,000 $ 400,000 February 55,000 812,000 March 25,000 360,000 April 31,000 373,000 May 34,000 378,000 June 12,000 150,000 Required: Show all work. (1 point each; total 6 points) 1) Compute the average tutoring cost per hour for the six-month period (i.e., Total costs/Total hours). (Round the average tutoring cost per hour to two decimal points.) 2) Use the high-low method to estimate the total fixed cost and the variable cost per hour. (Round the variable cost per hour to two decimal points.) Provide an equation to express the results of this estimation method. 3) Construct an Excel spreadsheet to perform a least-squares regression to estimate the tutoring company’s cost behavior. Attach the spreadsheet to the examination file. Provide an equation to express the results of this estimation method. 4) Use your Excel spreadsheet to compute the R2 value for the regression. Interpret the R2 value. That is, what does R2 mean? 5) Name one advantage and one disadvantage of each method to analyze mixed costs: - average cost - the high-low method. - least-squares regression (or Scattergraph) method. 6) Predict the tutoring company’s costs for a month when 20,000 tutoring hours are provided to customers. Use each of your methods to make the prediction. a) Average cost b) High –Low c) Regression Which prediction would you prefer to use and why? 2 Problem 2 (6 points) ABBY COMPANY produces and sells a product that has variable costs of $40 and a selling price of $96. Its current sales total $355,200 per month. Fixed manufacturing costs total $55,000 per month and fixed selling and administrative costs total $42,000 per month. The company is considering a proposal that will increase the selling price by 10%, increase the fixed manufacturing costs by 12%, and increase the fixed selling and administrative costs by $3,600. Required: Show all calculations (Parts 1-6 are worth 1 point each; total 6 points) Part 1: Compute the company's current break-even point in units. Part 2: Compute the company's current income and the current margin of safety. Part 3: Compute the new contribution margin per unit assuming the proposal is accepted. Part 4: Compute the new break-even point in units assuming the proposal is accepted. Part 5: Compute the company's income assuming the proposal is accepted and sales total 5,000 units. Part 6: Should the proposal be accepted? 3 Problem 3 (8 points) SAM’S COMPANY is a decentralized wholesaler of standard and luxury pet products with five autonomous divisions. The divisions are evaluated on the basis of ROI, with year-end bonuses given to the divisional managers who have the highest ROIs. Operating results for the company’s Cat Lovers Products Division for this year are given below: Sales $12,000,000 Variable expenses 7,000,000 Contribution margin 5,000,000 Fixed expenses 4,000,000 Net operating income $ 1,000,000 Divisional average operating assets $ 4,000,000 The company had an overall return on investment (ROI) of 16% this year (considering all divisions). Next year the Cat Lovers Products Division has an opportunity to add a new product line that would require an additional investment that would increase average operating assets by $1,800,000. The line consists of luxury cat beddings and matching cat clothing in very fancy fabrics. The cost and revenue characteristics of the new product line per year would be: Sales $2,300,000 Variable expenses 55% of sales Fixed expenses $750,000 Required: Parts 1-4 worth 1.5 points each; Part 5 worth 2 points (8 points total) 1. Computation of ROI (3 @ ½ point = 1.5 points total) Compute the Cat Lovers Products Division’s ROI for this year. (1/2 point) Compute the Cat Lovers Products Division’s ROI for the new product line by itself. (1/2 point) Compute the Cat Lovers Products Division’s ROI for next year assuming that it performs the same as this year and adds the new product line. (1/2 point) 2. If you were in the manager of Cat Lovers Products Division, would you accept or reject the new product line? Explain. (1.5 points) 3. Why do you suppose Sam Company’s headquarters is anxious for the Cat Lovers Product Division to add the new product line? (1.5 points) 4. Suppose that the Sam Company’s minimum required rate of return on operating assets is 12% and that performance is evaluated using residual income. (3 @ ½ point = 1.5 points total) Compute the Cat Lovers Products Division’s residual income for this year. (1/2 point) Compute the Cat Lovers Products Division’s residual income for the new product line by itself. (1/2 point) Compute the Cat Lovers Products Division’s residual income for next year assuming that it performs the same as this year and adds the new product line. (1/2 point) 5. Using the residual income approach, if you were the manager of the Cat Lovers Product Division, would you accept or reject the new product line? Explain. (2 points) 4 Problem 4 (6 points) Part A: (2 points) Educational toys that are appropriate for preschool children are becoming increasingly popular. Parents are also drawn to electronic toys that help education their little ones. EMMY CORPORATION is considering introducing a new electronic educational toy for children in their pre-school years. EMMY believes that the world market for such educational toys is extremely competitive and that to be competitive, the price of the electronic educational toys that the company is developing cannot exceed $16. EMMY’s required rate of return is 15% on all investments. An investment of $4,500,000 would be required to purchase the equipment needed to produce the 400,000 electronic educational toys that EMMYs management team thinks it can sell each year at the $16 price. Required: Show all work. (2 points) Please compute the target cost of one such electronic educational toy. Part B: (4 points) VICTORIA COMPANY is planning to use absorption cost-plus pricing to determine the price for a new product that will be of interest to consumers interested in gardening and home landscaping. The product reduces the manual labor involved in planting new garden spaces. VICTORIA COMPANY will need to invest $180,000 in operating assets to produce and sell 14,000 units. Its required return on investment (ROI) in its operating assets is 15%. VICTORIA COMPANY’s accounting department has provided cost estimates for the new product as follows: Per Unit Total Direct materials $5 Direct labor $4 Variable manufacturing overhead $2 Fixed manufacturing overhead $70,000 Variable selling and administrative expenses $1 Fixed selling and administrative expenses $50,000 Required: Show all work. (Parts a. and b. are 1 point each, Part