group project final paper
tbanes1
Accounting1A-41.xlsxCharts
Charts: Charts are a way to easily visualize your data sets in order to present findings or to gain further insights. There are a wide variety of chart types that you can utilize in Microsoft Excel. The most common types used are bar/column charts to show comparisons of values and line charts to show trends. Below is an example of how to set up a chart: 1. The first step is to select the data you would like to chart out. In general, you want to format your data in a way that you have column headers like days and then row headers such as your cost or company. See the below as an example: 2. Highlight the data you what to create a chart of: 3. Go to the “Insert” tab and you will see a section titled “Charts” 4. Click on “Recommended Charts”, which will generate an “Insert Chart” window a. Within this window you have two tabs: “Recommended Charts” and “All Charts” 1. The “Recommended Charts” tab will give you several charts that are recommended for the specific data set you have chosen 2. The “All Charts” tab will let you dictate the type of chart you want. You will see that on this tab, the left-hand side shows all the different chart types and as you click on them you will see the right-hand pane generate the different subtypes of charts available for that group. b. On the “All Charts” tab, choose the “Line” option from the left-hand pane 1. On the right-hand side look through the various options. If we want to show a trend line with the data points marked, we would choose the option titled “Line with Markers”. Chose this option and click “OK” 2. The below chart will generate 5. You will see here that this is a default chart. You can make many changes such as the following: a. Double click in the “Chart Title” and add your own chart name b. You can right click on either the X (here Day 1, Day 2,….) or Y (here 0-100) axes and click “Format Axis” pane will generate giving you a plethora of options to modify the axes c. Another set of easy tools is to click on the chart and within the “Chart Design” ribbon click on “Add Chart Element” within the “Chart Layouts” section 1. Within this section you can add titles to the axes, add other labels, create legends, etc.
Budget Launch Page
| Data Analytics Budgeting | ||
| Beginning Balance Sheet | ||
| Master Budget | ||
| Budgeting Assumptions | ||
| Sales Budget | ||
| Merchandise Purchases Budget | ||
| Selling & Admin Budget | ||
| Cash Budget | ||
| Budgeted Income Statements | ||
| Budgeted Balance Sheets | ||
| Cash Flow vs. Net Income | ||
Beginning Balance Sheet
| Earrings Unlimited | ||
| Balance Sheet | ||
| Beginning of the Quarter | ||
| Assets | ||
| Current assets: | ||
| Cash | $ 89,000 | |
| Accounts receivable | 708,700.00 | |
| Merchandise inventory | 149,600.00 | |
| Total current assets | $ 947,300 | |
| Plant and equipment: | ||
| Buildings and equipment | 1,100,000.00 | |
| Prepaid insurance | 28,500.00 | |
| Plant and equipment, net | 1,128,500.00 | |
| Total assets | $ 2,075,800 | |
| Liabilities and Stockholders' Equity | ||
| Accounts payable | $ 115,000 | |
| Dividends payable | 26,250 | |
| Common stock | 1,100,000.00 | |
| Retained earnings | 834,550.00 | |
| Total liabilities and stockholders' equity | 2,075,800 | |
Budgeting Assumptions
| Earrings Unlimited | ||||||
| Budgeting Assumptions | ||||||
| For The Second Quarter | ||||||
| All 12 Months | April | May | June | |||
| Sales Budget: | ||||||
| Budgeted unit sales | 68,000.00 | 103,000.00 | 53,000.00 | |||
| Selling price per unit | $ 19.00 | |||||
| Percentage of sales that are collected in the month of sale | 20% | |||||
| Percentage of sales that are collected in the month after sale | 70% | |||||
| Percentage of sales that are collected in the second month after sale | 10.00% | |||||
| Merchandise Purchases Budget: | ||||||
| Cost of goods sold as a percent of sales | 29% | |||||
| Percentage of next month's cost of goods sold in ending merchandise inventory | 40% | |||||
| Percentage of merchandise purchases that are paid for in the month of purchase | 50% | |||||
| Percentage of merchandise purchases that are paid for in the month after purchase | 50.00% | |||||
| Selling and Administrative Expense Budget: | ||||||
| Variable selling and administrative expense per unit | $ 0.76 | |||||
| Fixed selling and administrative expense per month: | ||||||
| Advertising | $ 350,000 | |||||
| Salaries | $ 136,000 | |||||
| Insurance | $ 4,500 | |||||
| Rent & Utilities | $ 47,500 | |||||
| Depreciation | $ 29,000 | |||||
| Cash Budget | ||||||
| Minimum cash balance | $ 65,000 | |||||
| Simple interest rate per month | 1% |
Sales Budget
| Earrings Unlimited | ||||
| Sales Budget | ||||
| For the Second Quarter | ||||
| April | May | June | Total | |
| Budgeted unit sales | 68,000.00 | 103,000.00 | 53,000.00 | 224,000.00 |
| Selling price per unit | $ 19.00 | $ 19.00 | $ 19.00 | $ 19.00 |
| Sales | $ 1,292,000 | $ 1,957,000 | $ 1,007,000 | $ 4,256,000 |
| Schedule of Expected Cash Collections | ||||
| April | May | June | Total | |
| Cash collections from sales two months' prior | $ 55,100.00 | $ 81,700.00 | $ 129,200.00 | |
| Cash collections from prior month's sales | $ 571,900 | $ 904,400 | $ 1,369,900 | |
| Cash collections from current month's sales | 258,400.00 | 391,400.00 | 201,400.00 | |
| Total cash collections | $ 885,400 | $ 1,377,500 | $ 1,700,500 | $ 3,963,400 |
Merchandise Purchases Budget
| Earrings Unlimited | ||||
| Merchandise Purchases Budget | ||||
| For This Year | ||||
| April | May | June | Quarter | |
| Budgeted unit sales | 68000 | 103000 | 53000 | 224000 |
| Add desired ending merchandise inventory | 41200 | 21200 | 13200 | 13200 |
| Total needs | 109,200.00 | 124200 | 66,200.00 | 237200 |
| Less beginning merchandise inventory | 27,200.00 | 41,200.00 | 21,200.00 | 27,200.00 |
| Required merchandise units | $ 82,000.00 | $ 83,000.00 | $ 45,000.00 | 210000 |
| $ 5.50 | $ 5.50 | $ 5.50 | $ 5.50 | |
| Required merchandise purchases | $ 451,000.00 | $ 456,500.00 | $ 247,500.00 | $ 1,155,000.00 |
| Schedule of Expected Cash Disbursements for Merchandise Purchases | ||||
| April | May | June | Quarter | |
| Cash disbursements related to prior month's purchases | $ 115,000 | $ 225,500 | $ 228,250 | |
| Cash disbursements related to current month's purchases | 225,500.00 | 228,250.00 | 123,750.00 | |
| Total cash disbursements for merchandise purchases | $ 340,500 | $ 453,750 | $ 352,000 | $ 1,146,250 |
Cash Budget
| Earrings Unlimeted | ||||
| Cash Budget | ||||
| April-June | ||||
| April | May | June | Quarter | |
| Beginning cash balance | 89,000 | 65,470 | 353,940 | 89,000 |
| Collections from customers | 885,400 | 1,377,500 | 1,700,500 | 3,963,400 |
| Total cash available | 974,400 | 1,442,970 | 2,054,440 | 4,052,400 |
| Less cash disbursements: | ||||
| Merchandise purchases | 340,500 | 453,750 | 352,000 | 1,146,250 |
| Advertising | 350,000 | 350,000 | 350,000 | 1,050,000 |
| Rent | 33,000 | 33,000 | 33,000 | 99,000 |
| Salaries | 136,000 | 136,000 | 136,000 | 408,000 |
| Commissions | 51,680 | 78,280 | 40,280 | 170,240 |
| Utiities | 14,500 | 14,500 | 14,500 | 43,500 |
| Equiment Purchases | 0 | 23,500 | 55,000 | 78,500 |
| Dividends Paid | 26,250 | 0 | 0 | 26,500 |
| Total cash disbursements | 951,930 | 1,089,030 | 980,780 | 3,021,740 |
| Excess (deficiency) of cash available over disbursements | 22,470 | 353,940 | 1,073,660 | 1,030,660 |
| Financing: | ||||
| Borrowings (at the beginnings of months) | 43,000 | 0 | 0 | 43,000 |
| Repayments | 0 | 0 | -43,000 | -43,000 |
| Interest (at 1% per month) | 0 | 0 | -1,290 | -1,290 |
| Total financing | 43,000 | 0 | -44,290 | -1,290 |
| Ending cash balance | 65,470 | 353,940 | 1,029,370 | 1,029,370 |
| Mininmum Cash Requirement | 65,000 | 65,000 | 65,000 | 65,000 |
Budgeted Income Statements
| Earrings Unlimited | ||
| Budgeted Income Statements | ||
| For the Three Month Period Ending June 30 | ||
| (Contribution costing basis) | ||
| Sales | 4,256,000 | |
| Variable expenses: | ||
| Commission | $ 170,240 | |
| Cost of goods sold | 1,232,000.00 | |
| 1,402,240 | ||
| Contribution margin | 2,853,760 | |
| Fixed expenses: | ||
| Advertising | 1,050,000.00 | |
| Rent | 99,000.00 | |
| Salaries | 408,000.00 | |
| Utilities | 43,500.00 | |
| Depreciation | 87,000.00 | |
| Insurance | 13,500.00 | |
| 1,701,000 | ||
| Net operating Income | 1,152,760.00 | |
| Interest expense | 1,290.00 | |
| Net Income | 1,151,470 | |
Budgeted Balance Sheets
| Earrings Unlimited | |
| Budgeted Balance Sheets | |
| June 30, 2021 | |
| Assets | |
| Cash | 1,029,370 |
| Accounts Receivable | $ 1,001,300 |
| Plant and equipment: | $ 1,091,500 |
| Inventory | $ 72,600 |
| Prepaid insurance | $ 15,000 |
| Total assets | $ 3,209,770 |
| Liabilities and Stockholders’ Equity | |
| Accounts payable | $ 123,750 |
| Dividends payable | $ 26,250 |
| Common stock | 1,100,000 |
| Retained earnings | 1,959,770 |
| Total liabilities and stockholders' equity | 3209770.00 |
Day 1 Day 2 Day 3
Cost A 55 65 50
Cost B 88 80 85
Cost C 60 70 65
