1.THE USE OF CALCULATORS AND SPREADSHEETS - 2. PLEASE MAKE SURE TO WRITE Stolal ANSWER EXPLANATIONS THAT ARE SUPPORTED BY YOUR CALCULATIONS WHEN ASKED IN A GIVEN QUESTION 3. PLEASE USE EXCEL TO CALCULATE AND SHOW WORKINGS. THANKS. File Home Insert Page Layout Formulas Data Review View Help Tell me X cut Arial 110 A A E 9. Gen Ib Copy - 3 Format Painter Clipboard Paste 25 Wrap Text Merge & Center BIUDA Font Alignment L26 - X for BICIDE I ELGIHL 1 Assume a local gluten-free pizzeria, on Harrison street, reported the following results for June and July Jun Jul 3 Unit Sales 2,500 2,900 5 Cost of Food Sold $ 10,000 $ 10,600 6 Wages and Salaries 3,700 4,000 7 Rent 2,000 2,000 8 Depreciation 500 500 9 Utilities 1,400 1,500 10 Supplies 500 580 11 Total $ 18,100 $ 19,180 12 13 a. Identify each cost as being fixed, variable or mixed. 14 b. Determine the equation for total operating costs (Fixed + Unit Variable Cost * # of pizzas) 15 c. Predict the total operating costs for 3500 pizzas 16 d. Determine the average costs for 3500 pizzas Instructions Cost Behavior BE- Analysis Multi-BE Problem NPV Sunk Costs File Home Insert Page Layout Formulas Data Review View Help D Tell me what you Arial 4 X Cut Paste - Copy - 3 Format Painter Clipboard - 10 . AA . A. == SEE Wrap Text Merge & Center - General $ - % BIU. Font Alignment Number Q22 BCDEFG 3 After successfully earning a MBA, Lincoln alumni decide to form their own 4 cruise ship company based out of San Francisco. Lincoln Alumni cruises offers luxury trips along the Pacific Coast 5 and to the Hawaiian Islands. They are debating what to do with their existing ship, S.S. 6 Mission Bay which was built 15 years ago for $90 M and is now fully depreciated. To replace the Mission Bay 7 would cost $180M and its current market value is $150M. Lincoln Alumni Cruises' cost of capital is 15% 9 Lincoln Alumni, dusting off some accounting materials, were able to determine the operating costs 10 for a two-week cruise to Alaska, which would be a change from a three week cruise from travelling down to 11 Guatemala. Here are their estimates (based on 2,000 passengers per cruise and 25 cruises per year) 14 PER CRUISE 16 Labor 17 Food 18 Fuel 19 Port fees and services 20 Marketing, ads, promotion 21 Supplies 22 Totals Variable Costs Fixed Costs 750,000 150,000 750,000 75,000 650,000 125,000 350,000 500.000 150,000 2,000,000 1,500,000 Page Layout Formulas Data Review View Help Tell me what you want to d A = = File Home Insert A X Cut Paste b Copy - Copy Format Painter Clipboard Q22 - X Arial BIU - 10 .A - A A 2 Wrap Text Merge & Center - . General $ -%, Number 868 Font Alignment for LBC ID E F L 14 PER CRUISE 15 16 Labor 17 Food 18 Fuel 19 Port fees and services 20 Marketing, ads, promotion 21 Supplies 22 Totals Variable Costs Fixed Costs 750,000 150,000 750,000 75,000 650,000 125,000 350,000 500,000 150,000 2,000,000 1,500,000 27 a. Assuming that the two-week Alaskan cruise will be priced at $2,000 per passenger, please calculate 28 the break-even number of passengers per cruise based on the data above. 30 b. What do the Lincoln Alumni need to consider beyond these estimates, and if they included this consideration, 31 what would happen to the break-even point? Would there be an issue if the max capacity of the ship was 2000 passengers? 33 HINT: Please calculate the OPPORTUNITY COST and compare against its capacity. Instructions Cost Behavior BE- Analysis Multi-Be Problem NPV Sunk Costs General Anal BIU. 10 AA ..A 2 Weap Tet Mergea Center - $ . % 9 RX cut Copy - Format Painter Clipboard A9 * Conditional Format Formatting Table Styles Cell Styles Font Alignment Number o A B C GHIJK 5 6 7 8 9 Sevall Surfware is a company that specializes in selling towels, swimsuits, and beach accessories The sales mix is 5.5.10 (ie for every 5 towels sold, 5 swimsuits and 10 beach accessories are sold). Find the break-even point for each product. The company's annual fixed costs are $68,000 Additionally, Sevall wants to achieve an operating profit of $102,000. How many units would it need to sell to achieve a profit of $102,000? Selling Variable Price Per Cost Per Unit Unit 10 12 13 Towels 14 Swimsuits 15 Beach Accessories Instructions Cost Behavior BE Analysis Multi-BE Problem NPV Sunk Costs VW X Cut Help P el me what you want to do 10 AA Copy Format Painter Cupboard General Arial BIU. . .A. Merge Center - S S - 969 oon Alone D X per barrel of this product is estimated to be $150 and $10 respectively. White California wolperthfactyto bet ter would have to pay pero 1 Firing our vimannen Externally enterprises are contemplating how to react to a regulations regarding the proposed of enery Redwood CRY 4 They face to Linderhon the intalnestment would cost (SOM) to build without meeting regler s Sabamelt produs estimated to b5150 and 5100 respectively. While Coloma would per the facility to be but may w e c endant pa y per year s Under Option the intal outlay for the refinery would inple to 24 $2.400M) However, with the added cost there would be provements with regard to increased per 9 Estimates suust that the variable cost per barrel of product would be reduced to $75 and the price that temalty could change customers would be to $200 per me 10 Lastly, there would not be the annual $15M fee imposed by the State of California e nces and perce 13 Under both conditions, they would expect to produce and sell 5 milion barrels per year Aditionally, the initial investment would be expected to have a useful We of 20 years 14 without any salvage value at the end of the 20 years for depreciation purposes, straight line depreciation would be used 15 The tax rate is 40% and the cost of capitais 16% for both options Lastly, the pricing, vanable costs and demand levels we estimated to be constant for the next 20 years 18 Which option should Externality choose that would maxime Exterity's financial value? How did the California penalty (Manual 19 Please support your recommendation with a Net Present Value calculation using discounted cash fow analysis ect your recommendation? 21 A suggestion keep your answers in the millions dont go wrapped up around the coros, as you may want to look of 333 and 3-35 as the problems abiend been the two RENAREN Instructions Cost Behavior BE- Analysis Multi-SE Problem NY Sunk Costs - Page Layout Formulas Data Review File Home Insert o x Cut L Copy - Format Painter View Help Tell me what you want Wrap Text General Merge & Center - $ - % 9 8 Paste Arial -10 AA == BIU-..A. Font G Copy E Clipboard Alignment Number F31 - X fx B C D 2 Industrial Grinders 4 During the term, we had a case study. Industrial Grinders, which focused on Sunk and Opportunity Costs. 6 Please refer to the table below, which comes from data in the Industrial Grinders case Competitor 100 Industrial Grinders 100 Plastic Rings Steel Rings (Finished) 320.40 $ 320.40 Industrial Grinders 100 Steel Rings (Unfinished or Specialized Steel) 9 Selling Price $ 4.20 15.60 76.65 46.80 11 Costs 12 Direct Material 13 Direct Labor 14 Overhead 15 Departmental 16 Administrative 17 Total Manufacturing Cc $ 3120 15 60 66.60 S 93 60 46.80 263.85 19 Regarding the Overhead costs, these are allocated based on Direct Labor 20 The Variable Overhead costs are 80% of the Direct Labor amount and relate to the benefits 21 for the labor force 22 Format Format Painter Clipboard Font Alignment Number F31 x V & D 320.40 $ 320.40 $ 9 Selling Price 10 11 Costs 12 Direct Material S 13 Direct Labor 14 Overhead 15 Departmental 16 Administrative 17 Total Manufacturing Cd $ 4.20 15.60 76.65 46.80 31.20 15.60 66.60 $ 93.60 46.80 263 85 19 Regarding the Overhead costs, these are allocated based on Direct Labor, 20 The Variable Overhead costs are 80% of the Direct Labor amount, and relate to the benefits 21 for the labor force 23 As you recall from the case, Industrial Grinders has the opportunity to hire the German Labor Force 24 at a rate of 70% of the normal rate. What would be the RELEVANT costs for Decision-Making 25 to produce the UNFINISHED (or Specialized Steel) to FINISHED steel? Please indicate your answers in the YELLOW boxes 26 above 27 28 Also, what PRICE POINT would the customer expect to see for the Steel Rings given that the Competitors Plastic Rings 29 last FOUR times as long? Please indicate in the YELLOW BOX in Cell E9. 31 Lastly, briefly comment on whether it would be cheaper to produce the Plastic Rings or to hire the German Labbr from a 32 RELEVANT COST perspective. No more than two sentences are required. Instructions Cost Behavior. BE- Analysis Multi-BE Problem NPV Sunk Costs ODO O XD NO PIwi 5 g