WEEK2
qkiii123
ch5_htd_appexer_1.docxSolutions to Application Exercises
5-13. Excel Application: Performance Bicycle Parts
Use the information provided below to create a spreadsheet to analyze the replacement tube cost structure for six potential suppliers.
Which supplier source requires the highest investment of working capital or cash for average inventory? Which supplier source provides Ted with the highest percentage of gross profit on the presta replacement tube?
Students should create a spreadsheet with the following assumptions:
Tubes per Day = 400
Selling Price = $6.50
Average Inventory = 150%
Carrying Cost = 24%
Units per Year = 144,000
Students should use the following formulas to complete the spreadsheet:
Per Unit Import Duty Cost = (Per Unit Base Cost + Per Unit Shipping Cost) × Import Duty Rate
Per Unit Warehouse Cost = Per Unit Base Cost + Per Unit Shipping Cost + Per Unit Import Duty Cost
Average Inventory (Units) = Average Inventory × Tubes Per Day × Shipping Days
Average Inventory Value = Per Unit Warehouse Cost × Average Inventory (Units)
Total Inventory Carrying Cost = Average Inventory Value × Carrying Cost
Average per Unit Carrying Cost = Total Inventory Carrying Cost ÷ Units per Year
Per Unit Total Cost = Per Unit Warehouse Cost + Average per Unit Carrying Cost
Profit = Selling Price – Per Unit Total Cost
Percent Gross Profit = Gross Profit ÷ Selling Price
India is the supplier source that requires the highest investment of working capital or cash for average inventory (Average Inventory Value). Vietnam is the supplier source that provides Ted with the highest percentage of gross profit on the presta replacement tube.
5-14. Access Application: VSI Consultants
Emily Loftus, the HR manager, has asked you to use the information shown in Figure 5-23, 5-24, and 5-25 to create an Access database to manage employees and projects. You can download the Excel file Ch05Ex02, and import the worksheets to tables in your database. Emily wants you to create two queries. The first query identifies the best candidates for three client projects: U.S. Brokerage, Helen's Clothiers, and Solar Systems. The second query matches employees with projects. Specifically, she wants to match projects for two employees: Y326 and T871. What other queries may be useful to Emily?
Students should produce an Access database with three tables by importing data from the Excel file named Ch05Ex02. Students should create a query to identify qualified employees for a specified project, and a query to identify projects that match employee qualifications, using an If statement to determine which employees have sufficient experience for each project. Students should use the Filter function to limit results to “Yes.”
Sufficient_Experience: If([Employees]![Years_Exp]>=[Projects]![Years_Exp], “Yes,” “No”)
