Project briefing for the Bishop Company
This project is based upon the Bishop Company question ATC 3-6 from the textbook (p. 147-148). Prepare an Excel spreadsheet as it appears in the text book.
As the source data (i.e. selling price, variable cost per unit, fixed costs and units sold) will change on a monthly basis, you should use formulas to automatically calculate the following values for the income statement and cost-volume-profit analysis:
Sales
Variable costs
Contribution margin
Fixed costs
Net income
Break even (in units)
Operating leverage
If your formulas are correct, you should only have to enter the source data in cells D4-D8 as all other figures will be calculated automatically.
As of 1 January 2011, the company plans to purchase certain components from outside suppliers rather than manufacturing them internally. As a result, it is forecast that fixed production costs per will decrease to $40,000 per month and the variable cost per unit will rise to $15 per unit. Bishops forecast performance for January 2011 is as follows:
Selling price per unit $18
Variable cost per unit $15
Fixed costs $40,000
Units sold 20,000
Required:
a) Extend your existing spreadsheet by preparing an income statement, break even and operating leverage calculations for the new January 2011 data. Locate this new analysis to the right of your December data in columns F to G. As the January data uses an identical format to December, you can copy your existing cells to columns F to G, then modify and re-date as needed. You will have to adjust the size of columns F-G to display the new data correctly. You should also go to page layout and change the orientation of your page to landscape.
b) Using the monthly data for December 2010 and January 2011, add a worksheet section that provides revised calculations for each months break even and net income figures assuming the selling price achieved is revised to $10, $16, $25 or $45 per unit.
a) At an appropriate place on your worksheet, provide a brief written assessment (no more than 400 words) as to whether the companys decision to purchase components externally should proceed. Use your figures from parts a) and b) to help justify your answer.
Your work should:
use an appropriate layout and format.
Make use of formulas and Excels built-in functions, rather than hard code your answers.
Be printed on no more than one page of letter sized paper.