Explore BrainMass

Spreadsheet building

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

The Willford Community College uses an Excel worksheet to compute tuition charges for incoming freshman. A sample of this file is attached. The file contains student name, major, high school GPA, and credit hours that they have registered. You are asked to complete the Excel file using formulas and function as described below.

1. Open the Willford-CC-Tuition.xls file workbook and save it as Willford-CC-Tuition on your disk.
2. Enter your name and date in cover worksheet then switch to data worksheet.
3. Insert a function to display the current date in cell F4.
4. Total tuition is $3050 for full-time students taking 12 credits or more and $125 per credit hour for part-time students (taking less than 12 credits). Use a function to determine the correct tuition charges.
5. Scholarship will be decided based on GPA and using table 1. Set up the table1 in Sheet1 and use a lookup function to determine the amount of the scholarship.
6. In order to encourage students to major in business the college is providing $250 tuition assistance for business majors. If a student changes his/her major the tuition assistance will NOT be applied. Use a function to determine tuition assistance.
7. To determine how much a student owe subtract scholarship and tuition assistance from total tuition.
8. Students should deposit one-third of what they owe or $500 whichever is smaller. Use a function to determine the amount of deposit.
9. Calculate the balance for each student.
10. Sort the students alphabetically.
11. Move to cell B6 and use a function to calculate average scholarship awards.
12. Move to cell E7 and use a function to determine how many students have received tuition assistance.
13. Include your name, Assignment #2, and date in the custom footer section.
14. Save your workbook as "Willford-CC-Tuition.xls" on your disk.

Table 1

GPA Scholarship
0.0 $0
2.0 $0
2.3 $100
2.8 $200
3.0 $300
3.5 $500

© BrainMass Inc. brainmass.com October 25, 2018, 9:19 am ad1c9bdddf


Solution Preview

see the attached file. Thanks
Adamski, Michelle POS 2.20 3 $375.00 $0.00 $- $375.00 $125.00 $250.00
Allman, Mary CSC 2.56 12 $3,050.00 $100.00 $- $2,950.00 $500.00 $2,450.00
Anderson, Robert CSC 2.77 12 $3,050.00 ...

Solution Summary

This post shows how to develop an excel sheet for simple decision making

See Also This Related BrainMass Solution

Fundamentals of Corporate Finance: Spreadsheet Building

Spreadsheet Assignment (WACC and Capital Budgeting)

Use the project data given below to build a spreadsheet to:

1. Calculate the cash flows for all years of this project.
2. Calculate the WACC for this project.
3. Calculate the NPV and IRR for this project.
4. Use an IF statement to generate a decision for this project in a worksheet other than the worksheet that calculates the project's cash flows.

The Town Amusement Park is considering adding a new amusement park ride. The new ride equipment would cost $290,000 to buy and ship, and $40,620 for handling and assembly.

The company estimates the project life of the proposed new ride to be three years. The proposed ride equipment is expected to have a market or scrap value of $140,500 at the end of the three year project. The new ride equipment will be depreciated straight line to zero across five years. The proposed new ride will add $20,000 in new maintenance costs if the new ride equipment is purchased. The company expects added sales from the new ride to be $150,000 per year. However, the company expects the sales of their other existing rides to be reduced by $25,000 if the new ride project is adopted and purchased.

The company wants to maintain a 0.48 debt to equity ratio. The YTM on the company's existing bonds is 4.3 percent. Its tax rate is 40 percent. The company's stock is selling for $67 per share and it just paid a dividend of $5.20 per share. Dividends are expected to grow at an average constant rate of 2.9 percent per year.

Note: The NPV function in Excel is not correctly programmed to calculate NPV as the term is used in finance. The Excel NPV function calculates only the PV of the future cash flows and does not include the initial outlay in its formulation. Adjust your spreadsheet formulation to reflect this programming error. Double check your spreadsheet results with your calculator to be sure your spreadsheet results are correct.

The spreadsheet must include:
at least two worksheets- with at least one worksheet for inputs and summary of output (results)
at least 2 different types of number format
at least 2 different colors for cell highlighting
all calculations must be done within the spreadsheet
all formulated cell calculations must use cell references (the number inputs must be first listed or input in another cell)
use at least one IF statement to automatically generate a project accept/reject decision

use at least 2 built-in formulas or functions (fx),other than =IF (e.g., =sum(D3:F3), =NPV(rate,A3:A9), etc.)
include at least 1 cell with a defined name (see the name manager under the formula tab)

View Full Posting Details