AND 1. THE USE OF CALCULATORS SPREAASHEETS 2. PLEASE MAKE SURE TO WRITE Stolal ANSWER EXPLANATIONS THAT ARE SUPPORTED BY YOUR CALCULATIONS deep AsE OuESTION 3. PLEASE USE EXCEL TO CALCULATE AND SHOW WORKINGS. THANKS. File Home Insert View X Cut Copy - Page Layout Formulas Data Review Arial 10A == BTV - BE 0.A. ES Paste Help Tell me what you want to do Wap Text General Merge & Center - $ - % 8- Conditional Format as Cell Formatting Table Styles Styles Format Painter Clipboard Font Alignment L26 x I J K L M N A B C D E F G H 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 6 Wages and Salaries 7 Rent 8 Depreciation 9 Utilities 10 Supplies 11 Total $10,000 3,700 2,000 500 1,400 500 S 18.100 $ 10,600 4,000 2,000 500 1.500 580 $ 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 15 c. Predict the total operating costs for 3500 pizzas 16 d. Determine the average costs for 3500 pizzas of pizzas) RANNO Instructions Cost Behavior BE- Analysis Multi-BE Problem NPV Sunk Costs Do o x] N] 09 Piwi 5 g Data Review View Help Tell me what you want to do == General 2 Wrap Test Merge & Center - $ -% 88-98 Conditi Formatti File Home Insert Page Layout Formulas X Cut Arial - 10 - AA Paste - Copy - Copy BIU - 2. 0.A. - Format Painter Clipboard Font 022 X fa . B C 1 Cruisin' with Lincoln Alumni Alignment Number D E F GL 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) 12 13 14 PER CRUISE Variable Costs Fixed Costs 16 Labor 750.000 150.000 17 Food 750,000 75,000 18 Fuel 650,000 19 Port fees and services 125,000 20 Marketing, ads, promotion 350.000 21 Supplies 500.000 150.000 22 Totals 2,000,000 1,500,000 Insert Page Layout Formulas Data Review View Help Tell me what you want to ile Home a X Cut = General Arial BIU. Copy - 10 - AA . . A. Wrap Text Merge & Center - Paste - E $ -% 98% - Format Painter Clipboard Font Alignment Number 022 1 x fx DEF 20 Marketing, ads, promotion 21 Supplies 22 Totals 350.000 500,000 150,000 2,000,000 ED1,500,000 26 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 Insert Page Layout Data File Home A Xcut Formulas 10 - AA .0.A. == Review View Help Tell me what you want to do Wrap Text General Merge & Center - $ -% - Conditional Format as Cell Formatting Table Styles Alignment Number Styles Paste Copy - - BIU. Insert Format Painter Clipboard Font AS & B A C D 1 Name 5 Sevall Surfware is a company that specializes in seling towels, swimsuits, and beach accessories 6 The sales mix is 55:10 (ie for every 5 towels sold, 5 swimsuits and 10 beach accessories are sold). 7 Find the break even point for each product. The company's annual fixed costs are $68,000 8 Additionally, Seval 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 13 Towels 10 14 Swimsuits 25 10 15 Beach Accessones 15 20 Instruction Cost Behavior Analysis Multi E Problem NPV Sunk costs @N ]w] 5 ige Share Commen Ariel 10 : A W. Test Merge Center 5 9 8 conditional Format g o Cell Styles Fom per barrel of the product is estimated to be $150 and 10 respectively. W e California would permit this facility to be built tematy would have to pay a penalty of $15M xem enterprises are contemplating how to react to C oa tions regarding the proposedodrefneryn Redwood Cey They c omens Undert alement would cost 50 ( SOM) to build without Myting reglory The price and w per bathis product imated to $150 and 5100 respectively White Coma would permet this facility to be b e maty would have to pay a pray e per year s 8 Under Option the outlay for the enery would trple to 24052400M) However, with th e cost there would be improvements with regard to increased operating 9 Estimates that the vote cost per barrel of product would be reduced to 75 and the price that Extematy could charge customers would be raised to $200 per barrel 10 Lastly, there would not be the S M le imposed by the State of California cences and perceived quality Under both conditions, they would expect to produce and sell 5 million bars per year Actionally, the initial investment would be expected to have a useful We of 70 years 14 without any Salvage value at the end of the 20 years for depreciation purposes sig ne depreciation would be used 15 The tax rate is 40% and the cost of capital is 16% for both options Lastly, the pricing, variable costs and demand lovels are estimated to be constant for the next 20 years 10 Which option should Extemality choose that would mamze Extematy's financial value? How did the California penalty (51SM annually 19 Please support your recommendation with a Net Present Value calculation using discounted cash flow analysis ect your recommendation? 21 A suggestion keep your answers in the millions-dont get wrapped up around the 2004 you may want to look af 13 and 15 this problems a blend before the Instructions com havior Analysis Muiti problem NPV Sunkcom Tell me what you want to do X Cut File Home Insert Page Layout Formulas Arial -20 - A CU I Copy Paste BIU - - 0.A. Format Painter Clipboard Font 132 - x & fr. 4 A B C Data Review View Help == 28 Wrap Test! Merge & Center - Alignment General $ -% Number -28 Font Alignment Number D E F 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 10 11 Costs 12 Direct Material 4.20 $ 76.65 13 Direct Labor 15.60 46.80 14 Overhead 15 Departmental 31.20 93.60 16 Administrative 15.60 46.80 17 Total Manufacturing Cc S 66.60 $ 263.85 18 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 Instructions Cost Behavior BE- Analysis Multi-BE Problem NPV Sunk Costs 0 0 ] N] 04 P3 wi -) BE A Data Review View Help Tell me what you want to do == File Home Insert Page Layout Formulas X Cut Arial . 10 AA [b Copy - Paste Copy 3 Format Painter BI U. 3. a. A. Clipboard Font 2 Wrap Text Merge & Center - General $ -% 9 88-98 Conditional Alignment Number B DIE FI GH 320.40 S 320.40 $ 9 Selling Price $ 10 11 Costs 12 Direct Material $ 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 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 Labor from a 32 RELEVANT COST perspective. No more than two sentences are required. Instructions Cost Behavior BE- Analysis Multi-BE Problem NPV Sunk Costs celap Test General Paste Copy - Format Painter B . A . E E Merge & Center - $ - % Clipboard Font Alignment Numb D A 124- X for B C D E F G H 1 Assume a local gluten free pizzeria, on Harrison street, reported the following results for June and July Jun Jul 3 Unit Sales i 2,500 2,900 I 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 Tell me what you want to do Cut 10 = Paste Copy - Arial BIU - A A .A. 25 Wrap Test Mergea Center General 5.%915888 Format Painter Clipboard com Forr Font Alignment Number C23 : X 4A 1 Cruisin' with Lincoln Alumni DELFI G H I 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 Variable Costs 750,000 750,000 16 Labor 17 Food 18 Fuel 19 Port fees and services 20 Marketing, ads, promotion 21 Supplies 22 Totals 231 24 Fixed Costs 150,000 75,000 650,000 125,000 350.000 150,000 1,500,000 500,000 2,000,000 Accumin that the tunnel Alaelan eruice will ha mirada Instructions Cost Behavior BE- Analysis 2 on nar naeconcernance calculate Multi-BE Problem NPV Sunk Costs Paste Copy BIU. 10 MAA . .A. Wrap Text Merge & Center - Format Painter Clipboard General $ -% 988 Font Alignment Number C23 X fx DEFGH 19 Port fees and services 20 Marketing, ads, promotion 21 Supplies 22 Totals 500,000 2,000,000 125,000 350,000 150,000 1,500,000 26 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. 29 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 32 33 HINT: Please calculate the OPPORTUNITY COST and compare against its capacity Instructions Cost Behavior BE- Analysis Multi-Be Problem NPV Sunk costs 10 A I Copy Paste Format Painter Clipboard Wrap Text Merge & Center - General $ -% B TU 0.A. 8-99 Insert 0. Font Conditional Format as Cell Formatting Table Styles Styles Alignment Number 4 1 Name: A B C D E F G H I J K L 2 3 5 6 7 8 Sevall Surfware is a company that specializes in selling towels, swimsuits, and beach accessories The sales mix is 55 10 (e. 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? 10 12 13 Towels 14 Swimsuits 15 Beach Accessories Selling Variable Price Per Cost Per Unit Unit 10 25 10 15 16 17 18 Instructions Cost Behavior BE- Analysis Multi-BE Problem NPV Sunk Costs DC @ N 02 PW 5 9 AS pboard - X Font Alignment Number Styles per barrel of this product is estimated to be $150 and $100 respectively. While California would permit this facility to be built, Externality would have to pay a penalt Hiring our Environment 3 tematy enterprises are contemplating how to react to Cantomia regulations regarding the proposed of refinery in Redwood City 4 They face two options. Under option Athena investment would cost 50 88500M) to build without fully meeting regulatory requirements. The unit price and variable cost 5 per barrel of the product is estimated to b5150 and 5100 respectively. While Coloma would permit this facility to be built Externality would have to pay a penalty of $15M per year after 3 Under Option the initial outlay for the od rehnery would inple to 24 (52,400M). However, with the added cost, there would be improvements with regard to increased operating 9 stimates that the variable cost per banel of product would be reduced to $75 and the price that Extemality could change customers would be raised to $200 per barrel 10 Lastly, there would not be the annual $15M fee imposed by the State of California ciencies and perceived quality 13 Under both conditions, they would expect to produce and sell 5 million barrels per year Additionally, the initial investment would be expected to have a useful life of 20 years 14 without any Savage value at the end of the 20 years for depreciation purposes, stragrelne depreciation would be used 15 The tax rate is 40% and the cost of capital is 15% for both options Lastly, the pricing anable costs and demand els are estimated to be constant for the next 20 years 15 Which option should Externally choose that would maximize Extemality's financial value? How did the California penalty (S15M annually affect your recommendation? 19 Please support your recommendation with a Net Present Value calculation using discounted cash flow analysis (A suggestion loop your answers in the million dont get wrapped up around all the sale you may want to look 333 and 35 as this problema blend been the BEN structions Cost Behavior E-Analysis Multi-BE Problem NPV Sunt costs Format Painter Merge & Center - Alignment $ % 9 8 % Clipboard Font Number 129 X for B o E 320.40 $ F 320.40 G H I 9 Selling Price $ 10 11 Costs 12 Direct Material 13 Direct Labor 14 Overhead 15 Departmental 16 Administrative 17 Total Manufacturing Cc $ 4.20 $ 15.60 76.65 46.80 31 20 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 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. 30 31 Lastly, briefly comment on whether it would be cheaper to produce the Plastic Rings or to hire the German Labor from a 32 RELEVANT COST perspective. No more than two sentences are required. 33 Instructions Cost Behavior BE Analysis Multi-BE Problem NPV Sunk Costs MANAGERIAL ACCOUNTING 1. Please, kindly use excel to sole all questions 2. Please make sure to write show answer explanations that are supported by your calculations when asked in a given question. THANK YOU QUESTION 1: COST BEHAVIOUR Assume a local gluten-free pizzeria, on Harrison street, reported the following results for June and July Jun Jul Unit Sales 2,500 2,900 Cost of Food Sold Wages and Salaries Rent Depreciation Utilities Supplies Total $ 10,000 3,700 2,000 500 1,400 500 $ 18,100 $10,600 4,000 2,000 500 1,500 580 $ 19,180 a. Identify each cost as being fixed, variable or mixed. b. Determine the equation for total operating costs (Fixed + Unit Variable Cost # of pizzas) c. Predict the total operating costs for pizzas d. Determine the average costs for 3500 pizzas QUESTION 2: BREAK-EVEN ANALYSIS Cruisin' with Lincoln Alumni After successfully earning a MBA, Lincoln alumni decide to form their own cruise ship company based out of San Francisco. Lincoln Alumni cruises offers luxury trips along the Pacific Coast and to the Hawaiian Islands. They are debating what to do with their existing ship. S.S. Mission Bay which was built 15 years ago for $90 M and is now fully depreciated. To replace the Mission Bay would cost $180M and its current market value is $150M. Lincoln Alumni Cruises' cost of capital is 15%. Lincoln Alumni, dusting off some accounting materials, were able to determine the operating costs for a two-week cruise to Alaska, which would be a change from a three week cruise from travelling down to Guatemala. Here are their estimates (based on 2.000 passengers per cruise and 25 Cruises per year) PER CRUISE Variable Costs Fixed Costs Labor Food 750,000 150,000 750,000 75,000 650,000 Fuel Portfees and services 125,000 Marketing, ads, promotion Supplies 350,000 500.000 150.000 2,000,000 1,500,000 Totals a. Assuming that the two-week Alaskan cruise will be priced at $2,000 per passenger, please calculate the break-even number of passengers per cruise based on the data above. b. What do the Lincoln Alumni need to consider beyond these estimates and if they included this consideration, what would happen to the break-even point? Would there be an issue if the max capacity of the ship was 2000 passenge HINT: Please calculate the OPPORTUNITY COST and compare againstits capacity QUESTION 3: MULTIPLE BREAK-EVEN ANALYSIS Name: Sevall Surfware is a company that specializes in selling towels, swimsuits, and beach accessories. The sales mix is 5:5:10 (i.e. 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 Price Per Unit Variable Cost Per Unit Towels Swimsuits Beach Accessories QUESTION 4: SUNK COSTS Industrial Grinders During the term, we had a case study, Industrial Grinders, which focused on Sunk and Opportunity Costs. Please refer to the table below, which comes from data in the industrial Grinders case Competitor 100 Plastic Rings 320.40 Industrial Grinders 100 Steel Rings (Finished) $ 320.40 Industrial Grinders 100 Steel Rings (Unfinished or Specialized Steel) Selling Price 4.20 15.60 76.65 46.80 Costs Direct Material Direct Labor Overhead Departmental Administrative Total Manufacturing Costs 31.20 15.60 93.60 46.80 66.60 263.85 Regarding the Overhead costs, these are allocated based on Direct Labor The Variable Overhead costs are 80% of the Direct Labor amount and relate to the benefits for the labor force As you recall from the case, Industrial Grinders has the opportunity to hire the German Labor Force ata rate of 70% of the normal rate. What would be the RELEVANT costs for Decision-Making to produce the UNFINISHED (or Specialized Steel) to FINISHED steel? Please indicate your answers in the YELLOW boxes above Also, whatPRICE POINT would the customer expect to see for the Steel Rings given that the Competitors Plastic Rings last FOUR times as long? Please indicate in the YELLOW BOX in Cell E9. Lastly, briefly comment on whether it would be cheaper to produce the Plastic Rings or to hire the German Labor from a RELEVANT COST perspective. No more than two sentences are required. Please, how should it bre, so I can better place it? Thanks