EXCEL PRACTICE - ACCT 345



EXCEL PRACTICE

You may use the brackets [ ] to check off steps as you perform them.

1. Enter EXCEL and prepare the spreadsheet:

[ ] Set the global column width to 10.

[ ] Set column A to a width of 30.

[ ] Set the global format to ZERO decimal places.

2. Layout the spreadsheet as shown on the LAST PAGE of the practice:

[ ] Layout column A as shown on the LAST PAGE of the practice.

[ ] Enter your name in cell B1.

[ ] Enter your student number in cell B2.

[ ] Make sure that row 9 has 2 decimal places, row 7 is formatted as percentages, rows 9-28 are formatted as currency, and that row 29 has 4 decimal places.

[ ] Make sure that the column headings on rows 4 and 11 are central justified.

3. Enter formulas:

[ ] Enter 12000 in cell C6 as the MARKET SIZE of Year 1.

[ ] MARKET SIZE is increase by 10 percent a year for years 2 through 5.

[ ] MARKET SHARE is thirty percent in Year 1. Increasing by 10 percent a year for year 2 and by 5 percent a year for years 3 through 5.

[ ] UNIT SALES VOLUME is MARKET SIZE times MARKET SHARE.

[ ] UNIT SALES PRICE is $120.00 in Year 1. Increasing by 5 percent a year for years 2 through 5.

[ ] TOTAL REVENUE is the product of UNIT SALES VOLUME and UNIT SALES PRICE.

[ ] MATERIAL COST is $30.00 per unit each year.

[ ] CONVERSION COST is $100,000 plus $25 per unit produced in excess of 5,000 units.

[ ] TOTAL COST OF SALES is the sum of MATERIAL COST and CONVERSION COST.

[ ] GROSS MARGIN is TOTAL REVENUE less TOTAL COST OF SALES.

[ ] MARKETING COST is 12 percent of total revenue each year.

[ ] DISTRIBUTION COST is $12.00 per unit sold each year.

[ ] ADMINISTRATIVE COST is $60,000 in Year 1. Increasing by 8 percent a year for years 2 through 5.

[ ] TOTAL OTHER EXPENSES is the sum of MARKETING, DISTRIBUTION, and ADMINISTRATION expenses.

[ ] NET OPERATING FLOWS are GROSS MARGIN less TOTAL OTHER EXPENSES.

[ ] Enter the INVESTMENT amount $500,000 in Year 0.

[ ] TOTAL FLOWS are NET OPERATING FLOWS less INVESTMENT. These should be computed for Year 0 to 5.

[ ] Compute Sum-of-the-Years-Digits depreciation for the investment over the five-year period. Assume a useful service life of ten years, and a $100,000 salvage value.

[ ] Calculate the INTERNAL RATE OF RETURN using the TOTAL FLOWS range from Year 0 to Year 5.

[ ] Calculate the NET PRESENT VALUE (discounted at 10%) using the NET OPERATING FLOWS range from Year 1 to Year 5.

[ ] Insert an additional row right under the DEPRECIATION EXPENSES and title it as DDB DEPRECIATION. In that row, Double Declining Balance depreciation should be calculated for Year 1 to 5. Assume a useful service life of ten years, and a $100,000 salvage value.

[ ] Draw solid single line for rows 5 and 12.

[ ] Now, your spreadsheet should look like the one shown on the LAST PAGE of the practice. SAVE THE WORKSHEET.

4. Graph task:

[ ] Create a bar graph.

[ ] Have it depict two sets of bars: the first SYD DEPRECIATION and the second set depicting DDB DEPRECIATION. (Each set should have 5 bars).

[ ] Title the graphs "DEPRECIATION EXPENSES".

[ ] Title the Y axis "AMOUNT" and the X axis "DEPRECIATION METHOD".

[ ] SAVE THE WORKSHEET.

5. Print task:

[ ] Designate column A as a border so that it will appear as the first column of each page.

[ ] Choose to print the spreadsheet (2 pages).

[ ] Print the formulas of your spreadsheet for columns A-C on one page.

[ ] Print the graph on a separate page.

[ ] Exit EXCEL.

[ ] Turn in all printouts (4 pages).

|A |B |C |D |E |F |G | |1 |STUDENT NAME |Y. CHEN | | | | | | |2 |GRADE REPORT NUMBER |#### | | | | | | |3 | | | | | | | | |4 |ASSUMPTIONS: | |YEAR 1 |YEAR 2 |YEAR 3 |YEAR 4 |YEAR 5 | |5 | | | | | | | | |6 |MARKET SIZE | |12000 |13200 |14520 |15972 |17569 | |7 |MARKET SHARE | |30% |33% |35% |36% |38% | |8 |UNIT SALES VOLUME | |3600 |4356 |5031 |5811 |6712 | |9 |UNIT SALES PRICE | |$120.00 |$126.00 |$132.30 |$138.92 |$145.86 | |10 | | | | | | | | |11 |PROJECT RETURNS: |YEAR 0 |YEAR 1 |YEAR 2 |YEAR 3 |YEAR 4 |YEAR 5 | |12 | | | | | | | | |13 |TOTAL REVENUE | |$432,000 |$548,856 |$665,625 |$807,237 |$978,976 | |14 |COST OF SALES: | | | | | | | |15 | MATERIAL COST | |$108,000 |$130,680 |$150,935 |$174,330 |$201,352 | |16 | CONVERSION COST | |$100,000 |$100,000 |$100,780 |$120,275 |$142,793 | |17 | TOTAL COST OF SALES | |$208,000 |$230,680 |$251,715 |$294,606 |$344,145 | |18 |GROSS MARGIN | |$224,000 |$318,176 |$413,910 |$512,631 |$634,832 | |19 |OTHER EXPENSES: | | | | | | | |20 | MARKETING | |$51,840 |$65,863 |$79,875 |$96,868 |$117,477 | |21 | DISTRIBUTION | |$43,200 |$52,272 |$60,374 |$69,732 |$80,541 | |22 | ADMINISTRATIVE | |$60,000 |$64,800 |$69,984 |$75,583 |$81,629 | |23 | TOTAL OTHER EXPENSES | |$155,040 |$182,935 |$210,233 |$242,183 |$279,647 | |24 |NET OPERATING FLOWS | |$68,960 |$135,241 |$203,677 |$270,448 |$355,185 | |25 |INVESTMENT |$500,000 | | | | | | |26 |TOTAL FLOWS |-$500,000 |$68,960 |$135,241 |$203,677 |$270,448 |$355,185 | |27 | | | | | | | | |28 |DEPRECIATION EXPENSES | |$72,727 |$65,455 |$58,182 |$50,909 |$43,636 | |29 |INTERNAL RATE OF RETURN |0.2294 | | | | | | |30 |NET PRESENT VALUE (10%) |$732,747 | | | | | | |31 | | | | | | | | |

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download