Exercise 5 Watch the video Excel 5

Start with a new workbook, change the first tab to Ex 5. Use this second workbook for the next four exercises (Ex 5-8). Use a new worksheet for each exercise and label the tabs. Have the tabs in numerical order. Submit the entire workbook after Exercise 8 has been completed.

Exercise 5 uses the SUM and IF functions and has both relative and absolute cell referencing.

Instructions - Exercise 5 My Sample

Data - Exercise 5

1. Make the first column 250 pixtels wide (Note: that = 35 Width). Enter the values as shown on the data sheet, auto fill the years. Make some of the row higher to provide a separation between the sections; do not leave empty rows. The fixed costs will remain the same throughout the years. All the remaining values in the body of the worksheet will be derived from formulas. If you change the Sales Estimate, projected growth, or variable cost percentages, you should see all the values in the spreadsheet change, except, of course, the fixed costs. When finished, experiment with this to see if you have done the exercise correctly.
2. Calculations: Read Step 7 below before beginning
   1. Sales = the 2008 sales amount will increase by the amount entered for projected sales growth (5%). 2009 will be 5% greater than 2008, etc. Note that the 5% is a variable amount. If another percentage value were entered into that cell, the sales estimates would change accordingly.
      Note: 2008 use absolute reference for growth rate? no; 2009-2012? yes + drag
   2. Variable Costs = The % as entered. For example, the 2008 administrative variable costs will be 10% of the amount of the 2008 sales estimate. Remember, if the 10% were to change, the amount of the variable cost would also automatically change. For 2009, the administrative variable cost will be 10% of the 2002 sales estimate, etc. Note: make 2008 sales (B11) absolute to drag right
   3. Total Variable and Total Fixed Costs Per Year = the sum of the respective columns.
   4. Net Income Before Taxes = Sales - (Total Variable Costs + Total Fixed Costs).
   5. Income Tax = if the Net Income Before Taxes exceeds 0 then the tax is 35% of the Net Income Before Taxes. If the Net Income Before Taxes is 0 or less then the taxes are zero (you must use an IF formula).
      =IF(Test Value,value if true, value if false) or Insert > Function > IF > OK > then enter test, true, and fale values
   6. Net Income After Taxes = obvious
   7. All of the cells in the projected years must have formulas; correctly use relative/absolute referencing. When any number is changed in the data section, the projections will change.
3. Format:
   1. Use a percent style with zero decimal positions for all percent values
   2. Use a comma style with zero decimal positions for all dollar values
   3. Format negative numbers to display in red parentheses
   4. Use bold and italic formatting as shown
   5. Enter the periods (.......) as shown. You should fill the cell and go beyond the cell width to see that when using text, the cell contents will overlap the next cell. If there is something in the next cell, you cannot see the overlap, but it is there. There should be only one line for Sales Estimates for 2007......... and Expected Annual Sales Growth........
   6. Indent Administrative, Marketing, and Manufacturing under the variable costs and fixed costs by 1; indent Total Variable Costs and Total Fixed Costs by 2
   7. Add borders around every cell from the row containing Year 2008, 2009, 2010, 2011, 2012 to the bottom
   8. Add a double underline for the Net Income After Taxes numbers.
   9. Fill the column that contains the data 550,000 to 100,000 with a color of your choice
   10. Fill the row that contains the years with a color of your choice
   11. Use a different font color, style, and size for the heading
4. Change the sales estimated for 2007 to 750,000 and the sales growth to 10%. Do most of the numbers change? If not, you have error(s). Remember fixed costs will not change in the short run. Change back to $550,000 and 5% growth.
5. Save the spreadsheet.
   

Exercise 6 Watch the video Excel 6

This exercise uses the payment function (PMT) and relative cell referencing. You are provided with values for the purchase of a house. Make up values for the other entries providing the Price, Down Payment, Interest Rate, and the Number of Years of your choosing; try to be realistic. Note: if you want to enter a number as a alpha (aka Label) (125 Red Dog) rather than a number or you want to enter a symbol that would be used to calculate (=) then type ' in the cell to start.

Data - Exercise 6

Instructions - Exercise 6 My Sample

Please note: Each line item should be on one line, not two. Depending on your printer, you may show Net Monthly Income....... on two lines - please keep it all on one line with the .......s filling up the cell.

