Microsoft Excel (Practice)

Shelly Cashman Excel 365/2021 | Module 1: End of Module Project 2 RetailPro COMPLETE A TRAVEL EXPENSE WORKSHEET GETTING STARTED • Save the file SC_EX365_2021_EOM1-2_FirstLastName_1.xlsx as SC_EX365_2021_EOM1-2_FirstLastName_2.xlsx o Edit the file name by changing “1” to “2”. o If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically. • With the file SC_EX365_2021_EOM1-2_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet. o If cell B6 does not display your name, delete the file and download a new copy. PROJECT STEPS 1. Martin Hernandez is an operations coordinator for RetailPro, a sales and marketing company that works with retail businesses throughout the southwestern United States. Martin travels frequently to consult with clients and make sure they are arranging stores and featuring products in the most profitable way. He has created a worksheet to track his travel expenses and asks for your help in completing it. Go to the Travel Expenses worksheet. Merge the cells in the range B2:I2. In cell B13, enter 5/3/2024 as the date. 2. Apply the Accent3 cell style to the range B10:I10. 3. In cell C16, use Retail conference in Houston as the complete entry for the cell. In cell D11, enter 65 as the number of Miles Driven. 4. In cell E11, enter a formula without a function that multiplies the number of miles driven (cell D11) by the current mileage rate (0.55). Use the Fill Handle to fill the range E12:E16 with the formula in cell E11. 5. Apply the Accounting number format to the range E11:I16. 6. In cell E17, enter a formula using the SUM function to sum the Mileage Expense (the range E11:E16). Fill the range F17:I17 with the formula in cell E17. 7. In cell B21, change the font to Franklin Gothic Book (Body). Copy the value in cell C5 and paste it in cell D21. 8. Change the style of the pie chart in the range B23:E36 to Style 8. Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project. (Note: The value in cell B13 for travel expenses worksheet may differ from that shown below.)   Final Figure 1: Travel Expenses Worksheet #ExcelPractice #SamProject #Cashman #Module

Shelly Cashman Excel 365/2021 | Modules 1-3: SAM Capstone Project 1a Ensight Healthcare Consultants CREATE FORMULAS WITH FUNCTIONS GETTING STARTED • Save the file SC_EX365_2021_CS1-3a_FirstLastName_1.xlsx as SC_EX365_2021_CS1-3a_FirstLastName_2.xlsx o Edit the file name by changing “1” to “2”. o If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically. • With the file SC_EX365_2021_CS1-3a_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet. o If cell B6 does not display your name, delete the file and download a new copy. PROJECT STEPS 1. Carla Arranga is a senior account manager at Ensight Healthcare Consultants, a consulting firm that works with hospitals, clinics, and other healthcare providers around the world. Carla has created a workbook summarizing the status of the consulting project for Everett Hospital. She asks for your help in completing the workbook. Go to the Project Status worksheet. Unfreeze the first column since it does not display information that applies to the rest of the worksheet. 2. In cell J1, enter a formula using the NOW function to display today's date. Apply the Short Date number format to display only the date in the cell. 3. Format the worksheet title as follows to use a consistent design throughout the workbook: a. Fill cell B2 with the Dark Red, Accent 6, Lighter 40% shading color. b. Change the font color to White, Background 1. c. Merge and center the contents of cell B2 across the range B2:H2. d. Use AutoFit to resize row 2 to its best fit. 4. Format the billing rate data as follows to suit the design of the worksheet and make the data easier to understand: a. Italicize the contents of cell I2 to match the formatting in cell I1. b. Apply the Currency number format to cell J2 to clarify that it contains a dollar amount. 5. Format the data in cell A4 as follows to display all of the text: a. Merge the cells in the range A4:A13. b. Rotate the text up in the merged cell so that the text reads from bottom to top. c. Middle-align and center the text. d. Remove the border from the merged cell. e. Resize column A to a width of 4.00. 6. Format the data in row 4 as follows to show that it contains column headings: a. Change "Description" to use Service Description as the complete column heading. b. Apply the Accent 6 cell style to the range B4:H4. c. Use AutoFit to resize column D to its best fit. 7. Carla wants to include the actual dollar amount of the services performed in column E. Enter this information as follows: a. In cell E5, enter a formula without using a function that multiplies the actual hours (cell D5) by the billing rate (cell J2) to determine the actual dollar amount charged for general administrative services. Include an absolute reference to cell J2 in the formula. b. Use the Fill Handle to fill the range E6:E13 with the formula in cell E5 to include the charges for the other services. c. Format the range E6:E13 using the Comma number format and no decimal places to match the formatting in column F. 8. Carla needs to show how much of the estimate remains after the services performed. Provide this information as follows: a. In cell G5, enter a formula without using a function that subtracts the actual dollars billed (cell E5) from the estimated amount (cell F5) to determine the remaining amount of the estimate for general administrative services. b. Use the Fill Handle to fill the range G6:G13 with the formula in cell G5 to include the remaining amount for the other services. c. Format the range G6:G13 using the Comma number format and no decimal places to match the formatting in column F. 9. Carla also wants to show the remaining amount as a percentage of the actual amount. Enter this information as follows: a. In cell H5, enter a formula that divides the remaining dollar amount (cell G5) by the estimated dollar amount (cell F5). b. Copy the formula in cell H5 to the range H6:H14, pasting only the formula and number formatting to display the remaining amount as a percentage of the actual amount for the other services and the total. 10. Calculate the project status totals as follows: a. In cell D14, enter a formula using the SUM function to total the actual hours (range D5:D13). b. Use the Fill Handle to fill the range E14:G14 with the formula in cell D14. c. Apply the Accounting number format with no decimal places to the range E14:G14. #SamProject #Excel #CapStone

