Chapter 13: Applying Excel: Excel Worksheet (Part 1 of 2)

Download the Applying Excel form and enter formulas in all cells that contain question marks.

For example, in cell C22 enter the formula "= B10".

Note: The present value factors could be computed using the built-in Excel function PV, but we recommend using the formulas in Appendix 13B.

Verify that your worksheet matches the example in the text.

Check your worksheet by changing the discount rate to 10%. The net present value should now be between $56,400 and $56,535—depending on the precision of the calculations. If you do not get an answer in this range, find the errors in your worksheet and correct them.

Click here to view Exhibit 13B-1 and Exhibit 13B-2, to determine the appropriate discount factor(s) using tables.

Save your completed Applying Excel form to your computer and then upload it here by clicking “Browse.” Next, click “Save.” You will use this worksheet to answer the questions in Part 2.

rev: 11_08_2017_QC_CS-108759

PLEASE ENTER ON EXCEL SPREAD SHEET & ENTER EXCEL FORMULAS WHERE THE QUESTION MARKS ARE

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 fitting to its regular customers for $9.30 each; the fitting has a variable manufacturing cost of $5.00.

The company’s Brake Division has asked the Electrical Division to supply it with a large quantity of X52 fittings for only $7.30 each. The Brake Division, which is operating at 50% of capacity, will put the fitting into a brake unit that it will produce and sell to a large commercial airline manufacturer. The cost of the brake unit being built by the Brake Division follows:

Although the $7.30 price for the X52 fitting represents a substantial discount from the regular $9.30 price, the manager of the Brake Division believes the price concession is necessary if his division is to get the contract for the airplane brake units. He has heard “through the grapevine” that the airplane manufacturer plans to reject his bid if it is more than $56 per brake unit. Thus, if the Brake Division is forced to pay the regular $9.30 price for the X52 fitting, it will either not get the contract or it will suffer a substantial loss at a time when it is already operating at only 50% of capacity. The manager of the Brake Division argues that the price concession is imperative to the well-being of both his division and the company as a whole.

Weller Industries uses return on investment (ROI) to measure divisional performance.

Required:

1. Assume that you are the manager of the Electrical Division.

a. What is the lowest acceptable transfer price for the Electrical Division?

b. Would you supply the X52 fitting to the Brake Division for $7.30 each as requested?

2. Calculate the net positive effect on the company's profit per brake unit the Electrical Division to supply the fittings to the Brake Division and if the airplane brakes can be sold for $56?

3. In principle, within what range would that transfer price lie?

(For all requirements, enter your "Financial Disadvantage" amounts as a negative value and round your final answers to 2 decimal places.)

Case 11A-7 Transfer Pricing; Divisional Performance [LO11-5] 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 fitting to its regular customers for $8.10 each; the fitting has a variable manufacturing cost of $4.58. The company’s Brake Division has asked the Electrical Division to supply it with a large quantity of X52 fittings for only $6.10 each. The Brake Division, which is operating at 50% of capacity, will put the fitting into a brake unit that it will produce and sell to a large commercial airline manufacturer. The cost of the brake unit being built by the Brake Division follows: Purchased parts (from outside vendors) $ 23.20 Electrical fitting X52 6.10 Other variable costs 14.32 Fixed overhead and administration 8.30 Total cost per brake unit $ 51.92 Although the $6.10 price for the X52 fitting represents a substantial discount from the regular $8.10 price, the manager of the Brake Division believes the price concession is necessary if his division is to get the contract for the airplane brake units. He has heard “through the grapevine” that the airplane manufacturer plans to reject his bid if it is more than $53 per brake unit. Thus, if the Brake Division is forced to pay the regular $8.10 price for the X52 fitting, it will either not get the contract or it will suffer a substantial loss at a time when it is already operating at only 50% of capacity. The manager of the Brake Division argues that the price concession is imperative to the well-being of both his division and the company as a whole. Weller Industries uses return on investment (ROI) to measure divisional performance. Required: 1. Assume that you are the manager of the Electrical Division. a. What is the lowest acceptable transfer price for the Electrical Division? b. Would you supply the X52 fitting to the Brake Division for $6.10 each as requested? 2. Calculate the net positive effect on the company's profit per brake unit the Electrical Division to supply the fittings to the Brake Division and if the airplane brakes can be sold for $53? 3. In principle, within what range would that transfer price lie?

Classifications on Balance Sheet

The current balance sheet of J. J. Arvesen Company contains the following major sections:

1. Current assets
2. Long-term investments
3. Property, plant, and equipment
4. Intangible assets
5. Other assets
6. Current liabilities
7. Long-term liabilities
8. Contributed capital
9. Retained earnings
10. Accumulated other comprehensive income

The following is a list of accounts in random order. Using the letters A through J, indicate in which section each account would most likely be classified. If an account does not belong under one of the sections listed, select "Not under any of the choices" from the classification drop down box. For all accounts, indicate if the account is a contra account or an account that would normally be deducted on the balance sheet by selecting "yes" from the second drop down box, otherwise select "no".

