Hi, I have an assignment in excel. I just need someone to check it to make sure I have it fine and that I followed instructions. Some of the formulas really have me stressed out. I would like to know what corrections are made as I am sure there will be lots. I know there are a few loose ends here that need to be done. I would appreciate for you to do this. Thank you so much.
Microsoft Word - MC1242 Computer Applications II Assignment 1.docx Assignment 1 (20%) Important Instructions to Students: • The data files for this assignment can be found in the Dropbox. • Create a folder titled Assignment 1 in which to save all files for this assignment. By creating this folder, you will help prevent loss of files. • Save your files as indicated in the assignment. As you work through each exercise, save regularly. • For all exercises, bold row headings and bold and center column headings. Ensure all data is clearly visible. • Where appropriate, apply the Accounting number format. • Before submitting your six (6) files, check your work carefully for errors. 1|Page Exercise #1 (5%) Open a new workbook and save it as Yourlastname Yourfirstname 1 Homework Plan in the Assignment 1 folder. 1. Create a worksheet based on the table given below, which indicates hours of study for each course throughout the week. 2. Enter formulas to calculate the Total Study Time for the last column. 3. Format the worksheet by following the instructions on page 1. 4. Save your file. Exercise #2 (15%) You are the human resources manager for a company called Mighty Tech Solutions, which provides support and repair services for computer related problems. You are in the process of creating a worksheet for the weekly employee payroll data. Once you have completed the worksheet, you will need to format it to give it a professional look. Open a new workbook and save it as Yourlastname Yourfirstname 2 Mighty Tech Solutions in your Assignment 1 folder. 1. Enter the data shown in the table above in the workbook. 2. Enter formulas to calculate the pay for each employee. 3. Add the worksheet title “Payroll Data” in row 1. Merge and center the title over the column labels. 4. Change the font and font size of the worksheet title, as appropriate, to emphasize it. 5. Make sure all columns are wide enough to display all the data and labels. 6. Save your file. Exercise #3 (25%) Your company, Delicious Scoops, is a retailer of all-natural ice cream. The following table lists sales for the best-selling flavors over a one-year period. Open a new workbook and save it as Yourlastname Yourfirstname 3 Delicious Scoops in your Assignment 1 folder. 1. Think about where you want the totals and averages for each flavor and each quarter. Where do you want totals to appear? Where do you want averages to appear? 2. Enter the data shown in the table in the workbook. 3. Calculate totals for sales of each flavor and each quarter. 4. Calculate averages for sales of each flavor and each quarter. 5. Format your data as desired, being sure to follow instructions on page 1. 6. Copy your data to a new worksheet. You want to calculate the impact of a 35% increase in sales for each quarter. 7. Type Increase in cell A11 of your new worksheet. 8. Enter .35 in cell A12. Use this cell in your calculations to determine the impact of a 35% increase. 9. Results for each quarter should be displayed in cells B12, C12, D12 and E12. 10. Bold the data in cells A11 and A12. 11. Save your file. Exercise #4 (25%) You are the owner of Build-To-Fit, a home renovation construction firm. To track your existing business and plan for future business, you want to build an Excel worksheet that analyzes all jobs. The categories you need to track for each job are job number, description, start and end dates, length of job, estimated and actual job costs, and amount the job was over estimate. In addition to tracking these job categories, you want to calculate summary statistics for the jobs. Open a new workbook and save it as Yourlastname Yourfirstname 4 Build-to-Fit in your Assignment 1 folder. 1. Rename the Sheet1 tab Job Summary. 2. Enter the title Build-To-Fit and in a separate cell enter the subtitle Job Tracking. 3. Enter labels and worksheet data for 10 jobs. Type column labels and enter your own data for Job Number, Description, Start Date, End Date, Length of Job, Estimated Job Costs, Actual Job Costs, and Amount Over. The data must make sense: Job number: can be any combination of numbers, not letters Job Description: must be text, not numbers; i.e. roof repair, basement repair Dates: must be in date format 4. Enter a formula to calculate the Length of Job based on the start and end dates. 5. Use an IF function to calculate the amount over estimate. Only show the amount over, not under. 6. Format the worksheet, following the instructions on page 1, and being sure to appropriately merge and center the title and subtitle. 7. Save your file on a single page using landscape orientation. Exercise #5 (5%) You are the office manager for a small office supplies company. You have created a workbook with product status and information and plan to share it with other members of the company. You want to make it easy for others to find and use the workbook, but you do not want anyone to be able to modify the workbook. You will use the Document Properties Panel to add information about the file, protect the file from changes, and add metadata to make the file easy to find. Open the workbook Supplies from the data files provided. Save it as Yourlastname Yourfirstname 5 Status in your Assignment 1 folder. 1. Using the Document Properties Panel, enter the following: • Office Manager as the author • The keywords Toner, Developer • The title Status of Toner and Developer 4|Page 2. Format the worksheet as desired and then save the workbook. 3. Mark the file as final and save the file. Exercise #6 (25%) In managing your home business, Kidz Books Direct to You, you decide to make use of a lookup table to fill in order information. You have already created a book order lookup table in the workbook titled Orders. Your next task is to use this table to fill in the book title, category, and unit price information based on the item number entered. You have assigned a unique item number to each book you carry. Open the workbook Orders from the data files provided and save it as Yourlastname Yourfirstname 6 Custom Books in your Assignment 1 folder. 1. Create a table from the Book Order Lookup Table. 2. Enter 1005 in cell E2. In cell F2, build a VLOOKUP formula to pull the book title, based on the item number entered. In cell I2, build a VLOOKUP formula to pull the category. In cell J2, build a VLOOKUP formula to pull the unit price. 3. Enter the following data as indicated: 1004 in cell E3 1009 in cell E4 1003 in cell E5 1006 in cell E6 1002 in cell E7 1008 in cell E8 1001 in cell E9 1002 in cell E10 1007 in cell E11 4. Copy the VLOOKUP formulas down their respective columns. 5. Preview the worksheet and make any necessary formatting changes. Columns B, C, D, G, and H are blank. 6. Save your file.