Billabong Tech uses the internal rate of return​ (IRR) to select projects. Calculate the IRR for each of the following projects and recommend the best project based on this measure. Project​ T-Shirt requires an initial investment of ​$24 comma 000 and generates cash inflows of ​$9 comma 000 per year for 6 years. Project Board Shorts requires an initial investment of ​$27 comma 333 and produces cash inflows of ​$10 comma 000 per year for 7 years. #Techniques #FinanceHelp #InternalRateOfReturn #Finance

Herky Foods is evaluating a new wrapping machine. With the​ machine, Herky will save money on packaging in each of the next 5​ years, producing the series of cash inflows shown in the following​ table: LOADING....   The initial investment is ​$1.59 million. Using a 9​% discount​ rate, determine the net present value​ (NPV) of the machine given its expected cash inflows. Based on the ​project's NPV​, should Herky make this​ investment? #NetPresentValue #Excel #MicrosoftExcel #Technqiues

You take a fixed-rate mortgage for $140,000 at 6.75% (annual interest rate) for 30 years, monthly payments. At the end of year two, you unexpectedly inherit $25,000 from your favorite grandmother. You decide to apply this $25,000 to the principal balance of your loan. a. What is the mortgage monthly payment after you apply the $25,000 to the principal? b. How much interest do you save over the life of the loan by doing this assuming that the mortgage is held to maturity? #Mortgage #Excel #Interests #Payments

Present​ value: Mixed streams   Consider the mixed streams of cash flows shown in the following​ table a.  Find the present value of each stream using a 6​% discount rate. b.  Compare the calculated present values and discuss them in light of the undiscounted cash flows totaling ​$70 comma 000 in each case. Is there some discount rate at which the present values of the two streams would be​ equal? #FInanceHelp #AccountingHelp #Excel #FutureValue #Investments

Value of a mixed stream   Harte​ Systems, Inc., a maker of electronic survillance​ equipment, is considering selling the rights to market its home security system to a​ well-known hardware chain. The proposed deal calls for the hardware chain to pay Harte ​$32000 and ​$25 000 at the end of years 1 and 2 and to make annual​ year-end payments of ​$12 000 in years 3 through 9. A final payment to Harte of ​$20 000 would be due at the end of year 10. a.  Select the time line that represents the cash flows involved in the offer. b.  If Harte applies a required rate of return of 9​% to​ them, what is the present value of this series of​ payments? c.  A second company has offered Harte an immediate​ one-time payment of ​$110 000 for the rights to market the home security system. Which offer should Harte​ accept? #FinanceHelp #Excel #AccountingHelp

Đây là video cách giải chi tiết từng bước một về cách sử dụng phần mềm để tìm Tối Ưu Hóa (Không có trong sách giáo khoa Toán hiện tại) #MicrosoftExcel #WolframAlpha #CachGiai #Optimization #Functions #Inequality #BatPhuongTrinh