1. A profit-maximizing, monopolistically competitive car wash washes 40 cars per day, and its total cost $200 and currently makes an economic profit of $280. In the long run, everything else equal, the
   a.   car wash will wash less than 40 cars per day.
   b.   car wash will charge more than $12 per wash.
   c.   car wash will need to hire new workers to wash more cars.
   d.   car wash will wash more than 50 cars per day.

2. For a competitive firm, if at least some portion of its short-run average cost curve lies below the price of the product, we can conclude that the firm
   a.  is earning zero economic profits.
   b.  is incurring short-run losses.
   c.  is going to shut down.
   d.  is earning a profit at the profit maximizing output level.

3. If stock exchanges did not exist,
   a.   the economy’s resources could be more efficiently allocated among firms.
   b.   the risk to the investor of buying stocks would be much greater.
   c.   investment banks would no longer play a role in handling stocks.
   d.   there would be no organized way for firms to issue stock.

4. A “specialist” is a
   a.   stockbroker who specializes in the “third market.”
   b.   person who works on the floor of the New York Stock Exchange and specializes in certain stocks.
   c.   stockholder who finds buyers and sellers for specific stocks, but also operates outside of specific stock markets.
   d.   stockbroker who operates only in a particular regional stock market.

5. Suppose that we learn that hotels in Los Angeles generally operate with an average vacancy rate of 15 percent (in other words, 85 percent of the hotel rooms are filled with guests). Given this information about excess capacity, we would judge this market to be
   a.  a perfectly competitive market.
   b.  a monopoly.
   c.  a monopolistically competitive market.
   d.  an oligopoly.

6. A monopolistically competitive firm
   a.   is always a retail establishment.
   b.   has more monopoly power in the long run than does a perfectly competitive firm.
   c.   tries to differentiate its product from competitors’ products.
   d.   faces a perfectly elastic demand curve for its product.

Last year the commercial time for a broadcast on a particular TV channel was normally distributed with
Expectation 6.1 minutes. The channel manager claims that this year there has been a change in the life expectancy of the broadcast time.
To test his claim, he modeled 16 hours of transmission and found that the average commercial time per hour of transmission
Is 5.7 hours with a standard deviation of 1.2 hours.

A. Would you justify the channel manager's claim with a significance level of 0.05 ?

B. What is the smallest level of significance by which to justify his claim?

C. The channel's VP of marketing built the following trust profit according to the same sample:
p {4.92 <μ <6.48} = 1-α, what level of trust did you use?

D. The elapsed time between two commercials is split exponentially with a span of 25 minutes.
What is the probability that more than 20 minutes will pass between 2 advertisements if less than 30 are known to have passed
subtlety?

Last year the commercial time for a broadcast on a particular TV channel was normally distributed with
Expectation 6.1 minutes. The channel manager claims that this year there has been a change in the life expectancy of the broadcast time.
To test his claim, he modeled 16 hours of transmission and found that the average commercial time per hour of transmission
Is 5.7 minutes with a standard deviation of 1.2 minutes.

A. Would you justify the channel manager's claim with a significance level of 0.05 ?

B. What is the smallest level of significance by which to justify his claim?

C. The channel's VP of marketing built the following trust profit according to the same sample:
p {4.92 <μ <6.48} = 1-α, what level of trust did you use?

D. The elapsed time between two commercials is split exponentially with a span of 25 minutes.
What is the probability that more than 20 minutes will pass between 2 advertisements if less than 30 are known to have passed
subtlety?

Required information

Problem 20-2A Manufacturing: Cash budget LO P2

[The following information applies to the questions displayed below.]

Built-Tight is preparing its master budget for the quarter ended September 30, 2017. Budgeted sales and cash payments for product costs for the quarter follow:

Sales are 20% cash and 80% on credit. All credit sales are collected in the month following the sale. The June 30 balance sheet includes balances of $15,000 in cash; $45,000 in accounts receivable; and a $5,000 balance in loans payable. A minimum cash balance of $15,000 is required. Loans are obtained at the end of any month when a cash shortage occurs. Interest is 1% per month based on the beginning-of-the-month loan balance and is paid at each month-end. If an excess balance of cash exists, loans are repaid at the end of the month. Operating expenses are paid in the month incurred and consist of sales commissions (10% of sales), office salaries ($4,000 per month), and rent ($6,500 per month).

rev: 03_17_2020_QC_CS-204679

Problem 20-2A Part 2

(2) Prepare a cash budget for each of the months of July, August, and September. (Negative balances and Loan repayment amounts (if any) should be indicated with minus sign. Enter your final answers in whole dollars.)

