We Helped With This Accounting Economics Homework: Have A Similar One?

CMPTR Excel 2016 | Chapter 14: SAM Project 1a

New Van for the Band

GETTING STARTED

·               Open the file CMPTR_EX16_14a_FirstLastName_1.xlsx, available for download from the SAM website.

·               Save the file as CMPTR_EX16_14a_FirstLastName_2.xlsx by changing the “1” to a “2”.

o      If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

·               With the file CMPTR_EX16_14a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

o      If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

1.        The band Strobe Lite has decided to self-release its debut album and will be recording it in the spring. They are planning to release the album just before the summer and will go on tour to support the album, and they are looking to buy a van for the tour. To plan for the next year, they created a worksheet to help keep track of their anticipated income and expenses, and to figure out their loan options for buying a van.

In the Band Budget worksheet, apply the Heading 1 cell style to the merged cell A1.

2.        Apply the Heading 4 cell style to cell A2.

3.        In cell A3, enter the TODAY function to replace the text “Current Date” with the current date.

4.        Merge and center the range A5:C5 and then apply the 60% - Accent5 cell style to the merged cell. (Hint: Depending on your version of Office, the cell style may be written as Sky Blue, 60% - Accent5, or Sky Blue.)

5.        Use AutoFill to fill the range C13:M13 with the abbreviations Feb through Dec, based on the value in cell B13.

6.        Center and bold the text in the range B13:M13.

7.        In addition to working day jobs, the band performs at various venues for different amounts of money throughout the year. They also earn income by selling merchandise at their shows and on their website. During the summer, they will be taking time off from their day jobs and their steady gig as a house band to play several festivals, and to supplement their income, they will play more parties than usual.

Complete the income section of the Band Budget worksheet as described below:

a.        In cell B14, enter an absolute reference to cell F7.

b.        In cell B15, enter an absolute reference to cell F8.

c.        In cell B16, enter an absolute reference to cell F9.

d.        In cell B17, enter an absolute reference to cell F10.

e.        Copy the formulas you entered in the range B14:B17 to the range C14:F17 and the range K14:M17.

8.        Apply the Blue-Gray, Accent 5, Lighter 80% fill color (9^th column, 2^nd row of the Theme Colors palette) to the range D19:M19.

9.        Apply the Accounting format with the $ symbol and zero decimal places to the ranges B14:M14 and B19:M20. (Hint: Depending on how you perform this, the number format may appear as Custom.)

10.     The band has estimated the expenses for recording their album, playing their summer tour, and making a documentary, and now needs to know how much they will be paying each month.

In cell B31, create a formula using the SUM function to calculate the total expenses for January based on the range B20:B30. Copy the formula you just created in cell B31 to the range C31:M31.

11.     Apply the Orange, Accent 2, Lighter 80% fill color (6^th column, 2^nd row of the Theme Colors palette) and add a Top and Thick Bottom Border to the range B31:M31. 

12.     Next, the band wants to compare their income and expenses so that they can figure out where and how to adjust their plan, if necessary.

In cell B32, enter a formula without using a function to calculate the net cash flow for the month of January. The formula should subtract the value in cell B31 from the value in cell B19. Copy the formula you just created in cell B32 to the range C32:M32.

13.     Indent the contents of the range A20:A30 once. (Hint: The values in the range A20:A30 should be aligned with the values in the range A14:A18).

14.     To easily compare the most important figures, the top of the worksheet features a year-end summary. The numbers for income are already there, but the band’s expenses need to be included as well.

In cell C7, enter the SUM function to calculate the total yearly expenses based on the range B31:M31.

15.     In cell C8, enter the AVERAGE function to calculate the average monthly expenses over the year based on the range B31:M31.

16.     In cell C9, edit the MAX function to determine the maximum monthly expenses over the year based on the range B31:M31.

17.     In cell C10, enter the MIN function to determine the minimum monthly expenses over the year based on the range B31:M31.

18.     Having a van or a trailer for the summer (and beyond) might be useful to the band. The band members are thinking about getting a loan, and they want to figure out what the monthly payment would be.

In the Van Payments worksheet, use the Format Cells dialog box to format the range B8:D8 with the Percentage style with two decimal places.

19.     In cell B10, enter a formula without using a function to calculate the total number of loan payments for Option 1. In the formula, the value in cell B7 (the payments per year) should be multiplied by the value in cell B9 (the number of years of the loan). Copy the formula you created in cell B10 to the range C10:D10.

20.     In cell B11, enter a formula using the PMT function to calculate the loan payment on the principal value shown in cell B3 (pv), using the calculated monthly rate in cell B8 (rate) and the payments per period in cell B10 (nper). Remember to use an absolute reference to the principal value in cell B3 in your formula. Copy the formula you created in cell B11 to the range C11:D11.

Your workbook should look like the Final Figures below. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.