Assignment 1: Video/ Essay: The New Heroes – ‘Dreams Of Sanctuary’ (January 26, due January 29) (5%) Dreams of Sanctuary features social entrepreneurs working to address a social problem. Who are those social entrepreneurs? What social problems are they
January 24, 2021
Managing the IS Organization Options
January 24, 2021
Show all

Excel spreadsheet budget planning

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?

Leave a Reply

Your email address will not be published. Required fields are marked *