Simple Excel with Formulas only

Jan2022 LAB 1 Assignment ( 3%) . Each question carries 10 marks 1. The EXCEL file Science and Engineering Jobs shows the number of jobs (in thousands) in the year 2000 and projections made for 2010 from a government study. Use the Excel file to com pute a) the projected increase from the 2000 baseline for each occupational category b) the percentage increase for each occupational category and the overall pe rcentage increase. 2. Store and Regional Sales file provides sales data for computers and its peripherals. a) Sort the table by the Region column in descending order b) Using the SUMIF function, m odify the spreadsheet to calculate the total sales revenue for (i) each of the eight stores (ii) each of the three sales regions 3. President’s Inn Guest file provides information on the Inn’s guest. ˗ Room rates are the same for one or two guests; however guests must pay an additional $20 per person per day for meals. ˗ Guests staying for 7 days or more receive a 10% discount. Using the IF function, m odify the spreadsheet to calculate a) the number of days that each party stayed at the inn and b) the total revenue for the length of stay. 4. Using Credit Risk data file Use the COUNTIF function to determine a) The number of customers who applied for new car, used car, business education, small appliance and furniture loans b) Number of customers with chec king account balance less than $500. 5. Modify the Credit Risk spreadsheet using IF functions to include new columns, classifying the checking and savings account balances as follows : • low if balance is less than $250, • medium if balance between $250 but less than $2000 • high , otherwise 6. Develop a spreadsheet to calculate the sales of a new product that is given by the formula = for = 0 160 ℎ 10 , ℎ = 15000 , = −8 = −0.05 7. An experiment was conducted to identify the relationship between sales and pricing, coupon and advertising strategies. (use Sales Promotion EXCEL file) a) Estimate the sales for each week for stores 1 – stores 3 using the following model: = 500 − 0.05 ∗ + 30 ∗ + 0.08 ∗ + 0.25 ∗ ∗ b) Compute the average estimated sales for each store and the average estimated sales for all the stores 8. In the Accounting Professionals dataset, use EXCEL fu nctions to determine: a) the employee that has served the most and for how long (years) b) average number of years of service c) number of male employees in the dataset d) number of female employees who have a CPA. 9. A pharmaceutical manufacturer has projected net p rofits for a new drug that is being released to the market over the next five years: Year Net Profit NPV = (1+) 1 $(300,000,000) ? 2 $(145,000,000) ? 3 $50,000,000 ? 4 $125,000,000 ? 5 $530,000,000 ? TOTAL A fixed cost of $80,000,000 has been incurred for research and development (in year 0). a) Use the spreadsheet to find the net present value for each year of these cash flows for a discount rate of 3% . Use the given formula above. b) Using the Excel function NPV, c alculate the overall NPV of the business. Is it a viable business? Explain your answer. 10. Define range names for all the data and model entities in the following spreadsheets and apply to the formulas in the model a) Break -Even Decision Model b) Crebo Manufacturing Model