Many regions in North and South Carolina and Georgia have experienced rapid population growth over the last 10 years. It is expected that the growth will continue over the next 10 years. This has motivated many of the large grocery store chains to build new stores in the region. The Kelley's Super Grocery Stores Inc. chain is no exception. The director of planning for Kelley's Super Grocery Stores wants to study adding more stores in this region. He believes there are two main factors that indicate the amount families spend on groceries. The first is their income and the other is the number of people in the family. The director gathered the following sample information #Excel #Regression #DataAnalysis

Shelly Cashman Excel 365/2021 | Module 1: SAM Project 1b Diaz Marketing COMPLETE A BUDGET SUMMARY WORKSHEET GETTING STARTED • Save the file SC_EX365_2021_1b_FirstLastName_1.xlsx as SC_EX365_2021_1b_FirstLastName_2.xlsx o Edit the file name by changing “1” to “2”. o If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically. • With the file SC_EX365_2021_1b_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet. o If cell B6 does not display your name, delete the file and download a new copy. PROJECT STEPS 1. Ashley Bowman manages the New England office for Diaz Marketing, a consulting firm that develops strategies for businesses to become more profitable. She has created a worksheet summarizing the revenue and expenses for the first six months of the year, and asks for your help in determining what the New England office needs to do in the rest of the year to meet its profit goals. Go to the Budget worksheet. Cut the worksheet subtitle from cell B1 and paste it in cell A2 to display the subtitle in its expected location. 2. In cell H2, add the text consultants so that the complete text appears as "Number of consultants" and more clearly identifies the value in cell J2. 3. Enter Feb in cell C3, Mar in cell D3, Apr in cell E3, May in cell F3, and Jun in cell G3. 4. In cell J4, enter a formula that subtracts the value in cell I4 from the value in cell H4 to determine how much revenue the New England office needs to earn from July to December to reach its goal for the year. 5. Ashley wants to calculate the gross margin from January to June and for the year to date. Provide this information as follows: a. In cell B6, enter a formula that divides the value in cell B5 (the gross profit for January) by the value in cell B4 (the revenue in January). b. Use the Fill Handle to fill the range C6:H6 with the formula in cell B6 to find the gross margin for February to June and for the year to date. 6. Ashley needs to sum the sales for each New England state for the year to date. Provide this information as follows: a. In cell H9, enter a formula that uses the SUM function to total the range B9:G9 to calculate the year-to-date sales for Connecticut. b. Use the Fill Handle to fill the range H10:H12 with the formula in cell H9 to find the year-to-date sales for the other states. 7. In cell B17, enter 27,363 as the general expenses for January. 8. Ashley wants to determine how much revenue each state needs to generate from July to December to reach its target for the year. Enter this information as follows: a. In cell J9, enter a formula that subtracts the value in cell I9 from the value in cell H9 to find the amount of Connecticut sales Ashley needs to meet the goal. b. Copy the formula in cell J9, and then paste it in the range J10:J14, pasting the formulas only, to find the differences for the other states, the total, and the revenue per consultant. 9. Ashley is interested in how much revenue the nine consultants each generate on average per month and for the year to date. Find this information as follows: a. Edit cell B14 to include a formula that divides the value in cell B13 (the total sales for Connecticut in January) by the value in cell J2 (the number of consultants). Don't change the reference to cell J2 that is already there. b. Fill the range C14:H14 with the formula in cell B14 to determine the average revenue per consultant from February to June and for the year to date. 10. Ashley also needs to calculate the operating margin, which is the ratio of operating profit or loss to revenue and indicates how much the office makes after paying for expenses. Calculate the operating margin as follows: a. In cell B19, enter a formula that divides the value in cell B18 (the operating profit or loss in January) by the value in cell B4 (the revenue in January). b. Use the Fill Handle to fill the range C19:H19 with the formula in cell B19 to find the operating margin for February to June and for the year to date. 11. Ashley wants to visualize the total year to date sales separated into states. Insert a chart to provide this visualization as follows: a. Create a 2-D Pie Chart based on the nonadjacent ranges A8:A12 and H8:H12. b. Enter Total Year to Date Sales by State as the chart title. c. Reposition and resize the chart so its upper-left corner is in cell K3 and its bottom-right corner is in cell M17 d. Apply Style 11 to the chart. 12. Hide the gridlines for the Budget worksheet to make it easier to read. Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.   Final Figure 1: Budget Worksheet