REQUIRED: To do this project, you will need to review the Excel budgets in chapter 8: Schedule 1 (p344), Schedule 12 (p346), Schedule 3 (p348), Schedule 4 (p349), Schedule 5 (p350), Schedule 7 (p352), Schedule 8 (p355), Schedule 9 (p358). Please prepare the following budgets in an Excel spreadsheet for each month of the first quarter and for the quarter in total. The first five (light green highlighted) budgets are to be on one tab, separate from the original data, and the two (light blue highlighted) budgets are to be on a third, separate, tab. All budgets should be formulae and cell references: there should be NO Values directly keyed in any of your budgets. All the numbers in the cells should have algebraic formulae (eg use sum function), or the cells would have a cell reference to the data sheet (eg use "=data!C5 to refer to the January sales volume in the "data" tab). Good financial formatting for the budgets should be followed and will be graded. As well, making the budgets easy for the reader to read will also count toward the grade. DUE FRIDAY APRIL 23, midnight. PLACE YOUR SPREADSHEET SOLUTION IN THE Horsexcom Excel Project DROPBOX IN FOLIO. TO BE SURE: The data sheet is given to you. Please add two tabs. One for the green set of budgets below, and one tab for the blue set of budgets below. One additional tab for: 1. Sales budget 4. Direct labor budget 2. Production budget 5. Overhead budget 3. Direct materials purchases budget 6. Selling and Admin budget A second additional tab for: 7. Cash Budget 8. Income Statement L February A B C D E F H K 1 Horsey Com! (submitted by Hoss Cartwright) 2 3 SALES INFORMATION 4 Projected sales in units 5 January 15000 6 25000 7 March 24000 8 April 21000 9 May 26000 10 11 Finished Goods Inventory predictions 12 Finished good inventory, Jan 1 5000 units 13 Finished Goods Inventory policy 14 Desired ending FGI 20% percent of next months sales volume 15 16 PRODUCTION INFORMATION 17 Direct labor two types 18 Leather worker 19 each harness takes 2.5 hours of direct labor 20 wage rate for direct laboris 15 dollars per hour 21 22 Assembler 23 each harness takes 2 hour of direct labor 24 wag rate for Assembler 10 dollars per hour 25 26 Overhead estimates 27 Fixed portion (per month) variable cost ($ per DLH (sum of Leather worker plus Assembler)) 28 Supplies 1 29 Power 0.2 30 Maintenance 12500 1.1 31 Supervision 14000 32 Depreciation 45000 33 Taxes 4300 34 Other 86000 1.6 35 36 Direct materials usage - two types 37 quantity per unit of output (per harness) price per quantity unit 38 A87 3 35.5 dollars 39 D33 2 15 dollars 39 D33 2 15 dollars 40 41 Raw Materials inventory policy 42 Desired ending of the month RMI 20% percent of the next month's production needs 43 Assume beginning RMI (on Jan 1) is A87 18000 44 D33 12000 45 46 BANKING INFORMATION 47 Borrowings are assumed to occur at the beginning of the month 48 Repayments are assumed to occur at the end of the month. When the company has enough cash on hand at the end of the month, it repays the interest first, then any principal to the extent possible. 49 Interest rate is 6% percent per year 50 Cash Balance at January 1 $20,000 dollars 51 Minimum cash balance each month end is $10,000 dollars 52 53 ADDITIONAL INFORMATION 54 Selling price of each harness (finished product outptut) 330 dollars 55 Collections predition 56 collected in month of sale 35% of current month sales 57 collected in subsequent month 65% of prior month sales 58 Accounts receivable as at January 1 $150,000 59 60 Selling and admin per month 61 Fixed portion (per month) variable cost ($ per unit sold (cost driver)) 62 Salaries 30000 63 Commissions 1.4 64 Depreciation 5000 65 Shipping 2.6 66 Other 13000 67 68 March purchase land for 250,000 dollars 0.6