1. Calculations:
   1. Down Payment = 20% of price (use formula and cell referencing)
      make Downpayment refernce absolute? No, downpayment can be different. Drag Right
   2. Balance = Price - Down Payment Drag Right
   3. Monthly Payment = use the payment function (PMT). Click on the fx button (just above the columns) and search for PMT. The interest rate and number of years above represent annual rate and years. You must pay the mortgage on a monthly basis; therefore, you must divide the rate by 12 (cell/12) and multiple the number of periods (nper) by 12 (cell*12). pv in the function represents the present value or the amount of the original loan (referred to as Balance above). The function calculates as a negative number. Negatives are difficult to work with so it is suggested that you make it positive by entering a - (minus) in front of the pv reference. The function must use cell references, NOT numbers. Add the ROUND function to two decimals. Note
      
      Note: you can't drag right for the other types of purchases because you need to enter different values for the loan interest rate and years terms for any New loans. You must redo the PMT formulas.
   4. Total Paid = Monthly payment * Number of Years * 12
   5. Interest Paid = Total Paid - Balance
   6. Income Available after paying for the first item (House) = Net Monthly Income - Monthly Payment. For all other items, the calculation becomes the Income Available - the Monthly Payment. Note that the first column calculation is different from the others. The Income Available will continue to decline as you use it to pay for the next purchase.
2. Format:
   1. Use comma style with two decimals for all dollar values.
   2. Use a percent style with one decimal for the interest rate.
   3. Use 0 decimals for the periods in years.
   4. Be sure it will print on one page.
   5. Add appropriate borders, colors, formats, etc.
3. Do you need a second or third job to pay for everything?
4. Change the price of each item, does the spreadsheet change? It should if you are using cell referencing.
5. Add various borders, lines, thick lines, double lines, etc. Add colors, use appropriate formatting. This should be saved in the same workbook as Exercise 5. Label the tab as Ex 6 and save.
6. Center the spreadsheet horizontally on the page.
7. Add a header and footer, using one already available on your "list".
8. Save as Ex 6 in the same workbook with Ex 5.

Exercise 7 Watch the video Excel 7 & 9

Instructions - Exercise 7 My Sample

Exercise 7 is based on the values obtained in Exercise 3.
Open The first Excel file with Exercises 1-4 in it. Highlight the Spreadsheet data, Right Click and Copy > Paste in Excel 5-8 tab 7.

You are to create an XY (Scatter) chart with data points connected by smooth lines. The data points are the "quantity" and the "profit/loss per unit". Total Cost and Total Revenue data should not be part of the chart.
Highlight (select) the data for "quantity", use the Ctrl key to select the data for "profit/loss per unit" and add it to your selection.

Click on the Chart Wizard Icon on the toolbar and select XY Scatter. Use the Press and Hold bar to preview the Chart subtype and procedd through the steps.

Delete the Legend. Add the title Break Even Analysis as the first line and Your Name as the second line. Use different formatting for the two title lines. Use Chart Options to add a title for the X- and Y-axes (Quantity for the title of the X-axis and Profit/Loss per Unit for the Y-axis). Look at the other Chart Options, use them if you wish. Now be creative and add some embellishments such as borders, shadows, round corners, etc. Use fill effects (gradients, textures, patterns) and different fonts. Notice each section of the chart can be embellished; simply right click on your mouse when on a section and select Format....

Create a Custom Header.

Save your scatter chart as Ex 7 in your second workbook.

Exercise 8

Instructions - Exercise 8 My Sample

The layout of Exercise 8 is "wide open"; design it display the information so a reader can understand and interpret what you create. Format, use color,, graphics, etc. Show me what you have learned. Answer the following questions using the future value (FV) function to determine the future value of an investment, and the payment function (PMT) to determine your regular payments on a loan.

Note: Most formula errors will be due to improper formatting in cells (10% not fomatted as a % entry)

1. What is the estimated future value of a tax-deferred investment under the following assumption?
   1. You invest $2,000/year; start investing at age 25 and invest the same amount every year until age 65; earn 10%/year.
   2. What if you invest $167/month, same time and rate?
   3. What if you invest $167/month, same time, at 12%?
   4. What if you invest $5,000/year, for 40 years at 12%?
   5. Use other amounts, rates, and times.
   6. Be sure you convert the rate and time to the frequency of your payment. Monthly payment - divide the interest by 12 and muliply the years by 12.
2. Assume you are planning to buy a home in Santa Cruz for $800,000:
   1. What is your monthly payment if you pay 10% down, for 30 years, at 6.5% interest?
   2. How much interest will you pay on the mortage over the 30 years?
   3. Change the the three components (principal, rate, and time) to numbers of your choice.
3. You want to buy a new car. You found the perfect one for $25,000 and are trying to decide whether to finance it through the dealer, or take a rebate from the dealer and obtain financing via your bank.
   1. Dealer financing = 3.9%
   2. The rebate = $3,000
   3. The bank will charge 9 1/2%
   4. Time = 5 years
   5. Disregard income tax effect
4. You want to have $1.5 million in your investment account by age 70. Assuming a 10% interest rate, how much will you have to invest each year or month to have this amount? Use the PMT function. You do not enter anything in the PV box because you don't know the present value. You enter 1,500,000 in the FV box because you know what you want your investment to be worth in the future.
5. Save as the fourth sheet of your second workbook.

Submit Ex 5-8 via Blackboard Assignments tool as one file.