- Details
- Parent Category: Economics Assignments' Solutions

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

Category | Economics |
---|---|

Subject | Accounting |

Difficulty | Undergraduate |

Status | Solved |

More Info | Portfolio Optimization Homework |

## Short Assignment Requirements

## Assignment Description

**CMPTR** Excel 2016 | Chapter 14: SAM Project 1a

New Van for the Band

working with formulas and functions

### 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.

Final Figure 1: Band Budget Worksheet

Final Figure 2: Van Payments Worksheet