Hi my name is Anna Abramova and I am really confused on how to do this Excel Project for Accounting 154. Can you help me? The file is attached!

ACC 154 - Excel Project #2 The goal of this project is to prepare a Master Budget for Value City Home Fu The project contains the same data as the Comprehensive Problem at the en Use the Handout Packet as your guide on how to complete each schedule. It Make sure your subtotals for each schedule are the same as in the Comp. Pro 1 General Guidelines to complete the project: Do not re-type any data (numbers) into your actual schedul Click on the cell where the data must go, type = sign and then click on the ce If you simply type in the numbers without linking them to the proper cells, yo The only amount you are allowed to type in is the amount of borrow 2 Whenever possible, use Excel formulas when adding, subtracting, multiplying To use the formulas, type the = sign first, click on the first cell used in the ca To add more than one cell, use the SUM formula. Type the =SUM( highlight th 3 Formatting Guidelines: A. The first and last line (row) of each schedule must be formatted as Accoun B. All the data except first and last rows must formatted as Numbers, no dec C. Make sure your numbers line up. If they don't, fix the alignment or add th D. Check your formulas by hitting 'CTRL ~' on the keyboard. E. Save your work frequently. 4 Once you are done with the entire project, make the following changes: A) In Data Box #2, change January's sales to $260,000 B) In Schedule #8, change the borrowing amount to $5,000 Hint: After the changes, your Ending Cash Balance should be now $9,545 Your Income Before Taxes should be now $31,775. Your Total Assets should be now $253,895. C) If your numbers are right, delete all the Hints, save the project, a or Value City Home Furnishings Store's first quarter. ive Problem at the end of Chapter 9 in the Handout Packet. ete each schedule. It will be much easier to complete the Excel Project if you have alrea me as in the Comp. Problem in the Handout Packet. ur actual schedules. All the data must be linked to Data Boxes nd then click on the cell where the data is coming from, click Enter. to the proper cells, your final calculations will be wrong and you will not receive credit f e amount of borrowing in Schedule #8. ubtracting, multiplying, or dividing numbers . rst cell used in the calculation, click on the +, -,/, * signs depending on your calculation he =SUM( highlight the cells that need to be added, and then close the parenthesis ), cl e formatted as Accounting. (This will insert a dollar sign in front of the number.) d as Numbers, no decimal places, use 1,000 coma separator. Numbers are always right e alignment or add the parentheses format. lowing changes: save the project, and submit it into the Dropbox. t if you have already completed the Comp. Problem in the Handout Packet. a Boxes . not receive credit for the exercise. n your calculation, and then click on the second cell involved, click Enter. e parenthesis ), click Enter. number.) s are always right aligned. Name: ACC 154 - Excel Project #2 Value City Home Furnishings Store, prepares its Master Budget on a quarterly basis. The December 31 balance sheet is as follows (These are the beginning balances for the year): Data Box #1 Beginning Balances Cash Accounts Receivable Inventory Property and Equipment Accounts Payable Capital Stock Retained Earnings Debit $26,000 50,000 86,400 80,000 Credit $102,000 45,000 95,400 $242,400 $242,400 Actual sales for December and budgeted sales for the next four months are as follows: Data Box #2 Total Sales December January February March April $200,000 $240,000 $250,000 $275,000 $260,000 Sales are 75% for cash and 25% on credit. Hint: The cells in this schedule should be linked to Data Schedule #1 Sales January February March Cash Sales (75%) Credit Sales (25%) Total Sales All credit sales are collected in the month following the sale. The accounts receivable at December 31 will be collected in January. Hint: That's December's credit sales that w Schedule #2 Cash Collections (Receipts) January February March Cash Sales from this month Credit Sales from last month Total Collections (Receipts) Schedule #3 Accounts Receivable at March 31 will be The company's gross profit is 40% of sales. Hint: Cost of Sales is 60% of Total Sales for that month. You will have to multiply Total Sales by 60% At the end of each month, inventory is to be on hand equal to 60% of the following month's sales ne Hint: Desired Ending Inventory is 60% of next month's Cost of Sales. You will have to multiply Cost of Sales by 6 Hint: To calculate March 31 Inventory,multiply: 60% * April total Sales from Data Box #2 * 60% Hint: Beginning Inventory for January comes from Data Box #1. Beginning inventory for Feb is ending inventory fr Hint: Treat the Total column separately. Do not add up Beginning and Ending inventories across the row. Shcedule #4 Inventory Purchases January February March Budgeted Cost of Sales Plus: Desired Ending Inventory Total Inventory Needs Less: Beginning Inventory Required Purchases 30% of a month's inventory purchases are paid for in the month of purchase; the remainder is paid Hint: From Accounts Payable should be linked to Data Box #1. Hint: January's Required Purchases will be paid as follow: 30% in January and 70% in February. Shcedule #5 Cash Payments for Inventory Purchases January Payments From Accounts Payable From January Purchases From February Purchases From March Purchases February Payments March Payments Total Payments Shcedule #6 Accounts Payable at March 31 will be 2 n a quarterly basis. nning balances for the year): months are as follows: schedule should be linked to Data Box #2. Total t: That's December's credit sales that will be collected in January. Total Hint: That's credit sales from March ave to multiply Total Sales by 60% to get Cost of Sales. % of the following month's sales needs, stated at cost. will have to multiply Cost of Sales by 60% to get Ending Inventory. s from Data Box #2 * 60% g inventory for Feb is ending inventory from Jan etc. ing inventories across the row. Total Hint: Do not add up this row. Hint: Do not add up this row. of purchase; the remainder is paid for in the following month. y and 70% in February. ses Total Payments Hint: This is 70% of March's Required Purchases. Monthly expenses are budgeted as follows: Hint: All the expenses are paid in the same month they are incurred Data Box #3 Selling and Administrative Expenses Salaries and wages $14,000 Advertising 25,000 Shipping 5% of sales Other Expenses 15% of sales Schedule #7 Cash Payments for Selling & Administrative Expenses January February March Total Salary Expense Advertising Shipping Hint: Calculate Shipping by Other Expenses Hint: Calculate Other Expe Cash payments for operating expenses Data Box #4 During February, the company will purchase land that co During March, land will be purchased for cash at a cost: During January, the company will declare and pay divide $8,000 $2,000 $30,000 Additional Information: The company must maintain a minimum balance of $5,000. All borrowing is done at thebeginning of a month and all repayments are made at the end of a month. Borrowing and repayments of principal must be in multiples of $1,000. Interest is paid only at the time of payment of principal. The annual interest rate is 18%. Schedule #8 Cash Budget January February March Quarter Beginning Cash Balance Hint: Beginning cash balan Add Cash Collections (Receipts) Hint: Cash Collections (Re Cash Available (a) Less Payments For Inventory Purchases Hint: Schedule #5 For Operating Expenses Hint: Schedule #7 For Land Purchase Hint: Data Box #4 For Dividends Hint: Data Box #4 Total Budgeted Payments (b) Surplus/(Shortage) (a-b) Financing Activity Borrowing Hint: You must borrow eno Interest Expense Repayment Ending Cash Balance Interest Expense = Principal x Interest Rate x Time Hint: In March, you will rep Hint: Calculate Shipping by multiplying Sales from Data Box #2 by 5% Hint: Calculate Other Expenses by multiplying Sales from Data Box #2 by 15% the end of a month. Hint: Beginning cash balance for January comes from Data Box #1. Hint: Cash Collections (Receipts) come from Schedule #2 Hint: Schedule #5 Hint: Schedule #7 Hint: Data Box #4 Hint: Data Box #4 Hint: You must borrow enough to cover the Shortage and have at least $5,000 in ending Cash Balance. It must be in multiples of $1,000. Hint: In March, you will repay the principal plus interest. Repayments and interest should be presented as negative numbers. st be in multiples of $1,000. gative numbers. Additional Information: Depreciation expense: $8,000 Schedule #9 Value City Company Income Statement For the Quarter Ended March 31 Revenues Cost of Goods Sold (60% of Sales) Gross Profit Operating Expenses: Salaries and wages Advertising Shipping Depreciation Other Expenses Total Operating Expenses Net Operating Income Less: Interest Expense Income Before Taxes Schedule #10 Value City Company Balance Sheet March 31 Current Assets : Cash A/R Inventory Total current assets Property and equipment, net Total Assets Current Liabilities A/P Stockholders' Equity Capital Stock Retained Earnings Total SE Total Liabilities and SE Once you are done with the entire project, make the following changes: A) In Data Box #2, change January's sales to $260,000 B) In Schedule #8, change the borrowing amount to $5,000 Hint: After the changes, your Ending Cash Balance should be now $9,545 Your Income Before Taxes should be now $31,775. Your Total Assets should be now $253,895. C) If your numbers are right, delete all the Hints, save the projec Schedule #1 Schedule #4 Schedule #7 Schedule #7 Schedule #7 From above Schedule #7 Schedule #8 Ending Cash balance from Schedule #8 Schedule #3 Ending Inventory from Schedule #4 Property and Equipment from Data Box #1 less Depreciation plus Land Purchases from Schedule #8 Schedule #6 Data Box #1 Beginning Retained Earnings from Data Box #1 plus Net Income from Schedule #9 less Dividends from Schedul ke the following changes: $260,000 unt to $5,000 he Hints, save the project, and submit it into the Dropbox. Schedule #8 s Dividends from Schedule #8