Adelphi Company has budgeted activity for March to reflect net income $105,000. All sales are credit sales. Receivables are planned to increase (decrease -) by $3,000 payables to increase (decrease -) by $13,000 and Depreciation Expense is $60,000. Use this information to determine how much cash will increase (decrease) during the month of March. (Decreases in accounts receivable or accounts payable will have a negative sign in front of number. Round & enter final answer to the nearest whole dollar.) During FY 2018, Adelphi Company reported sales of $400,000, a contribution margin of $5.00 per unit, fixed costs of $80,000, and net income of $40,000. Use this information to determine the number of units Adelphi sold during FY 2018. (Round answer to nearest whole number) Towson Company manufactures book cases, and each requires 40 board feet of lumber. Towson expects that 1,700 and 1,750 book cases will be built in June and July, respectively. Towson keeps lumber on hand at 35% of the next month's production needs. Use this information to determine number board feet of lumber that Towson Company should buy in June. (Round & enter final answers to the nearest whole number.)

During FY 2018, Adelphi Company reported sales of $400,000, a contribution margin of $5.00 per unit, fixed costs of $80,000, and net income of $40,000. Use this information to determine the number of units Adelphi sold during FY 2018. (Round answer to nearest whole number)

Towson Company manufactures book cases, and each requires 40 board feet of lumber. Towson expects that 1,700 and 1,750 book cases will be built in June and July, respectively. Towson keeps lumber on hand at 35% of the next month's production needs. Use this information to determine number board feet of lumber that Towson Company should buy in June. (Round & enter final answers to the nearest whole number.)

Demonstrate your grasp of the Unix file system by constructing a directory structure as follows:

1. In your home ( ~ ) directory, create a directory named “UnixCourse”.

   This directory will be used in the remaining assignments of this course, as well as this one.

   Several of the commands that you will issue in this course will examine files in this directory and, in some cases, send me a listing of those files or even copies of those files for grading and diagnostic purposes. So you should not store anything unrelated to the course assignments in this ~/UnixCourse area.

2. Within your UnixCourse directory, you should create a directory named “fileAsst”. Within that directory, you should create two directories, named “Empire” and “Alliance”.

   Use the ls command to check your work.

3. Within the Empire directory, you should place a copy of the file ~cs252/Assignments/fileAsst/darth.txt

   Use the ls command to check your work.

4. Within the Alliance directory, you should place a copy of the file ~cs252/Assignments/fileAsst/r2d2.txt

   Use the ls command to check your work.

5. When you have completed this much, execute the command

   ~cs252/bin/fileAsst-1
   

   (Take note of the difference between the numeric digit 1 and the lower-case letter l in the command above. If you can’t tell the difference between these, check your web browser settings to see if you can get it to use some better fonts.)

   This will check to see if you have built the desired directory structure so far. You should repeat the above steps as necessary until this command reports success.

6. If you have been successful so far, the fileAsst-1 command will have placed a new file somewhere in the directory structure you have built. Find it, and move it into the Empire directory, renaming the file to “emperor.dat”.

7. Execute the command

   ~cs252/bin/fileAsst-2
   

   If all is well, you will receive the access code for completion of this assignment.

Use this constant dictionary as a global variable:

tile_dict = { 'A': 1, 'B': 3, 'C': 3, 'D': 2, 'E': 1, 'F': 4, 'G': 2, 'H': 4, 'I': 1, 'J': 8, 'K': 5, 'L': 1, 'M': 3, 'N': 1, 'O': 1, 'P': 3, 'Q': 10, 'R': 1, 'S': 1, 'T': 1, 'U': 1, 'V': 4, 'W': 4, 'X': 8, 'Y': 4, 'Z': 10 }

Implement function scrabblePoints(word) that returns the calculated points for the word based on the tile_dict above. The word parameter is a string. This function takes the string and evaluates the points based on each letter in the word (points per letter is set by the global dictionary). P or p is worth the same points. No points calculated for anything that is not A-Z or a-z.

[You may use upper() and isalpha() ONLY and no other method or built-in function]

Examples:

word = “PYTHON”

print(scrabblePoints(word))

returns:

14

word = “hello!!”

print(scrabblePoints(word))

returns:

8

word = “@#$=!!”

print(scrabblePoints(word))

returns:

0

Note: This function relies on scrabblePoints. Function you solved in Question 2.

Implement function declareWinner(player1Word = “skip”, player2Word = “skip”) that returns either “Player 1 Wins!”, “Player 2 Wins!”, “It’s a Tie”, “Player 1 Skipped Round”, “Player 2 Skipped Round”, “Both Players Skipped Round”. The player1Word and player2Word parameters are both type string. Assume input is always valid. This function should call on the function scrabblePoints to earn credit.

[No built-in function or method needed]

Examples:

player1Word = “PYTHON”

player2Word = “Pizza”

     print(declareWinner(player1Word, player2Word))

     returns:           

     Player 2 Wins!

              print(declareWinner(player1Word))

              returns:           

              Player 2 Skipped Round

Please do the second function only. I just needed to add the first function for reference