Lab Week 1 – 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 don’t 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

3. In cell A1 enter the following text: Personal Monthly Expense Worksheet
4. In cell A2:E2 enter the following header titles
   1. A2 = Monthly Living Expenses
   2. B2:D2 = Type January in cell B2 then use the Autofill Handle to automatically populate February and March into cells C2:D2
   3. E2 = 3 Month Total
5. Enter the following data into your worksheet beginning in cell A3 to cell D11:

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

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 January’s expenses
20. Select cell B12 and use the Autofill Handle to copy the Function to cells C12, D12, and E12

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

BE PREPARED TO ANSWER THE FOLLOWING QUESTIONS:

1. Submit your completed spreadsheet, “Personal Monthly Expenses Workbook.xlsx” — has to be separate canvas thing I think

1. What is the exact content of cell E12 (type the formula exactly not the value)
2. What is the exact content of cell B12 (type the formula exactly not the value)
3. 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?