0

    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?

                                                                                                                                      Order Now