This homework has 5 parts, each part has a separate document to solve for. There is a word document that has the instructions, but it couldn’t be uploaded since the maximum files is 5, so I copied it to the requirements section.5

Part 1 – (20 points)

1.Start by downloading the starting file for Part 1 of the exam from myCourses.

2.Five customers would each like to get a car loan from First Animated National Bank. Based on the information given, analyze which customer will spend the most money (after all costs and interest). Using financial formulas learned in class, fill in any information missing. HINT: Assume each customer will make one (1) monthly payment throughout each year of their loan’s term.

3.Compute the missing information in Columns B through F.

4.Write a formula to calculate the total amount each customer will pay over the lifetime of the loan in Cell G3. Copy this formula through Cell G7.

5.Write a formula to calculate the total amount of interest each customer will pay over the lifetime of the loan in Cell H3. Copy this formula through Cell G7.

6.Format the values of the highlighted cells in Columns B through F such that their number formatting matches other values in its respective column.

7.Format all dollar amounts as Accounting style.

8.Format APR in row 7 as percent with 2 decimal places.

9.Format ALL NEGATIVE values in RED with parenthesis.

10.Write a conclusion summarizing which customer will end up paying the most (in terms of amount of interest) above and beyond the cost of their car.

11.Save your file and name it “Your Name – Part 1A”.

12.Save your file and submit it to the Exam 2/Part 1A drop box in myCourses.

Part 3 – (20 points)

1.Start by downloading the starting file for Part 3 of the exam from myCourses.

2.In cell G6, write a formula to calculate the total Sales. This is calculated by taking the sum of the individual categories of widgets’ sales, which is calculated by multiplying the units sold by the price per unit.

3.In cell G7, write a formula to calculate the total COGS. This is calculated by taking the sum of the individual categories of widgets’ COGS, which is calculated by multiplying the units sold by the COGS.

4.In cell G10, write a formula to calculate the total Distribution Expenses. This is calculated by taking the sum of the individual categories of widgets’ Distribution Expenses, which is calculated by multiplying the units sold by the Distribution Costs.

5.Before starting the Scenario Manager in the next step, name the following cells with appropriate names:

6.Using the Scenario Manager, create three scenarios using the data in the table below:

7.Create a Scenario Summary by using the Gross Profit, Contribution Margin, Total Fixed Expenses and the Marginal Income Before Taxes as your result cells.

8.Save your file and name it “Your Name – Part 3A”.

9.Save your file and submit it to Exam 2/Part 3A dropbox in myCourses.

Part 4 (20 points)

1.Start by downloading the starting file for Part 4 of the exam from myCourses.

2.Examine the Analysis worksheet, and apply appropriate names to cells D17:D20.

3.Set up the structure of a one-variable data table on the Analysis worksheet starting in Cell L4 that shows the Gas Prices, Vehicle Charge (M4), Mileage Fee (N4), Gas Expenses (O4), and Total Transportation Expense (P4) given changes in the average price of gas from $1.25 to $2.05, by increments of $0.10 (in column Cells L5 to L13).

4.Instruct Excel to complete the one-variable data.

5.Customized the headings and basic formatting to the data table so it has a more professional appearance and still capable of doing the calculations.

6.Below the one-variable data table, starting at Cell G20, create the structure for a two-variable data table that shows the total projected transportation expense given the interaction between average gas prices form $1.25 to $2.05, by increments of $0.10 (across the row), and miles per gallon from 16 to 30 by increments of 2 miles per gallon (in column).

7.Instruct Excel to complete the two-variable data table.

8.Customized the headings and basic formatting to the data table so it has a more professional appearance and still capable of doing the calculations.

9.Save your file and name it “Your Name – Part 4A”.

10.Save your file and submit it to Exam 2/Part 4A dropbox in myCourses.

Part 5 – (20 points)

Note: You will need to use a PC for this section. If you are using a Mac, set the file up like you would do for Solver. When you submit your work, make a note stating that you used the Mac. I am able to solve this if you set it up properly. It will not work on Google Sheets.

1.Start by downloading the starting file for Part 5 of the exam from myCourses.

2.In cells F4:F7, develop the formulas needed to determine the number of gross impressions for each station. The Gross Impressions are calculated by finding the product of the Audience and the Number of Ads. You may enter a value into the Number of Ads column to test your formulas.

3.In cells G4:G7, develop the formulas needed to determine the total cost for each station. The Total Cost is calculated by finding the product of the Cost per Ad and the Number of Ads and then adding in the onetime Production Cost. You may enter a value into in the Number of Ads column to test your formulas.

4.In cells H4:H7, develop the formulas needed to determine the percentage of ads for each station. The percentage is calculated by taking the number of ads for each station and dividing it by the total number of ads for all of the stations. You may enter a value into in the Number of Ads column to test your formulas.

5.In cells E8:G8, develop the formulas needed to get the total number of ads, the total number of gross impressions and the total cost. You may enter a value into in the Number of Ads column to test your formulas.

6.Using the Solver, figure out how many ads should be ran on each station, with the goal of getting the most Total Gross Impressions possible, based on the constraints listed below. Be sure to enter list your constraints on the worksheet starting in cell A12.

a.Total Cost cannot exceed the $1,000,000 budget.

b.No station can receive more than 40% of the total number of ads.

c.Each station must have at least 10 ads.

d.The number of ads ran must be a whole number. You cannot run half an ad.

e.Total Gross Impressions must exceed 100,000,000.

7.Save your file and name it “Your Name – Part 5A”.

8.Save your file and submit it to Exam 2/Part 5A dropbox in myCourses.

Part 6 – (20 points)

1.Start by downloading the starting file for Part 6 of the exam from myCourses.

2.Using Data Validation, setup the worksheet so that a user can only enter a whole number between 0 and 5000 for the Units Sold. Add an appropriate input message. Add a stop error with an appropriate message.

3.Using Data Validation, setup the worksheet so that a user can only enter a decimal value between $0 and $200 for the Price Per Unit. Add an appropriate input message. Add a Stop error with an appropriate message.

4.Using Data Validation, setup the worksheet so that a user can only enter a valid Marketing Campaign value. List the valid values in ascending order. Valid values include the following:

$25,000

$35,000

$45,000

$55,000

Add an appropriate input message. Add a Warning error with an appropriate message.

5.Setup your worksheet with protection so that the user can only modify the cells under the assumptions section (B6, B7, B10, B11, B14, and B15). Be sure to setup the protection so that the user can only select those cells that are unlocked.

6.Save your file and name it “Your Name – Part 6A”.

7.Save your file and submit it to Exam 2/Part 6A dropbox in myCourses.