To be completed in Excel using Excel formulas and functions for calculation

PROBLEM 112 Performing an AllVariance Analysis (LO3 - CC15, 17, 18; LO4 - CC22, 23, 24) Problem 112: Excel Template CHECK FIGURES (1a) Materials price variance: $15,000 F (2a) Labour efficiency variance: $4,375 U Becton Labs Inc. produces various chemical compounds for industrial use. One compound, called Fludex, is prepared by means of an elaborate distilling process. The company has developed standard costs for one unit of Fludex, as follows: D During November, the following activity was recorded by the company relative to production of Fludex: a. Materials were purchased, 12,000 millilitres at a cost of $225,000. b. There was no beginning inventory of materials on hand to start the month; at the end of the month, 2,500 millilitres of material remained in the warehouse unused. c. The company employs 35 lab technicians to work on the production of Fludex. During November, each worked an average of 160 hours at an average rate of $12 per hour. d. Variable manufacturing overhead is assigned to Fludex on the basis of direct labourhours. Variable manufacturing overhead costs during November totalled $18,200. e. Fixed overhead is also allocated on the basis of direct labourhours. The company had budgeted $14,000 for the month but underapplied it by $675. f. During November, 3,750 good units of Fludex were produced. The normal volume for the month is 4,000 good units. The company's management is anxious to determine the efficiency of the activities surrounding the production of Fludex. The company's policy is to investigate any variance more than 2% different from the relevant standard. Required: 1. a.For materials used in the production of Fludex, compute the price and quantity variances. b.The materials were purchased from a new supplier who is anxious to enter into a longterm purchase contract. Would you recommend that the company sign the contract? Explain. 2. a.For direct labour employed in the production of Fludex, compute the rate and efficiency variances. b.In the past, the 35 technicians employed in the production of Fludex consisted of 20 senior technicians and 15 assistants. During November, the company experimented with only 15 senior technicians and 20 assistants in order to save costs. Would you recommend that the new labour mix be continued? Explain. 3. Compute the variable overhead spending and efficiency variances. What relationship can you see between this efficiency variance and the labour efficiency variance? 4. Compute the fixed overhead cost variances for November PROBLEM 102 Performing an NPV Analysis (LO2 - CC8) CHECK FIGURE (1) Net annual cash receipts: $63,300 In eight years, Kent Duncan will retire. He has $300,000 to invest, and he is exploring the possibility of opening a selfservice car wash. The car wash could be managed in the free time he has available from his regular occupation, and it could be closed easily when he retires. After careful study, Kent has determined the following: a. A building, in which a car wash could be installed, is available under an eightyear lease at a cost of $3,400 per month. b. Purchase and installation costs of equipment would total $300,000. In eight years, the equipment could be sold for about 10% of its original cost. c. An investment of an additional $4,000 would be required to cover working capital needs for cleaning supplies, change funds, and so forth. After eight years, this working capital would be released for investment elsewhere. d. Both a car wash and a vacuum service would be offered, with a wash costing $3 and the vacuum costing 50 cents per use. e. The only variable costs associated with the operation would be 46 cents per wash for water and 20 cents per use of the vacuum for electricity. f. In addition to rent, monthly costs of operation would be as follows: cleaning, $900; insurance, $150; maintenance, $1,000. g. Gross receipts from the car wash would be about $2,700 per week. According to the experience of other car washes, 70% of the customers using the wash would also use the vacuum. h. Kent will not open the car wash unless it provides at least a 10% return, since this is the amount that could be earned by simply placing the $300,000 in highgrade securities. Required: 1. Assuming that the car wash will be open 52 weeks a year, compute the expected net annual cash receipts (gross cash receipts less cash disbursements) from its operation. (Do not include the cost of the equipment, the working capital, or the salvage value in these computations.) 2. Would you advise Kent to open the car wash? Show computations using the NPV method of investment analysis. Round all dollar figures to the nearest whole dollar. PROBLEM 125 Computing ROI and RI (LO2 - CC4, 6) CHECK FIGURE (1) ROI, 25% Financial data for Joel de Paris Inc. for last year follow: The company paid dividends of $15,000 last year. The \"Investment in Buisson, S.A.\" on the balance sheet represents an investment in the shares of another company. Required: 1. 2. Compute the company's margin, turnover, and ROI for last year. The board of directors of Joel de Paris Inc. has set a minimum required return of 15%. What was the company's residual income last year