Business Finance Homework Help

Part 1

• Step 1: Click on the following link and download the file: Project1_StartFile1.

This is your “start file” for Project 1 Part 1. Save the file as Project1_LastNameFirstName_1 and complete “Project Requirements” detailed below. DO NOT CHANGE THE FORMAT or MOVE ROWS OR COLUMNS.

• Step 2: Complete the requirements specified in the Project Requirements For Part 1 section below

Project Requirements For Part 1:

Billy’s Gut Busting Buffet – Billy is opening a new buffet restaurant and wants to use Excel to build a weekly budget forecast and estimate his profit. He has inputs and assumptions for revenue and expenses in the spreadsheet. Now he needs to set up his weekly budget forecast to show the profit he can expect based on the input he has provided. Research conducted has indicated that the number of customers Billy can expect in a week is directly tied to the price of an individual buffet meal (see “Buffet Price and associated number of customers” data in Inputs and Assumptions section of start file spreadsheet). All customers will be charged the same meal price, Billy must determine what price to charge for a meal ($12, $15, $18).

1. Use an Excel feature in cell B13 to allow a user of this model to click on cell B13 and select (or set) one of the three buffet meal price options from a dropdown list.
2. In the Weekly Budget Forecast section of the spreadsheet calculate each revenue item and each expense item for the week as well as the Total Revenue, Total Expenses, Profit, Profit Margin, and Breakeven Quantity. All of these must be calculated and must contain cell references to the inputs and assumptions so that if input values change the calculated weekly budget values will change as well. Round all values to the nearest dollar.

Additional Revenue – Billy’s Gut Busting Buffet expects that half the customers will order soda. For example if 700 customers visit the buffet and purchase a meal then 350 of those customers will also purchase drinks. If 580 customers visit the buffet then 290 of them will purchase soda etc. The revenue from each soda is $1.50. Create a formula to calculate this additional revenue from soda in cell C26.

Salaries – Create a formula to calculate the salary expense. This expense will depend on the set buffet price. If the buffet price is $18, the lower rate will be used, along with the weekly total revenue, to calculate the weekly salary expense. When the buffet price is changed, the salary expense should change accordingly.

Breakeven Quantity – Calculate how many meals Billy will have to sell to breakeven. Use a formula for this calculation. Note: To check that your formula is correct, you can use goal seek.

Part 2

• Step 1: Click on the following link and download the file: Project1_StartFile2.xlsx

This is your “start file” for Project 1 Part 2. Save the file as Project1_LastNameFirstName_2 and complete “Project Requirements” detailed below. DO NOT CHANGE THE FORMAT or MOVE ROWS OR COLUMNS.

• Step 2: Complete the requirements specified in the Project Requirements For Part 2 section below

Project Requirements For Part 2:

Sports Plus is a sporting goods retail store. They want to use Excel to help them decide the best way to fund a major building expansion. They have found two banks that will lend them the money for the expansion. The terms from each bank are listed in the start file.

1. Calculate the monthly payment amount and the total amount of interest paid for each option in the green cells. Be sure the amounts in the calculated cells will change if the input values change. (Notes: For the terms offered by Bank 1, you must consider that the 0% Down Payment is an input and may change, so it should be included in the subsequent calculations of the monthly payment and total interest paid. Also, be sure that the Bank 1 Annual Interest Rate is 3.5% and the Bank 2 Annual Interest Rate is 4.0%)
2. For the terms offered by Bank 1 complete the one-variable data table to show the impact of changes in the Down Payment on the Monthly Payment.
3. For the terms offered by Bank 2 complete the two-variable data table to show the sensitivity of the total interest paid amount to changes in interest rates and changes in loan terms. Format the results of the two-variable data table to currency and round to the nearest dollar.
4. Assume Sports Plus can negotiate the interest rate from Bank 2. What interest rate should they request to make the total interest paid in the loan from Bank 2 equal to the total interest paid in the loan from Bank 1?