Words: 581
Research conducted has indicated that the umber 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 BIB to allow a user of this model to click on cell Bal 3 and select (or set) one of the three buffet meal price options from a drown list. . In the Weekly Budget Forecast section of the spreadsheet calculate each revenue item and each expense item for the week as ell as the Total Revenue, Total Expenses, Profit, Profit Margin, and Breakable 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. This version of the homework will not have an amount in the input value for Additional Revenue.
Salaries – Create a formula to calculate the salary expense. This expense will depend on the set buffet price. If the buffet price is $1 8, the lower rate will be used, along with the weekly total revenue, to calculate the Kelly salary expense. When the buffet price is changed, the salary expense should change accordingly. Breakable Quantity – Calculate how many meals Billy will have to sell to breakable. Use a formula for this calculation. To check that your formula is correct, you can use goal seek.
Part 2 Locate the Excel Spreadsheet named HA Stratifies. XSL from Blackboard/ ASSESSMENTS/Assignments. This is your “start file” for Assignment 1 Part 2. Save file as HA_Electromagnetisms_2 and complete “Assignment Requirements” All numbers must be properly formatted (i. E. Currency, percentage etc. ). Once oh have completed the steps below, save the Excel file. You will need to submit this spreadsheet in addition to answering 10 questions (based on the completed spreadsheet) in Blackboard.
Go to Blackboard/ASSESSMENTS ‘Assignments / Assignment 1 Part 2 Submission to answer the 10 multiple choice questions; the last question will ask you to submit your completed spreadsheet. Assignment Requirements: 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 ann. 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 o show the impact of changes in the Down Payment on the Monthly Payment. . 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.