Project 1 Instructions Starting File: Project 1 – Budget Plan – Student.xlsx You
ID: 3782731 • Letter: P
Question
Project 1 Instructions Starting File: Project 1 – Budget Plan – Student.xlsx You are developing a budget for a young married couple, Shawna and Antwon Davis, who are trying to save money toward the purchase of a home. They need to monitor their progress by tracking their monthly income and their monthly expenses. Follow the steps to produce the final results shown in Figure 1 at the end of this document. Notes: • This workbook has 2 worksheets: the Documentation worksheet (for the history of this workbook), and the Budget worksheet. Be sure to work on the correct worksheet as indicated in the instructions. • You should use cell references in all formulas – do not re-enter data. • If your version of Excel does not have the same names for the styles referenced here, select a style that gives an equivalent appearance (use the image at the end of this document to verify). • Remember: Project work must be your own individual work – no group work allowed! Steps: Download the starting file from the Assignment Dropbox and save it on your computer. Then open it to work on it. 1. In the Documentation worksheet, enter your name in cell B3 and the date you created this workbook in cell B4. 2. Enter a brief statement for the purpose of this workbook in cell B5. 3. Merge cell A1 across cells A1:E1. Left-align it. Format it with the green Accent6 cell style (or equivalent), then increase the font size to 18. 4. Format cells A3:A5 with the green Accent6 cell style, and increase the font size to 12. 5. Merge cell B3 across cells B3:E3, and left-align it. Add a border to the merged cell. 6. Format cells B4:B5 in same manner as B3. Switch to the Budget worksheet. Use formulas with cell references to complete any required calculations in these steps. 7. Enter “January” in cell C4. Use the copy handle in cell C4 to fill the rest of the month values in cells D4:N4. 8. Enter a formula to calculate the total income for January in cell C7. Use the copy handle in cell C7 to fill in the rest of the totals in cells D7:N7. 9. The Davises estimate that they are in the 16% tax bracket. This value is shown in cell J22. To estimate the income taxes that will need to be paid at the end of the year, they want to see the monthly amount of taxes based on their income. Enter a formula in cell C9 to calculate the tax amount for January. Do likewise to calculate the rest of the tax amounts in cells D9:N9. (Hint: do not re-enter data – use a cell reference to point to the tax percentage.) 10. Enter a formula in cell C16 to calculate the total expenses for January. Use the copy handle in cell C16 to fill the rest of the totals in cells D16:N16. 11. Enter a formula in cell C17 to calculate the net cash flow for January. Net cash flow is the amount of income less the amount of expenses. Use the copy handle in cell C17 to fill the rest of the totals in cells D17:N17. Now you will complete the Year-End Summary area. Note that ALL formulas in the Year-End Summary area must reference the monthly values (not other cells in the summary area). 12. Enter a formula in cell C22 to calculate Antwon’s total income for the entire year. 13. Enter a formula in C23 to calculate Shawna’s total income for the year. 14. Enter formulas to calculate the Average, Maximum, and Minimum income for both of them in cells D22:F23. 15. Enter formulas to calculate the overall total, average, maximum, and minimum income in cells C24:F24. 16. Enter formals in C26:F32 to calculate the total, average, maximum, and minimum of each type of expense listed in B26:B32. a. Enter formulas in C34:F34 to calculate the overall sum, average, maximum, and minimum, using the original data. 17. Merge cell A4 across A4:B4, and left-justify it. Apply the Heading 2 cell style. 18. Center the contents of cells C4:N4. Apply the Heading 2 cell style. Adjust the column widths as needed to ensure that all of the month values display properly. 19. Merge cell A21 across A21:B21 and left-justify it. Apply the Heading 2 cell style. 20. Center the contents of cells C21:F21. Apply the Heading 2 cell style. 21. Merge cell A5 across A5:A7. Rotate the text up, and center and middle-align it. Apply cell style green Accent6. 22. Merge cell A9 across A9:A16. Rotate the text up, and center and middle-align it. Apply cell style orange Accent2. 23. Merge cell A22 across A22:A24. Rotate the text up, and center and middle-align it. Apply cell style green Accent6. 24. Merge cell A26 across A26:A33. Rotate the text up, and center and middle-align it. Apply cell style orange Accent2. 25. Merge cell A17 across A17:B17, and left-justify it. Apply cell style blue Accent5. 26. Merge cell A34 across A34:B34, and left-justify it. Apply cell style blue Accent5. 27. Apply cell style 20% - Accent6 to cells B7:N7 and B24:F24. 28. Apply cell style 20% - Accent2 to cells B16:N16 and B33:F33. 29. Apply cell style 40% - Accent1 to cells C17:N17 and C34:F34. (corrected) 30. Apply cell borders to A5:N17 and A22:F34. 31. Adjust the row height for rows 8 and 25 to 10. 32. Change only the bottom border for cells B7:N7, B16:N16, B24:F24, and B33:F33 to a double line with color Black 33. Format all numeric values with Accounting style, with no decimal places. 34. Merge A1 across cells A1:N1, and apply the Heading 1 style. 35. Set the page orientation to Landscape, and the margins to Narrow. Set the print area to be A1:N34. Adjust the page breaks so that the entire budget will print on a single page, using Page Break Preview. 36. Create a page footer with your name in the left portion of the footer, and the date you completed this project in the right portion of the footer. 37. Save your file, close Excel, and submit your file into the Project 1 link in Blackboard.
Explanation / Answer
Use "Merge and center" option to merge the cells.
To put a formula in a C2 and D2 in the cell B2, then type "=C2+D2" in the cell B2, the sum of the contents of the cell C2 and D2 will be displayed in the cell B2.
To left justify the contents,select the text and select the option of left alignmrnt from the top toolbar.
To Save the excel file, click on the file from the toolbar and select the save option, give the name and the location, where the file needs to be saved.
These are some of the things you need while making the project.You can comment below, if you need anything else.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.