PERSONAL MONTHLY BUDGET WORKSHEET
BACKGROUND
You have been baking most of your life and your friends, family, and now many of the people in your local community ask you to bake cakes, cupcakes, and cookies and are willing to pay you for your delicacies. You have been working out of your home part-time while still holding down a full-time job. Recently two owners of a local bakery have reached out to you after trying some of your baked goods and asked you to join their food co-op, Longmeadow Baking Company.
Currently, the partners of Longmeadow Baking Company only bake loaves of bread, muffins, and pastries but want to include cakes, cupcakes, and cookies, and you are the perfect fit for them. They would allow you to use their equipment that you dont have like ovens and mixers for a small fee and would want to split the remaining cost of the business with you equally in thirds. You have always thought about opening a bakery, and you need to determine if this is a good fit for you and a viable option for you to achieve the dream you have to be a full-time Baker.
GOAL
For this assignment, you are going to calculate your personal monthly expenses for January, February, and March using Microsoft Excel to determine whether or not you are able to join the Longmeadow Baking Company and start your bakery business while continuing to pay your bills and live comfortably.
Because you have already determined that Longmeadow Baking Company has good foot traffic, location, a great reputation, and an established presence in the community you realize this opportunity will give you the accelerated growth in your own business you would not be able to achieve on your own. Completing the monthly expense worksheet will allow you to determine if you can afford to pay yourself a $1,000 per week salary and accept this opportunity.
You will demonstrate the following Excel skills with this exercise:
Starting Excel and creating a new workbook
Navigating worksheet
Saving workbooks
Entering and editing data
Autofill
Adjusting columns and rows
Formatting data and cells (font, alignment, number, styles)
Data alignment (Merge and Center, Wrap text, justification)
Borders
Autosum
The completed spreadsheet will:
PART 1: CREATING PERSONAL MONTHLY EXPENSES
1. Open a new workbook in Microsoft Excel
2. Save the workbook as Personal Monthly Expenses Workbook.xlsx
ENTER STARTING DATA
3. In cell A1 enter the following text: Personal Monthly Expense Worksheet
4. In cell A2:E2 enter the following header titles
a. A2 = Monthly Living Expenses
b. B2:D2 = Type January in cell B2 then use the Autofill Handle to automatically populate February and March into cells C2:D2
c. E2 = 3 Month Total
5. Enter the following data into your worksheet beginning in cell A3 to cell D11:
January February March
Rent/Mortgage 850 850 850
Insurance 65 65 65
Credit Card Payments 250 250 250
Car Payment 185 185 185
Groceries 334 425 372
Household Utilities 218 248 189
Clothing 85 279 112
Eating Out 189 244 201
Entertainment & Travel 46 76 589
ADJUST FORMAT, FONTS, ALIGNMENT, COLUMN AND ROW SIZING
6. Change the Column Width of column A to 28
7. Change the Column Width of columns B:E to 14
8. Change the Row Height of row 1 to 50
9. Merge and Center cells A1:E1
10. In cell A1, place your cursor in front of the word Worksheet and move the word Worksheet onto a second line below Personal Monthly Expense
11. Change the Fill Color of cell A1 to #FF3399 (found in the more colors options menu)
12. Change the Font Color in cell A1 to White, 16 point, and Bold
13. Change the Fill Color of cells A2:E2 to Blue Accent 1 Lighter 40%
14. Change the Font Color to White and Bold
15. Apply the Accounting format to cells B3:D11
CALCULATE 3 MONTH TOTALS AND MONTHLY TOTALS USING AUTOSUM FUNCTION
16. In cell E3, Calculate the 3 month total of Jan,Feb, & Mar Rent/mortgage expenses by using the AUTOSUM Function.
17. Select cell E3 and use the Autofill Handle to copy the Function through the remaining cells in the column up to cell E11
18. In cell A12 enter the word Totals and Right-Align the text
19. in cell B12 use the AUTOSUM Function to calculate the total for Januarys expenses
20. Select cell B12 and use the Autofill Handle to copy the Function to cells C12, D12, and E12
ADD BORDERS AND CELL STYLES TO IMPROVE READABILITY
21. Select cells B12:E12 and apply the Total Cell Style (Styles Group on the Home Tab)
22. Select cells A12:E12 and make the text and numbers Bold
23. Select cells A1:E11 and apply the All Borders Border Style
24. Select cell A2:E2 and apply the Thick Outside Borders Border Style
25. Select cell A1 and apply the Thick Outside Borders Border Style
26. Save your workbook as Personal Monthly Expenses Workbook.xlsx and close Microsoft Excel
Your work should look like below when you are done with this assignment
Worksheet View
ANSWER THE FOLLOWING QUESTIONS:
Complete the answers for the following questions
1. Submit your completed spreadsheet, Personal Monthly Expenses Workbook.xlsx
2. Your original assumption is that you will be able to take a salary of $1,000/week from the business. Will this salary be sufficient? Explain your answer. Additionally suggest how you might adjust your spreadsheet to include your salary and the associated calculations that will help you answer if you should move forward with your new business idea.
3. What is the overall total expense for all 3 months?
4. What month had the highest total expenses?
5. Which expenses category had the highest 3 month total?
6. Which expenses category had the lowest 3 month total?
7. The Total Cell Style was applied to which cell range?
8. What number format was used in the table?
9. What is the exact content of cell E5 (the formula exactly not the value)
10. What is the exact content of cell E12 (type the formula exactly not the value)
11. What is the exact content of cell B12 (type the formula exactly not the value)
12. You just found a bill from February for a restaurant that you forgot to record on your original spreadsheet. The bill was for $253, making the actual expense for Feb = $497. What is the monthly total for Feb now?
