Question Set 4:
1) From October 12th until the 30th (3 weeks), track the stock price of the company you are analyzing. Create an excel file to do this (just like financial accounting for those students that took that course with me). The difference in the opening and closing price, multiply by 1,000.
Use this excel file to make your 4th quarter sales forecast.
Question Set 5:
1) Take a screenshot of your most recent balance sheet and income statement and enter each on a separate excel worksheet. Write out this data into another worksheet. You will need to link this data to sheets of your master budget.
2) With your three months of sales forecast you now have to find the data to make your master budget. Below is a list of the data points you will have to find using your company’s most recent financial statements
– Total fixed expsenses (Divide the total by 12 to get the monthly total)
– The percentage of variable costs to sales revenue
– The monthly depreciation expense (Divide the total by 12 to get the monthly total)
– A cost of goods sold percentage
– Income tax rate for the budgeted income statement
– Interest rate on debt for the capital budget
See attached excel sheet as a guide.
Question Set 6
Using the data above, you now have to start the schedules necessary to make the 3-month master budget.
Items you will use:
1) Sales are 40% Cash and 60% Credit. Of the credit sales, 30% are collected the next month, 40% the month after, and 30% the month after that.
2) Your account receivable total on the balance sheet will be collected as follows: 40% in October, 40% in November, 15% in December, and 5% is uncollectible.
3) 50% of your account payable total is inventory bought on account which you pay for the next month.
4) Whatever your inventory total is on your balance sheet, you must keep 20% of this as ending inventory each month (Like the example we did in class).
5) You must maintain 10% of your cash total from you balance sheet in your cash account (This applies to the cash budget).
*For this submission, you have to complete the cash and capital budgets. You must explain whether you are buying long term assets and why? Or are you paying off short term debt. If you don’t have cash to do either of these, explain what is causing this.