We are doing a budgeting project and I am having trouble finding the formulas for some of the cells on the budgets. This is the assumption page and the budgets im struggling with. I have filled out some of the cells but not all of them.
REVENUE ASSUMPTIONS: MORE COST ASSUMPTIONS: Selling price of silk-screened shirts Cary Sue's compensation: Monthly salary Estimated number of shirts to be sold in the first flve quarters: Percentage commission of sales First quarter year 1 Amount of workday focusing on sales Second quarter year 1 Third quarter year 1 Fourth quarter year 1 First cuarter year 2 Schedule of collections: Cash collected in the quarter of the sale Building rental: Cash collected in the quarter following the sale Monthly rent of building Percent of building used for silk-gcreening process COST ASSUMPTIONS: Percent of building used for sales and adminstrative duties Purchasing assumptions: Other costs: Cost per sweatshirt purchased Cost of ink used in the silk-screen process (per shirt) Desired ending inventory of shirts as a percent of the next quarter's sales Weekly advertising cost Annual property taxes and insurance on machinery Schedule for payment of sweatshirts: To be paid in the quarter of purchase Annual property tax To be paid in the quarter following the purchase Maintenance cost per shirt Utilities monthly fixed cost Direct labor costs: Maintenance monthly fixed cost Hourly wage rata to pay college workers Silk-screening labor hours per shirt Other Items: Total equipment cost to be paid in quarler 1 and quarter 2 Depreciation expense: Desired laan amount Selling and administrative equipment quarterly depreciation Interest rate on loan Income tax rate HINTSTIPS: Four quarters in a year. Yearly amount /4= quarterly amount. Thirteen weeks in a quarter. Weekly amount * 13= quarterly amount. Complete the Silk-screen Overhead Budget. REMINDER: Make sure every cell is either a formula or a reference o another cell (or range of cells). Depreciation is given to you. Calculations for year totals are provided as well. NOTE/HINT: your variable costs here should be based upon the number of shirts sold, NOT the number of shirts 9. Using the information found in the case and the previous budgets, complete the Budgeted Income Statement. REMINDER: Make sure every cell is either a formula or a reference to another cell (or range of cells). The cell references for cost of goods sold are given to you - take time to see where this comes from but do not change it. 10. Using the information found in the case and the previous budgets, complete the Cash Budget. REMINDER: Make sure every cell is either a formula or a reference to another cell (or range of cells). The cell reference for borrowings in quarter 1 is given to you; no other borrowing or repayments are made. 11. Using the information contained in the case and the previous budgets, complete the Budgeted Balance Sheet. REMINDER: Make sure every cell is either a formula or a reference to another cell (or range of cells). Cell references for equipment, accumulated depreciation, interest payable, taxes payable are provided; the formula to calculate capital is provided too. Examine these to understand where the numbers come from but do not change them. NOTE: if "Michael Woods, Capital" is negative, that is okay; although not good, equity can be negative. SWEATS GALORE Budgeted Balance Sheet December 31, 201 Assets Cash Accounts receivable Sweatshirt Inventory Equipment Less: Accumulated depreciation 17,350(3,060) Total assets Liabilities and Owner's Equity Accounts payable Note payable Interest payable Taxes payable Total liabilities Michael Woods, Capital Total liabilities and owner's equity
